SQLAlchemyの基礎的な扱い方
Kibaraです。
暖かくなってきたのは嬉しいですが、花粉症の季節が到来して泣いております。
目が痒くて。
さて、今回はSQLAlchemyの基礎的な扱いを学びましたので、
元気にアウトプットしたいと思います。
SQLAlchemy
Pythonで使えるORマッパーです。
データベースをPythonコードでオブジェクトのように扱えるようにしたものですね。
たとえばこんな感じで記述できます。
# テーブル定義 Base = declarative_base() class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) age = Column(Integer) # SQLiteのメモリDBに接続したオブジェクトを作る engine = create_engine('sqlite:///:memory:') Session = session_maker(bind=engine) ses = Session() # SQLiteに対してSelect文を発行して結果を表示 # SELECT * FROM users; と同等 for row in ses.query(Users).all(): print(row.id, row.name, row.age)
事前準備
データベース
データベースは事前に作成しておきます。
今回はPostgreSQLをインストールしました。
> psql --version psql (PostgreSQL) 11.1
勢いあまってテーブルも作ってしまいます。
> \d users
テーブル "public.users"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+----------+----------+---------------+------------
id | smallint | | not null |
name | text | | |
age | smallint | | |
インデックス:
"users_pkey" PRIMARY KEY, btree (id)
> SELECT * FROM users;
id | name | age
----+------+-----
1 | aoki | 27
2 | iida | 31
3 | usui | 18
(3 行)
このほか、PostgreSQLの設定ファイルも編集して、
外部アクセスを許可しておきましょう。
PostgreSQLをインストールしたフォルダのdata配下にあります。
# ./data/pg_hba.conf host all all 0.0.0.0/0 md5
Python
今回は以下の環境で動作確認しています。
> python --version Python 3.7.2
SQLAlchemyを使うので、必要なパッケージをインストールします。
> conda install sqlalchemy psycopg2 > conda list psycopg2 psycopg2 2.7.6.1 py37h7a1dbc1_0 > conda list sqlalchemy sqlalchemy 1.2.15 py37he774522_0
データベースに接続する
データベースに接続する方法は、コネクションとセッションの2通りあります。
まずはごく基本的な操作として、2種類の接続方法を使って、
事前準備で作成したusersテーブルのデータをSELECTしてみます。
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker # engine はDBの接続情報を保持します # echo=Trueを指定することで、発行したSQLを出力してくれます engine = create_engine( 'postgresql://[user_id]:[password]@[host]:[port]/[db_name]', echo=False) # コネクションを張ります print("===コネクション===") connection = engine.connect() for row in connection.execute('SELECT * FROM users'): print(row.id, row.name, row.age) connection.close() # セッションを張ります print("===セッション===") Session = sessionmaker(bind=engine) session = Session() for row in session.execute('SELECT * FROM users'): print(row.id, row.name, row.age) session.close() # 出力結果 # ===コネクション=== # 1 aoki 27 # 2 iida 31 # 3 usui 18 # ===セッション=== # 1 aoki 27 # 2 iida 31 # 3 usui 18
コネクションとセッションを両方使ってみましたが、どちらも同じ結果になりました。
基本的にどちらも同じことができるのですが、例えば次のような違いがあります。
・コネクション
connection.begin()で明示的にトランザクションを開始させる必要がある。
トランザクション管理変数を定義し、transaction.commit()などとする。
・セッション
セッションを作成した時点でトランザクションが開始されている。
session.commit()やsession.rollback()でトランザクションを終了でき、
終了と同時に次のトランザクションが開始される。
参考
SQLAlchemy 1.2 - Managing Transactions
接続方式による記述の違いを確認する
試してみましょう。
# コネクションの場合 connection = engine.connect() transaction = connection.begin() try: connection.execute( "INSERT INTO users values (4, 'endou', 24)") transaction.commit() print("** COMMIT **") except SQLAlchemyError: transaction.rollback() print("** ROLLBACK **") for row in connection.execute('SELECT * FROM users'): print(row.id, row.name, row.age) connection.close() # セッションの場合 Session = sessionmaker(bind=engine) session = Session() try: session.execute("INSERT INTO users values (5, 'oshima', 46)") session.commit() print("** COMMIT **") except SQLAlchemyError: session.rollback() print("** ROLLBACK **") for row in session.execute('SELECT * FROM users'): print(row.id, row.name, row.age) session.close()
コネクションとセッションでそれぞれ1名、usersテーブルに登録し、
登録が成功すればコミット、失敗した場合はロールバックするような処理を記述しました。
コネクションではトランザクションを管理する別変数transactionを用意し、
transactionに対して.commit()や.rollback()を行っています。
対してセッションにはトランザクション管理が含まれていますので、
session.commit()やsession.rollback()といった操作が可能になっています。
これを2回実行してみると、1回目は登録に成功していますが、
2回目はid重複のためINSERT処理に失敗してロールバックを行っています。
# 1回目 ** COMMIT ** 1 aoki 27 2 iida 31 3 usui 18 4 endou 24 ** COMMIT ** 1 aoki 27 2 iida 31 3 usui 18 4 endou 24 5 oshima 46 # 2回目 ** ROLLBACK ** 1 aoki 27 2 iida 31 3 usui 18 4 endou 24 5 oshima 46 ** ROLLBACK ** 1 aoki 27 2 iida 31 3 usui 18 4 endou 24 5 oshima 46
特別な事情がない限り、一般的な用途ではセッションで十分かと思いますので、
以降はセッションのみ使用します。
テーブルを定義する
さて、これまでは生のSQL文を自分で書いて実行させるパターンでした。
折角ORマッパを使っているのに、自力でSQLを書くのは面倒です。
SQLAlchemyではPythonのクラスとしてテーブルを定義することで、
よしなに上手いことやってくれるように記述ができます。
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base # テーブル定義のベース Base = declarative_base() # テーブル定義クラス class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) age = Column(Integer) # セッションを張る Session = sessionmaker(bind=engine) session = Session() # 問い合わせを発行する for row in session.query(Users).all(): print(row.id, row.name, row.age)
これを実行すると、次のSQL文が発行されます。
SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age FROM users
その結果として、テーブルに格納されたデータを取得できています。
1 aoki 27 2 iida 31 3 usui 18 4 endou 24 5 oshima 46
よしなに上手いことやってくれましたね。
テーブルを作成する
これまでPythonで記述していたテーブル定義をもとに、
実際のデータベースにテーブルを作ることができます。
ユーザーの誕生日と出身地を登録するテーブルを作ってみましょう。
Base = declarative_base() # ユーザーを登録するテーブル class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) age = Column(Integer) # ユーザーの誕生日と出身地を登録するテーブル class Profile(Base): __tablename__ = 'profile' id = Column(Integer, ForeignKey('users.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True) birthday = Column(DATE) birthplace = Column(String(50)) Base.metadata.create_all(bind=engine)
まず、先ほどと同様にProfileテーブルを定義したのち、
作成先のデータベースを指定してBase.metadata.create_all()を実行すると、
定義に従ってテーブルを作成してくれます。
実行してみると、以下のSQLを発行していました。
CREATE TABLE profile ( id INTEGER NOT NULL, birthday DATE, birthplace VARCHAR(50), PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE )
本当にデータベースにテーブルができたのか確認しておきます。
localdb=# \d profile
テーブル "public.profile"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------------+-----------------------+----------+---------------+------------
id | integer | | not null |
birthday | date | | |
birthplace | character varying(50) | | |
インデックス:
"profile_pkey" PRIMARY KEY, btree (id)
外部キー制約:
"profile_id_fkey" FOREIGN KEY (id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
できてる……！
なお、既にテーブルが存在していてもエラーにはなりません。
CRUD操作
先ほどの例ではsession.queryでSELECT文が発行できました。
同様にして、CRUD操作の記述方法を確認しておきます。
Create
INSERT文はadd()で実現します。
# INSERTしたいデータを作っておく user_profile = [ (1, '1992/2/11', 'Aichi'), (2, '1988/1/14', 'Miyagi'), (3, '2002/6/16', 'Yamaguchi'), (4, '1996/8/5', 'Osaka'), (5, '1972/12/25', 'Nagasaki'), ] # データを1つずつ読みだして、Profileクラスに格納 for data in user_profile: profile = Profile() profile.id = data[0] profile.birthday = data[1] profile.birthplace = data[2] session.add(profile) # INSERT session.commit() # コミットしないとデータベースには反映されない # データ確認 for row in session.query(Profile).all(): print(row.id, row.birthday, row.birthplace)
Profileテーブルとして定義したクラスのオブジェクトにデータを格納することで、
対応するテーブルにデータをINSERTすることができます。
Read
これは既に紹介した通り、session.query()で実現できます。
Session = sessionmaker(bind=engine) session = Session() for row in session.query(Profile).all(): print(row.id, row.birthday, row.birthplace)
絞り込みを行う場合はquery()のあとに条件を記述します。
idが2～4のユーザーを取り出してみましょう。
# まずSQL文を作る sql = session.query(Profile).\ filter(2 <= Profile.id).\ filter(Profile.id <= 4) # データ取得 for row in sql.all(): print(row.id, row.birthday, row.birthplace) # 一気にやってしまってもよい # for row in session.query(Profile).\ # filter(2 <= Profile.id).\ # filter(Profile.id <= 4).all(): # print(row.id, row.birthday, row.birthplace)
.filter()はWHEREに相当します。
このほか、.limit()や.order_by()なども使えます。
Update
Updateは他と違い、対応するメソッドはありません。
.query()で取得したデータに対して変更を加え、.commit()するとUPDATEされます。
# 現状のデータ確認 for row in session.query(Profile).all(): print(row.id, row.birthday, row.birthplace) # 変更対象のデータを取得・変更してコミット data = session.query(Profile).\ filter(Profile.birthplace == 'Yamaguchi').all() data[0].birthplace = 'Ishikawa' session.commit() # 修正後のデータを確認 for row in session.query(Profile).all(): print(row.id, row.birthday, row.birthplace)
もちろんデータベースもUPDATEされています。
localdb=# select * from profile; id | birthday | birthplace ----+------------+------------ 1 | 1992-02-11 | Aichi 2 | 1988-01-14 | Miyagi 4 | 1996-08-05 | Osaka 5 | 1972-12-25 | Nagasaki 3 | 2002-06-16 | Ishikawa <= UPDATE
変数dataに対する変更がデータベースにまで反映されました。
少し複雑なことをするときは注意が必要かもしれませんね。
DELETE
データ削除は、まず.query()で削除対象のデータを取得してから、
.delete()を行います。
ユーザーIDが5の人のプロフィールを削除します。
# データ抽出してから削除 session.query(Profile).\ filter(Profile.id == 5).\ delete() session.commit() # データ確認 for row in session.query(Profile).all(): print(row.id, row.birthday, row.birthplace)
.all()の代わりに.delete()で削除を実行できました。
まとめ
SQLAlchemyにおけるDB接続方法やトランザクションについて、
またCRUD操作の記述方法を学びました。
SQLAlchemyのドキュメントは英語ですが、サンプルもあり、
非常に読みやすかった印象です。
まだまだ色々な機能があるので、使いこなせるように頑張ります。
