Python - PostgreSQL(Psycopg)メモ - SAVEPOINTしてみる 2017/5
Version 3.6.1 on CentOS7

使用するテーブル

1. ROLLBACK TO SAVEPOINT

import psycopg2import psycopg2

# SELECTメソッド
def do_select(sql):
    cur.execute(sql)
    for row in cur:
            print(row)

# main
print("----- START -----")

constr = "host=localhost port=5432 dbname=dbname user=user password=password"
conn = psycopg2.connect(constr)

cur = conn.cursor()

sql = "INSERT INTO test_table_1 (test_1_memo) VALUES ('テスト１')"
cur.execute(sql)

cur.execute("SAVEPOINT my_savepoint")

sql = "INSERT INTO test_table_1 (test_1_memo) VALUES ('テスト２')"
cur.execute(sql)

cur.execute("ROLLBACK TO SAVEPOINT my_savepoint")

sql = "INSERT INTO test_table_1 (test_1_memo) VALUES ('テスト３')"
cur.execute(sql)

conn.commit()

sql = "SELECT * FROM test_table_1"
do_select(sql)

print("----- END -----")

実行結果

----- START -----
(1, 'テスト１', False, datetime.datetime(2017, 5, 2, 10, 20, 11, 270135), None, None)
(2, 'テスト３', False, datetime.datetime(2017, 5, 2, 10, 20, 11, 270135), None, None)
----- END -----

2.RELEASE SAVEPOINT

import psycopg2
import psycopg2

# SELECTメソッド
def do_select(sql):
    cur.execute(sql)
    for row in cur:
            print(row)

# main
print("----- START -----")

constr = "host=localhost port=5432 dbname=kosdech_test user=kosdech password=kosdech"
conn = psycopg2.connect(constr)

cur = conn.cursor()

sql = "INSERT INTO test_table_1 (test_1_memo) VALUES ('テストＡ')"
cur.execute(sql)

cur.execute("SAVEPOINT my_savepoint")

sql = "INSERT INTO test_table_1 (test_1_memo) VALUES ('テストＢ')"
cur.execute(sql)

cur.execute("RELEASE SAVEPOINT my_savepoint")

conn.commit()

sql = "SELECT * FROM test_table_1"
do_select(sql)

print("----- END -----")

実行結果

(1, 'テストＡ', False, datetime.datetime(2017, 5, 2, 10, 27, 13, 221348), None, None)
(2, 'テストＢ', False, datetime.datetime(2017, 5, 2, 10, 27, 13, 221348), None, None)