PostgreSQL Python 教程: 事务

八月 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 事务示例

我们将使用我们在创建表教程中创建的partsvendor_parts表进行演示。

parts_vendors_tables

假设您需要添加一个新部件并同时指定供应该部件的供应商。为此,首先将新行插入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))

让我们查询partsvendor_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)

如您所见,我们已成功将数据插入到partsvendor_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".

我们再次从partsvendor_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 数据库中插入数据。

了解更多

PostgreSQL Python 教程