八月 14, 2023
摘要：在本教程中，您将学习如何使用 psycopg 数据库适配器在 Python 中处理 PostgreSQL 事务。
psycopg 中的事务介绍
在 psycopg 中，connection类负责处理事务。当您使用cursor对象向 PostgreSQL 数据库发出第一个 SQL 语句时，psycopg 会创建一个新事务。
从那时起，psycopg 将在同一事务中执行所有后续语句。如果任何语句失败，psycopg 将中止事务。
connection类有两种结束事务的方法：commit()和rollback()。如果您想将所有更改永久提交到 PostgreSQL 数据库，请调用commit()方法。如果您想取消更改，请调用rollback()方法。关闭连接对象或使用del销毁它也将导致隐式回滚。
需要注意的是，一个简单的SELECT语句将启动一个事务，这可能会导致表膨胀和锁定等不良影响。因此，如果您正在开发长期运行的应用程序，则应在长时间不使用连接之前调用commit()或rollback()方法。
或者，您可以将connection对象的autocommit属性设置为True。这确保 psycopg 执行每个语句后立即提交。
当您执行需要在事务外部执行的语句（例如CREATE DATABASE和 VACUUM）时，该autocommit模式也很有用。
下面显示了 psycopg 中处理事务的典型模式：
#!/usr/bin/python
import psycopg2
conn = None
try:
conn = psycopg2.connect(dsn)
cur = conn.cursor()
# execute 1st statement
cur.execute(statement_1)
# execute 2nd statement
cur.execute(statement_1)
# commit the transaction
conn.commit()
# close the database communication
cur.close()
except psycopg2.DatabaseError as error:
print(error)
finally:
if conn is not None:
conn.close()
Postgres Python 事务示例
我们将使用我们在创建表教程中创建的parts和vendor_parts表进行演示。
假设您需要添加一个新部件并同时指定供应该部件的供应商。为此，首先将新行插入parts表中并获取部件 ID。然后，将行插入vendor_parts表中。下面的add_part()函数演示了这个想法：
#!/usr/bin/python
import psycopg2
from config import config
def add_part(part_name, vendor_list):
# statement for inserting a new row into the parts table
insert_part = "INSERT INTO parts(part_name) VALUES(%s) RETURNING part_id;"
# statement for inserting a new row into the vendor_parts table
assign_vendor = "INSERT INTO vendor_parts(vendor_id,part_id) VALUES(%s,%s)"
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
# insert a new part
cur.execute(insert_part, (part_name,))
# get the part id
part_id = cur.fetchone()[0]
# assign parts provided by vendors
for vendor_id in vendor_list:
cur.execute(assign_vendor, (vendor_id, part_id))
# commit changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
为了测试该add_part()函数，我们调用它来插入一些部件并给它们分配各自的供应商，如下所示：
if __name__ == '__main__':
add_part('SIM Tray', (1, 2))
add_part('Speaker', (3, 4))
add_part('Vibrator', (5, 6))
add_part('Antenna', (6, 7))
add_part('Home Button', (1, 5))
add_part('LTE Modem', (1, 5))
让我们查询parts和vendor_parts表来确认事务。
suppliers=# select * from parts;
part_id | part_name
---------+-------------
1 | SIM Tray
2 | Speaker
3 | Vibrator
4 | Antenna
5 | Home Button
6 | LTE Modem
(6 rows)
suppliers=# select * from vendor_parts;
vendor_id | part_id
-----------+---------
1 | 1
2 | 1
3 | 2
4 | 2
5 | 3
6 | 3
6 | 4
7 | 4
1 | 5
5 | 5
1 | 6
5 | 6
(12 rows)
如您所见，我们已成功将数据插入到parts和vendor_parts表中。
让我们插入另一部分，但这一次，我们故意使用无效的供应商 ID 来进行演示。程序不应在未给新部件分配供应商的情况下添加新部件。
# no rows inserted into the parts and vendor_parts tables
add_part('Power Amplifier', (99,))
发生异常。
insert or update on table "vendor_parts" violates foreign key constraint "vendor_parts_vendor_id_fkey"
DETAIL: Key (vendor_id)=(99) is not present in table "vendors".
我们再次从parts和vendor_parts表中查询数据，没有新数据，因此，该函数的运行符合预期。
使用with语句运行事务
从 psycopg 2.5 开始，连接和游标是上下文管理器，因此您可以将它们与with语句一起使用：
with psycopg2.connect(dsn) as conn:
with conn.cursor() as cur:
cur.execute(sql)
如果with代码块内没有发生异常，Psycopg 就会提交事务，否则会回滚事务。
与其他上下文管理器对象不同，退出with块不会关闭连接，而只会终止事务。因此，您可以在后续的另一个事务的with语句中使用相同的connection对象，如下所示：
conn = psycopg2.connect(dsn)
# transaction 1
with conn:
with conn.cursor() as cur:
cur.execute(sql)
# transaction 2
with conn:
with conn.cursor() as cur:
cur.execute(sql)
conn.close()
在本教程中，您学习了如何使用 psycopg 事务以及如何使用事务在 PostgreSQL 数据库中插入数据。