Python psycopg2 交易管理

Python使用psycopg2存取PostgreSQL資料庫時的交易管理。

環境

• Python 3.13.3
• PostgreSQL 17
• psycopg2 2.9.10

事前要求

參考「Python 存取PostgreSQL資料庫」了解psycopg2的基本用法。

建立資料表

在PostgreSQL資料庫建立一個資料表employee。

CREATE TABLE public.employee (
	id int4 NOT NULL,
	"name" varchar NULL,
	CONSTRAINT employee_pk PRIMARY KEY (id)
);

在employee資料表新增一筆資料如下：

INSERT INTO public.employee
(id, "name")
VALUES(1, 'john');

交易管理

以下為psycopg的資料庫交易管理，使用with取得context manager包裝的connection和cursor物件，在with區塊內若發生錯誤，則同區塊內的SQL操作會自動回滾。

import psycopg2

# 取得連線
conn = psycopg2.connect(database = "postgres", 
                        user = "postgres", 
                        host= 'localhost',
                        password = "12345",
                        port = 5432)

with conn: # connectoin context manager
    with conn.cursor() as cur: # cursor context manager
        cur.execute("INSERT INTO employee (id, name) VALUES (2, 'mary')") # 成功
        cur.execute("INSERT INTO employee (id, name) VALUES (NULL, 'tony')") # 錯誤，emploiyee.id為主鍵不可為NULL，因此回滾with區塊中的SQL操作

測試

執行後印出以下，因為with區塊中發生錯誤，則該區塊中的SQL操作會回滾；若未發生錯誤則會自動commit並關閉cursor和連線資源。

Traceback (most recent call last):
  File "c:\..\demo.py", line 12, in <module>
    cur.execute("INSERT INTO employee (id, name) VALUES (NULL, 'tony')")
    ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.NotNullViolation: null value in column "id" of relation "employee" violates not-null constraint
DETAIL:  Failing row contains (null, tony).

資源釋放的方式類似Java的try-with-resources。

• Psycopg 2.9.10 doc - Transactions control