Python データベース(SQLAlchemy)

まずはデータベースに接続（.dbファイルを開く）ための処理を行うdb_hanbai.pyを作成する。

• create_engineで保存するファイル名を指定する
• sessionmakerでデータベース接続で使用するオブジェクトSessionを作成する。これはデータベースを実際に使用するときに使用する。
• declarative_baseでテーブルの元となるクラスBaseを作成する。

db_hanbai.py

from sqlalchemy import create_engine, engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///hanbai.db')
Session = sessionmaker(bind=engine)
Base = declarative_base()

テーブルのデータを一件（一行分）格納するためのクラスであるモデルクラスを作成する。
このクラスはBaseを親クラスとして継承する。
__tablename__ にテーブル名を指定する。

その後、各列をColumnで指定する。このとき、データ型（整数：Integer、文字列：String、日付：Date、日時：DateTime）を指定する。Stringの場合、最大文字数を指定する。主キーにはprimary_key=Trueを指定する。

最後に自モジュール実行時のif文中にテーブル作成のための命令を記述する。

shouhin.py

from sqlalchemy import Column, Integer, String
from db_hanbai import Base, engine

class Shouhin(Base):
    __tablename__ = 'shouhin' # テーブル名

    # 商品ID
    sid = Column(Integer, primary_key=True)

    # 商品名
    sname = Column(String(255))

    # 単価
    tanka = Column(Integer)

# テーブル作成
if __name__ == "__main__":
    Base.metadata.create_all(bind=engine)

これを実行することでテーブルが作成される。

uriage.py

hiは日付型（Date）。defaultで初期値を算出する関数を指定する。ここではdatetime.datetime.todayで今日の日付を指定。

from sqlalchemy import Column, Integer, Date
from db_hanbai import Base, engine
import datetime

class Uriage(Base):
    __tablename__ = 'uriage'

    uid = Column(Integer, primary_key=True)
    sid = Column(Integer)
    kosu = Column(Integer)
    hi = Column(Date, default=datetime.datetime.today)
    cid = Column(Integer)

if __name__ == "__main__":
    Base.metadata.create_all(bind=engine)

テーブル削除

全テーブルの削除

Base.metadata.drop_all(engine)

個別テーブルの削除

Shouhin.__table__.drop(engine)

main.py

shouhinテーブルを操作するためのmain.pyを作成する。

まず、ShouhinクラスとSessionのインポートを行う。次にSessionクラスのインスタンスを取得し、セッションを確立（データベースに接続）する。今後はこのsessionを使ってデータベース操作を行っていく。

from shouhin import Shouhin
from db_hanbai import Session 

session = Session()

データ追加

最初はテーブルは空なのでデータベースに追加を行う。追加するShouhinオブジェクトを作成し、session.add で追加を行う。最後に session.commit() を行わないとこの変更が保存されない。

# 追加するオブジェクトの作成
s  = Shouhin(sname="りんご", tanka=100)

session.add(s) # 追加

session.commit() # コミット（確定）

全データ取得

テーブル内にある全データを取得するにはsession.query(クラス名) を行う。テーブルクラスのオブジェクトのリストが取得出来る。

list = session.query(Shouhin).all()

# 表示
for s in list:
    print(s.sid, s.sname, s.tanka)

主キーによる取得

主キーを指定し、テーブル内にある一件のデータを取得するにはsession.query(クラス名, 主キーの値) を行う。テーブルクラスのオブジェクトが一件取得出来る。

s = session.query(Shouhin, 1).all()

# 表示
print(s.sid, s.sname, s.tanka)

検索

filterで条件を指定する。

list = session.query(Shouhin).filter(Shouhin.tanka >= 150).all()

for s in list:
    print(s.sid, s.sname, s.tanka)

文字列の部分検索も可能

list = session.query(Shouhin).filter(Shouhin.sname.contains("ん")).all()

for s in list:
    print(s.sid, s.sname, s.tanka)

データの変更

検索して取得したオブジェクトの中身を変更しコミットを行う。

s = session.get(Shouhin, 3)

s.tanka = 120

session.commit()

データの削除

検索して取得したオブジェクトをsession.delete で削除する。

s = session.get(Shouhin, 4)

session.delete(s)

session.commit()

• queryで複数のモデルを指定する
• joinでモデル名と条件を指定する
• 参照時には オブジェクト名.モデルクラス名.列名 を指定

list = session.query(Uriage, Shouhin).join(Shouhin, Shouhin.sid == Uriage.sid).all()
for u in list:
    print(u.Uriage.uid, u.Uriage.sid, u.Shouhin.sname, u.Uriage.kosu, u.Uriage.hi)

複数結合

list = session.query(
        Uriage, 
        Shouhin,
        Customer
).join(
        Shouhin, 
        Shouhin.sid == Uriage.sid
).join(
        Customer, 
        Customer.cid == Uriage.cid
).all()

for u in list:
    print(u.Uriage.uid, u.Uriage.sid, u.Shouhin.sname, u.Uriage.kosu, u.Uriage.hi,u.Customer.cid,u.Customer.name)

並び替え

list = session.query(Shouhin).order_by('tanka').all()

for s in list:
    print(s.sid, s.sname, s.tanka)

降順の場合、desc関数を使用する（インポート必要）

from sqlalchemy import desc

list = session.query(Shouhin).order_by(desc('tanka')).all()

SQL直接実行

from sqlalchemy import text

list = session.execute(text('SELECT sid, SUM(kosu) as goukei FROM uriage GROUP BY sid'))

for u in list:
    print(u.sid, u.goukei)

パラメータ使用

list = session.execute(text('SELECT sid,kosu FROM uriage WHERE sid = :sid'), {'sid':1})

for u in list:
    print(u.sid,u.kosu)