mogdb psycopg2 驱动实现savepoint 功能

原作者：李华

客户在使用 mogdb psycopg2驱动执行一个事物过程中希望,部分的失败不影响已经成功的事务的提交，在jdbc odbc驱动中都可以使用参数控制是否开启savepoint功能,在psycopg2驱动中无此选项需要修改代码实现。
希望实现的业务逻辑：

如果按照默认的逻辑：

失败的insert 必须回滚而且会把成功的事务也进行回滚处理,使用python 实现该逻辑

import psycopg2
from psycopg2 import errorcodes
from datetime import datetime

def insert_data(cursor, value):
    sql = "INSERT INTO test.t_user (user_id, status) VALUES (%s, %s)"
    cursor.execute(sql, value)

def test_1():
    try:
        start = datetime.now()
        conn = psycopg2.connect(dbname='test', host='10.88.2.11', port=29000, user='test', password='Test#123')
        print("Connect cost [%s]" % (str(datetime.now() - start)))

        conn.autocommit = False  

        cursor = conn.cursor()

        values_to_insert = [('ho', '2'), ('li', '3'),('hua','3'),('mog','3')]  # Values to insert

        try:
   
            savepoint_counter = 0

            for value in values_to_insert:
                savepoint_name = f"sp_{savepoint_counter}"
                cursor.execute(f"SAVEPOINT {savepoint_name}")
                print(f"savepoint: {savepoint_name}")


                try:
                    insert_data(cursor, value)
                    #conn.commit()  
                    print(f"Insert successful: {value}")
                except psycopg2.IntegrityError as e:
                    if e.pgcode != errorcodes.UNIQUE_VIOLATION:
                        raise e

                    print("Unique constraint violation, rolling back to previous savepoint")
                    cursor.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name}")
                    print(f"Insert failed: {value}")
                    print(f"ROLLBACK TO SAVEPOINT: {savepoint_name}") 

                savepoint_counter += 1

            conn.commit()
            print("commit")

        except Exception as e:
            print("Outer error:", e)

    finally:
        if conn:
            conn.close()

def main():
    test_1()

if __name__ == '__main__':
    main()
复制