LoginSignup
31
29

More than 3 years have passed since last update.

@mink0212

sqlalchemyを使用してpythonでDBアクセスをする

Posted at

はじめに

pythonでDBアクセスをする際によく使われるORマッパーとして、SQLAlchemyがあります。
基本的なselectやinsertといったDBの操作はいろいろなサイトに乗っていて簡単に使えるのですが
DB操作以外でちょっと調べたのでまとめます。

環境

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

インストール

インストールは普通にpipでインストールします。

>pip install sqlalchemy
>pip install psycopg2

使用するクラスは次のクラスを使います。

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Pets(Base):
    __tablename__ = 'pets'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    birthday = Column(DateTime)

    def __init__(self, name, age, birthday):
        self.name = name
        self.age = age
        self.birthday = birthday

    def __str__(self):
        return 'id:{}, name:{}, age:{}, birthday:{}'.format(self.id, self.name, self.age, self.birthday)

セッションの生成

大抵のプログラムでDBの操作をするときは、効率化のためにセッションを生成して、そのセッションを使いまわします。
SQLAlchemyも同様にグローバル変数にセッションを生成して、それを関数内で呼び出して使用すると使いまわしてくれます。
sql発行後に local_session.close() をしていますが、これはグローバル変数にセッションを戻しているだけなので生成したセッションは消えていません。
セッションを切断するためにはengine.dispose()をする必要がありますが基本セッションを使いまわすのであまり必要ないです。

ポイント

ENGINE = create_engine(接続文字列)
SESSION = sessionmaker(ENGINE)
local_session = SESSION()
取得結果 = local_session.query(ORマップ用クラス).all()
local_session.close()

実験

pyMod.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

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

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

ENGINE = None
SESSION = None

def read_data(name):
    local_session = SESSION()
    try:
        pets = local_session.query(Pets).filter(Pets.name=="pochi").all()

        for pet in pets:
            print(pet)

    finally:
        local_session.close()


if __name__ == "__main__":
    ENGINE = create_engine(CONNECT_STR)
    SESSION = sessionmaker(ENGINE)
    print('--------------- before select 1 ------------------------')
    print('  session:{}'.format(ENGINE.pool.status()))

    read_data('pochi')
    print('--------------- after select 1 ------------------------')
    print('  session:{}'.format(ENGINE.pool.status()))

    print('--------------- before select 2 ------------------------')
    print('  session:{}'.format(ENGINE.pool.status()))
    read_data('tama')

結果

# python3 pyMod.py
--------------- before select 1 ------------------------
  session:Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
id:1, name:pochi, age:4, birthday:1990-02-12 00:00:00
--------------- after select 1 ------------------------
  session:Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0
--------------- before select 2 ------------------------
  session:Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0
id:2, name:tama, age:3, birthday:1992-08-06 20:02:21
--------------- after select 2 ------------------------
  session:Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0

結果を見るとbefore select1の時にはConnections in poolが0でselect実行後には、1になっています。
また、Current Overflowも-5から1つ生成して-4になっています。
select 2を見るとselect 1で使ったセッションを使いまわしているため、コネクションの数が変わっていません。

セッションの制限

DBの設定によってはセッション数を制限しておりセッションの生成を管理したい場合があります。
その際はengine作成時にパラメータを入れてあげるだけで制限できました。

ポイント

ENGINE = create_engine(CONNECT_STR, pool_size=コネクション数, max_overflow=制限を超えて生成できるコネクション数)

pool_sizeの実験

上のソースから変更部分のみ

pyMod.py
    ENGINE = create_engine(CONNECT_STR, pool_size=2, max_overflow=1)

結果

# python3 pyMod.py
--------------- before select 1 ------------------------
  session:Pool size: 2  Connections in pool: 0 Current Overflow: -2 Current Checked out connections: 0
id:1, name:pochi, age:4, birthday:1990-02-12 00:00:00
--------------- after select 1 ------------------------
  session:Pool size: 2  Connections in pool: 1 Current Overflow: -1 Current Checked out connections: 0
--------------- before select 2 ------------------------
  session:Pool size: 2  Connections in pool: 1 Current Overflow: -1 Current Checked out connections: 0
id:2, name:tama, age:3, birthday:1992-08-06 20:02:21
--------------- after select 2 ------------------------
  session:Pool size: 2  Connections in pool: 1 Current Overflow: -1 Current Checked out connections: 0
PS C:\Users\minkl\OneDrive\ドキュメント\program\python\sql_alchemy>

結果を見るとPool sizeが指定した2になっています。

max_overflowの実験

上のソースから変更部分のみ

pyMod.py
    ENGINE = create_engine(CONNECT_STR, pool_size=0, max_overflow=1)

結果

# python3 pyMod.py
--------------- before select 1 ------------------------
  session:Pool size: 0  Connections in pool: 0 Current Overflow: 0 Current Checked out connections: 0
id:1, name:pochi, age:4, birthday:1990-02-12 00:00:00
--------------- after select 1 ------------------------
  session:Pool size: 0  Connections in pool: 1 Current Overflow: 1 Current Checked out connections: 0
--------------- before select 2 ------------------------
  session:Pool size: 0  Connections in pool: 1 Current Overflow: 1 Current Checked out connections: 0
id:2, name:tama, age:3, birthday:1992-08-06 20:02:21
--------------- after select 2 ------------------------
  session:Pool size: 0  Connections in pool: 1 Current Overflow: 1 Current Checked out connections: 0

結果を見るとCurrent Overflowが1になっています。

オーバーしたときの実験

上のソースから変更部分のみ

pyMod.py
    ENGINE = create_engine(CONNECT_STR, pool_size=0, max_overflow=0)

結果

# python3 pyMod.py
--------------- before select 1 ------------------------
  session:Pool size: 0  Connections in pool: 0 Current Overflow: 0 Current Checked out connections: 0

結果のところから進まなくなりタイムアウトしました。設定値的にセッションを生成できないけど、機能的にはセッションの解放待ちになっています。

取得型の指定

基本はDBの値をクラスで取得しますが、queryに指定することでコレクション型で取得することができます。

pyMod.py
pets = local_session.query(Pets.name, Pets.age).all()
print(type(pets))
for pet in pets:
    print(type(pet))
    print(pet)

結果

# python3 pyMod.py
--------------- before select 1 ------------------------
  session:Pool size: 2  Connections in pool: 0 Current Overflow: -2 Current Checked out connections: 0
<class 'list'>
<class 'sqlalchemy.util._collections.result'>
('pochi', 4)
<class 'sqlalchemy.util._collections.result'>
('tama', 3)

オートインクリメントのタイミング

postgresqlにはserialという数字を自動的にインクリメント(+1)してくれる機能があります。
そのインクリメントのタイミングを調べました。

pyMod.py
def insert_date(name):
    local_session = SESSION()
    try:
        pet = Pets(name, 4, datetime(year=1990, month=2, day=12))
        print(pet.id)
        local_session.add(pet)
        print(pet.id)
        local_session.commit()
        print(pet.id)
    except:
        local_session.rollback()

    finally:
        local_session.close()

結果

None
None
4

結果を見るとコミットしたタイミングでインクリメントされました。

一括アップデート

アップデートはselectで取得したクラスの変数を書き換えてaddをしますが、性能的な問題で一括で更新したいことがあります。
その際は、直接更新できるようです。

pyMod.py
def update_data():
    local_session = SESSION()
    local_session.query(Pets).filter(Pets.age==4).update({Pets.name:'mink'})
    local_session.commit()    

    local_session.close()

結果

id:4, name:mink, age:4, birthday:1990-02-12 00:00:00
---- update -----
id:4, name:mink, age:4, birthday:1990-02-12 00:00:00
id:1, name:mink, age:4, birthday:1990-02-12 00:00:00
id:3, name:mink, age:4, birthday:1990-02-12 00:00:00

おわりに

SQLAlchemyでDBに接続してみました。カーソル等を使わなくて済むのでとても使いやすいですが、セッションあたりが混乱しやすいです。
未だにセッションを返却する関数がcloseなのか納得がいっていないので人に説明するときにすごく苦労しています。

31
29
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
31
29