このQiitaの目的
SQLAlchemyを使うとPythonからSQLを直接操作できます。
SQLAlchemyにはcoreとORMがあるのですが、このQiitaではORMのみ扱います。
日本語のドキュメントが少なかったので、以下の書籍や公式ドキュメントで調べたことをまとめておきます。内容は順次加筆していこうと思います。
Essential SQLAlchemy - Amazon
環境
mysqlでsampledatabaseという名前の空のデータベースをあらかじめ作成しsqlalchemyでいじっていきます。
mysqlのバージョン8.0.13
Pythonのバージョンは3.7.2
実行環境はPyCharmの2018.3.4
SQLAlchemyのバージョンは1.13.11
SQLAlchemyとは / メリット / デメリット
SQLAlchemyとはPythonのモジュールで、session.query(Person.id,Person.name).first()のように書くことでSQLを直接操作できます。
SQLAlchemyではSQLをクラスとして扱えるので、オブジェクト指向で書けるのがメリットです。
一方で日本語の文献が多くないのはデメリットです。
テーブルの作成
まずインストールします。
pip install sqlalchemy
次にengine(SQLサーバーに接続)とbaseとsessionを作成します。
import pymysql
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql:///sampledatabase")
Session = sessionmaker(bind=engine)
session = Session()
このsessionは今後ほぼ全ての操作で使用します。engineでは今回mysqlを使いました。そしてテーブルの作成。
from sqlalchemy import (Table, Column, Integer, String)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Staff(Base):
__tablename__ = "staffs"
staff_id = Column(Integer(), primary_key=True)
staff_name = Column(String(20), index=True)
staff_age = Column(Integer())
staff_section = Column(String(20))
staff_post = Column(String(20))
今回はmysqlを使用しましたがsqlite等も使えます。
対応データベース
なお、対応データベースは以下(2020年2月11日時点)。
- MySQL
- SQLite
- PostgreSQL
- Oracle
- Mycrosoft SQL Server
- Firebird
- Sybase
データのinsert
データのinsertはsession.add()で行います。
from staffdata import *
Yamada = Staff(staff_id=1, staff_name="Yamada Taro", staff_age=30, staff_section="Marketing", staff_post="Manager")
session.add(Yamada)
session.commit()
注意しなければならないのは、インスタンスをaddした後はsession.commit()しないとデータが保存されません。
この段階でYamadaのデータがstaffsテーブルにinsertされたので、
print(Yamada.staff_id)
と実行すると
1
と出力されます。複数データをinsertする場合はaddを繰り返すより以下の方が高速です。
Yamada2 = Staff(staff_id=2, staff_name="Yamada Hanako", staff_age=25, staff_section="Legal", staff_post="Partner")
Sato = Staff(staff_id=3, staff_name="Sato Taro", staff_age=40, staff_section="Legal", staff_post="Manager")
session.bulk_save_objects([Yamada2, Sato])
session.commit()
bulk_save_objectsを使うと複数のデータをまとめてinsertでき、それぞれaddするよりはるかに高速です。ただ、bulk_save_objectによるinsertは速度を優先しているため、primary keyの使用やforeign key(複数のtableで同じデータを使用するときにリンクできる)の併用は適さないとされています。以下公式ドキュメント。
If you are inserting multiple records and don't need access to relationships or the inserted primary key, use bulk_save_objects or its related methods.
primary keyやforeign keyを併用したい場合は大人しく1つ1つaddしましょう。その際session.commit()するよりsession.flush()の方が早いようです。仕組みはよくわかりません。。先ほどのコードでいうと
session.bulk_save_objects([Yamada2, Sato])
session.commit()
の部分を
session.add(Yamada2)
session.add(Sato)
session.flush()
に変えます。
Query
query()メソッドを使うとデータをクエリ（選択）できます。以下のコードで先ほど作成したstaffsテーブルの全データを選択、表示できます。
all_staffs = session.query(Staff).all()
print(all_staffs)
このall()メソッドは全データを選択することを表し、他にもfirst()メソッドやone()メソッド、scalar()メソッドがあります。データをひとつだけ取り出したい場合は通常first()メソッドを使用します。one()メソッドだとそもそも入力するデータが1つでないとエラーが出ます。
一部のcolumnのみクエリする
query()メソッドの引数でcolumnの名前を指定することで、一部のcolumnのみクエリすることができます。
print(session.query(Staff.staff_name, Staff.staff_section))
Order
order_by()メソッドを使うとデータを並び替えることができます。先ほどのデータを年齢で並び替える場合は、
for staff in session.query(Staff).order_by(Staff.staff_age):
print("{} - {}".format(staff.staff_age, staff.staff_name))
降順にorder
desc()を使います。
from sqlalchemy import desc
for staff in session.query(Staff).order_by(desc(Staff.staff_age)):
print("{} - {}".format(staff.staff_age, staff.staff_name))
orderしてlimit
limit()を使うと指定した数だけ取り出せます。order_byと組み合わせて若手の2人を取り出します。
limit = session.query(Staff).order_by(Staff.staff_age).limit(2)
print([result.staff_name for result in limit])
func（sum,count）
funcを使うとsum()やcount()など簡単な統計量をだせます。
from sqlalchemy import func
sum
まず合計を出すsum()
age_sum = session.query(func.sum(Staff.staff_age)).scalar()
count
次にデータの数をカウントするcount()
name_count = session.query(func.count(Staff.staff_name)).first()
label
label()を使うとcolumnの名前を変更できます。先ほど紹介したsumやcountでは新しい「count」などの名前のcolumnが生成されそこにデータが入ってしまうので、labelを併用することは特に有効です。「staff_section」を「staff_location」にリネームしてみます。リネームできたかの確認にはkey()メソッドが有用です。
rename = session.query(Staff.staff_section.label("staff_location"))
print(rename.key())
# [u'staff_location']
filter
最も使用頻度が高いfilter()とfilter_by()。カラム内のデータをフィルタリングできます。
filtered = session.query(Staff).filter(Staff.staff_name == "Sato Taro").first()
filtered2 = session.query(Staff).filter_by(Staff.staff_name="Sato Taro").first()
filteredとfiltered2では同じ結果が出力されます（違いがよくわかりません）。もしSato Taroのstaff_idのみを出力したい場合はquery(Staff)をquery(Staff.staff_id)に変えます。また、カラムの文字列を一部だけfilterすることもできます。その場合は
filtered3 = session.query(Staff).filter(Staff.staff_name.like("%Sato%").first()
とします。そうするとSatoを含む行が全て取得できます。
複数の条件をand,orで繋いでfilter
filter()で2つ以上の条件を繋ぐ方法は2通りあります。最もわかりやすいのはこの方法です。
query = session.query(Staff).filter(
Staff.staff_age >= 30,
Staff.staff_post == "Manager"
).all()
and(連言)であることを明確にしたい場合は
from sqlalchemy import and_, or_, not_
query = session.query(Staff).filter(
and_(
Staff.staff_age >= 30,
Staff.staff_post == "Manager"
)
).all()
と書くこともできます。or(選言)である場合、or_を使います。
from sqlalchemy import and_, or_
query = session.query(Staff).filter(
or_(
Staff.staff_age.between(35,45),
Staff.staff_post.contains("Manager")
)
).all()
between()は「2つの引数の間」、contains()は「文字列を含んでいる」という条件を効果的にfilterできます。
Update
データのupdateは以下のように行えます。
query = session.query(Staff)
staff_update = query.filter(Staff.staff_name == "Yamada Taro")
staff_update.staff_age = staff_update.staff_age + 100
session.commit()
最後にsession.commit()しないとデータが上書きされないので注意が必要です。
updateはupdate()を使って行うこともできます。この場合、 session.commit()は必要ありません。
query = session.query(Staff)
query = query.filter(Staff.staff_name == "Yamada Taro").query.update({Staff.staff_age: Staff.staff_age + 100})
Delete
データの削除は以下のように行えます。
query = session.query(Staff).filter(Staff.staff_name == "Yamada Taro")
target_data = query.one()
session.delete(target_data)
session.commit()
あるいは
query = session.query(Staff)
query = query.filter(Staff.staff_name == "Yamada Taro")
query.delete()
です。sessionからdeleteする場合はcommit()する必要があります。忘れがちです。