SQLAlchemyの落とし穴

はじめに

pythonでDBアクセスをする際によく使われるORマッパーとして、SQLAlchemyがあります。sqlalchemyを使用してpythonでDBアクセスをするはSQL以外のことをまとめました。今回はSQLAlchemyを理解しないで使用すると困りそうなことをまとめました。

環境

• python3:3.6.5
• SQLAlchemy:1.3.7
• psycopg2:2.8.3

SQLとSQLAlchemyで取得できるものが違う

SQLAlchemyにはORMでの更新以外にもSQL文を直接使用することができます。DBから更新する方法と取得する方法が異なると意図した情報がとれないので気を付けてください。

具体例

同一セッション内であってもコミット前にSQL文で変更した内容をORMで取得しようとしたら更新前のものが取れてしまいます。逆も同様です。

実験

SQL文やORMで更新した後にSQL文とORMで取得して内容を表示してみます。

実験ソース

新しくセッションを張るとDBの情報を取りに行ってしまうため、更新用関数と表示用関数ともに同一セッションを持ちまわしています。また、更新時の変数が表示時の変数に影響を与えたくないため更新と表示を別の関数として作成しています。セッションの作成等は以前を見てください。

main.py

def get_mapper_and_query(local_session):
    print('============== query get ===============')
    records = local_session.execute("select * from pets where id = 3;")
    for record in records:
        print(record)
    print('============== mapper get ===============')
    pet2 = local_session.query(Pets).filter(Pets.id==3).first()
    print(pet2)

def update_mapper_and_query():
    local_session = SESSION()
    print('*************************** default ***************************')
    get_mapper_and_query(local_session)
    print('*************************** default ***************************\n')

    print('*************************** mapper update ***************************')
    pet = local_session.query(Pets).filter(Pets.id==3).first()
    pet.age = 10
    get_mapper_and_query(local_session)
    print('*************************** mapper update ***************************')

    print('*************************** query update ***************************')
    local_session.execute("update pets set age=20 where id = 3;")
    get_mapper_and_query(local_session)
    print('*************************** query update ***************************\n')

結果

もともとageが5だったものがORMの更新後のORMによる取得時だけ10に更新されています。さらに、SQL文の更新後にはSQL文による取得時はageが20に更新されてORMによる取得時にはORM更新時の10がそのまま残っています。このようにそれぞれの更新方法と取得方法が異なれば取れるものが異なります。

*************************** default ***************************
============== query get ===============
(3, 'mink', 5, datetime.datetime(1990, 2, 12, 0, 0))
============== mapper get ===============
id:3, name:mink, age:5, birthday:1990-02-12 00:00:00
*************************** default ***************************

*************************** mapper update ***************************
============== query get ===============
(3, 'mink', 5, datetime.datetime(1990, 2, 12, 0, 0))
============== mapper get ===============
id:3, name:mink, age:10, birthday:1990-02-12 00:00:00
*************************** mapper update ***************************
*************************** query update ***************************
============== query get ===============
(3, 'mink', 20, datetime.datetime(1990, 2, 12, 0, 0))
============== mapper get ===============
id:3, name:mink, age:10, birthday:1990-02-12 00:00:00
*************************** query update ***************************

DBへのコネクションが増えていく

SQLAlchemyにはコネクションプールの機能があります。永続的にプログラムを動かし続ける場合はコネクションが生成されるタイミングを理解しないとどんどんコネクションが増えていきます。

具体例

DB情報を取得するWEBサービスをflaskで作成したらDBのセッション数の上限に達してエラーするようになった。

実験

flaskでDBから情報取得するサービスを作成してDB側で接続数を確認してみます。

実験ソース

リクエストを受けたらDBへのセッションを作成して情報を返却するソースです。今回はわかりやすいようにDBのセッションにpy_appというアプリケーション名をつけています。DB接続はsqlalchemyを使用してpythonでDBアクセスをするから、flaskについてはflaskでhttpステータスを返却する方法を見てください。

main.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from flask import Flask, jsonify
from Pets import Pets

DATABASE = 'postgresql'
USER = 'postgres'
PASSWORD = 'postgres'
HOST = 'localhost'
PORT = '5431'
DB_NAME = 'animal_db'


CONNECT_STR = '{}://{}:{}@{}:{}/{}'.format(
    DATABASE,
    USER,
    PASSWORD,
    HOST,
    PORT,
    DB_NAME
)

app = Flask(__name__)


@app.route('/hello/<name>')
def hello(name):
    engine = create_engine(CONNECT_STR, connect_args={"application_name":"py_app"})
    session = sessionmaker(engine)()
    pets = session.query(Pets).filter(Pets.name==name).all()
    return jsonify(pets)

if __name__ == "__main__":
    app.run()

結果

起動時のセッション数

postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
 count
-------
     0
(1 row)

1回目のリクエスト後のセッション数

postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
 count
-------
     1
(1 row)

2回目のリクエスト後のセッション数

postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
 count 
-------
     2
(1 row)

30回目のリクエスト後のセッション数

postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
 count
-------
    30
(1 row)

このようにリクエストを発行するたびにセッションが張られていくことがわかります。これを防ぐにはセッションを持ちまわすかレスポンス前にセッションをクローズしてエンジンをディスポーズする必要があります。

おわりに

今回の取得の値が異なる点はテストで見つかりますがセッションは下手したら見つからない可能性がありとても危険です。ORMは便利に使えてしまうために十分に理解せずに使用する人がいますがこのような落とし穴にハマる可能性があるため十分に理解する必要があります。