Psycopg2の基本的な使い方と、トランザクションについて新たに学んだ点をまとめてみた。
環境
Psycopg2 : 2.8.4
Heroku : 0.1.4
python : 3.7.4
Psycopg2でDBに接続してSQL文を実行する
psycopg2に関しては公式ドキュメントに全て書いてある。
https://www.psycopg.org/docs/usage.html#transactions-control
以下は最低これだけわかれば良いというやつ。簡単だね～。
# まず、connect関数でDBと接続されたconnectionインスタンスを生成する
conn = psycopg2.connect(DATABASE_URL)
# そしてconnectionインスタンスのcursorメソッドで、sql文を実行できるcursorインスタンスを生成する
cursor = conn.cursor()
# INSERTとかDELETEとかする場合はexecuteした後、commitしてDBに反映させる
cursor.execute("実行したいsql文")
conn.commit()
# SELECTしたらexecuteした後、fetchoneかfetchallかfetchmanyで取得する
cursor.execute("実行したいsql文")
data = cursor.fetchall()
cursor.close() #こちらは忘れずにクローズ
# 最後に接続を切る
conn.close()
注意すること
sql文を実行したら必ずcursorをcloseかcommitすること。
commitしても自動でcursorがcloseされる。
すなわち、INSERTとかの実行後は必ずconn.commit()し、SELECTしたらfetchした後必ずcursor.close()する。
ドキュメントによると、sql文が実行(execute)されたときにトランザクションなるものが作成されるらしい。これはconnectionクラスで処理される。cursorがcloseされるとトランザクションが終了する。
既に実行されているトランザクションがcloseする前に別のsql文が実行された場合、そのsql文は同じトランザクションで実行される（新たにトランザクションが作成されるわけではない）
つまり、トランザクションをきちんと終了しない(cursorをcloseしない)と同じトランザクションで永遠とsql文が実行され続けることになる。これが恐ろしいことに、トランザクションの中で一度エラーが発生すると、そのトランザクションが終了するまで永遠とエラーにトラップされ、次のsql文が実行できなくなってしまうのだ。
だからsql文を実行するたびにcursorはcloseしよう。
closeを忘れそうなあなたにwith文
with文を使うとこんな感じ。
with文中での処理が終わると勝手にcommitまでしてくれる優れもの。
ちなみにSELECTを実行した後にcommitしても特に問題は無いよう。
つまりとりまcommitしとけってことか。
import psycopg2
DATABASE_URL = os.environ.get('DATABASE_URL')
SQL = "select * from table名"
with psycopg2.connect(DABASE_URL) as conn:
with conn.cursor() as curs:
curs.execute(SQL)
result = curs.fetchall()
# resultに検索結果がリストで格納される
ちなみにwithでcursorはcloseしてもconnectionは繋がりっぱなしらしいので注意。
（つなぎっぱなしで何が悪いんだろう...）
2024/06/24 更新 : DBとの接続は処理負荷が高い。connectionをプールしておくことで、次のトランザクションを素早く行えるようになっている。 コネクションプール側で同時最大接続数なども管理しているため、適切にconnectionを切りましょう。
参考: https://qiita.com/hiroshi-ishihara/items/449eb120fece242f1a0d
with文は使いたくないあなたへ autocommitモード
connectionインスタンスのset_sessionメソッドで、すぐ次のsql文の実行を制御できる。
autocommitモードにすると実行後に勝手にcommitしてくれる。ありがとう。
readonryモードにするとINSERTやDELETEなどできなくなる。
詳しくは公式ドキュメントで
import psycopg2
DATABASE_URL = os.environ.get('DATABASE_URL')
SQL = "select * from table名"
# cursorインスタンスを生成するとこまではいつも通り
conn = psycopg2.connect(DATABASE_URL)
cursor = conn.cursor()
# 次のexecuteに対する設定ができる
conn.set_session(readonly=True, autocommit=True)
# 普通に実行してもその後勝手にcommitされる～～
self.cursor.execute (sql)
こちらもconn.close()しない限り接続しっぱなし。