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()))
結果
