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

以下は最低これだけわかれば良いというやつ。簡単だね～。

psycopg2.py

# まず、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しとけってことか。

connect.py

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などできなくなる。

詳しくは公式ドキュメントで

connect.py

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()しない限り接続しっぱなし。