「PythonとJavaScriptではじめるデータビジュアライゼーション」を読む

3.6SQL

sqlalchemyはcondaに入っていたのでinstallする必要はなかった。

3-6.py
nobel_winners = [
    {'category':'physics',
    'name':'Albert Einstein',
    'nationality':'Swiss',
    'sex':'male',
    'year':1921},
    {'category':'physics',
    'name':'Paul Dirac',
    'nationality':'British',
    'sex':'male',
    'year':1933},
    {'category':'chemistry',
    'name':'Marle Curie',
    'nationality':'Polish',
    'sex':'female',
    'year':1911}
]

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,Enum
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Winner(Base):
    __tablename__ = 'winners'

    id = Column(Integer,primary_key=True)
    name = Column(String)
    category = Column(String)
    year = Column(Integer)
    nationality = Column(String)
    sex = Column(Enum('male','female'))

    def __repr__(self):
        return "<Winner(name='%s',category='%s',year='%s')>" %(self.name,self.category,self.year)

engine = create_engine('sqlite:///data/nobel_prize.db',echo=True)
Base.metadata.create_all(engine)


Session = sessionmaker(bind=engine)
session = Session()

albert = Winner(**nobel_winners[0])
session.add(albert)
print(session.new)

結果

3-6.pyに追加
nobel_winners = [
    {'category':'physics',
    'name':'Albert Einstein',
    'nationality':'Swiss',
    'sex':'male',
    'year':1921},
    {'category':'physics',
    'name':'Paul Dirac',
    'nationality':'British',
    'sex':'male',
    'year':1933},
    {'category':'chemistry',
    'name':'Marle Curie',
    'nationality':'Polish',
    'sex':'female',
    'year':1911}
]

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,Enum
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Winner(Base):
    __tablename__ = 'winners'

    id = Column(Integer,primary_key=True)
    name = Column(String)
    category = Column(String)
    year = Column(Integer)
    nationality = Column(String)
    sex = Column(Enum('male','female'))

    def __repr__(self):
        return "<Winner(name='%s',category='%s',year='%s')>" %(self.name,self.category,self.year)

engine = create_engine('sqlite:///data/nobel_prize.db',echo=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

winner_rows = [Winner(**w) for w in nobel_winners]
session.add_all(winner_rows)
session.commit()

結果

3-6-4.py
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,Enum

Base = declarative_base()

class Winner(Base):
    __tablename__ = 'winners'

    id = Column(Integer,primary_key=True)
    name = Column(String)
    category = Column(String)
    year = Column(Integer)
    nationality = Column(String)
    sex = Column(Enum('male','female'))

    def __repr__(self):
        return "<Winner(name='%s',category='%s',year='%s')>" %(self.name,self.category,self.year)

engine = create_engine('sqlite:///data/nobel_prize.db',echo=True)
Session = sessionmaker(bind=engine)
session = Session()
#Winner count
result = session.query(Winner).count()
print("Winer count : ",result)
#Swiss Winner
result = session.query(Winner).filter_by(nationality='Swiss')
print("result:",list(result))
#other Winner
result = session.query(Winner).filter(Winner.category == 'physics',Winner.nationality != 'Swiss')
print("result:",list(result))
#id
for i in range(1,4):
    result = session.query(Winner).get(i)
    print("result:",result)
#year order by
res = session.query(Winner).order_by('year')
print("year order",list(res))
#例3-4
def inst_to_dict(inst,delete_id=True):
    dat = {}
    for column in inst.__table__.columns:
        dat[column.name] = getattr(inst,column.name)
    if delete_id:
        dat.pop('id')
    return dat

winner_rows = session.query(Winner)
nobel_winners = [inst_to_dict(w) for w in winner_rows]
print(nobel_winners)
#更新
print("UPDATE\n")
marie = session.query(Winner).get(3)
marie.nationality = 'French'
print(session.dirty)
session.commit()
print(session.dirty)
print(session.query(Winner).get(3).nationality)
#クエリの結果を削除
print("DELETE\n")
session.query(Winner).filter_by(name='Albert Einstein').delete()
print(list(session.query(Winner)))

結果（一部）

3.6.5 datasetを使った簡単なSQL

3-6-5.py
# -*- coding: utf-8 -*-
import dataset

nobel_winners = [
    {'category':'physics',
    'name':'Albert Einstein',
    'nationality':'Swiss',
    'sex':'male',
    'year':1921},
    {'category':'physics',
    'name':'Paul Dirac',
    'nationality':'British',
    'sex':'male',
    'year':1933},
    {'category':'chemistry',
    'name':'Marle Curie',
    'nationality':'Polish',
    'sex':'female',
    'year':1911}
]
db = dataset.connect('sqlite:///data/nobel_prize.db')
wtable = db['winners']
winners = wtable.find()
print(list(winners))

結果

datasetで新しくテーブルを作る
import dataset

nobel_winners = [
    {'category':'physics',
    'name':'Albert Einstein',
    'nationality':'Swiss',
    'sex':'male',
    'year':1921},
    {'category':'physics',
    'name':'Paul Dirac',
    'nationality':'British',
    'sex':'male',
    'year':1933},
    {'category':'chemistry',
    'name':'Marle Curie',
    'nationality':'Polish',
    'sex':'female',
    'year':1911}
]
db = dataset.connect('sqlite:///data/nobel_prize.db')
wtable = db['winners']
winners = wtable.find()
print(list(winners))

#既存のテーブルを削除
wtable.drop()
wtable = db['winners']
print(list(wtable.find()))

with db as tx:
    for w in nobel_winners:
        tx['winners'].insert(w)
print(list(db['winners'].find()))

結果