使用psycopg2中的语句创建事务

Question

我试图使用psycopg2向表中添加一些新列。PostgreSQL缺少一个ALTER TABLE table ADD COLUMN IF NOT EXISTS，所以我在它自己的事务中添加每一列。如果该列存在，则将有一个python&；postgres错误，没关系，我希望我的程序继续，并尝试添加下一列。其目标是使其成为幂等函数，因此可以连续运行多次

目前看起来是这样的：

def main():
    # <snip>
    with psycopg2.connect("") as connection:
        create_columns(connection, args.table)

def create_columns(connection, table_name):
    def sql(sql):
        with connection.cursor() as cursor:
            cursor.execute(sql.format(table_name=table_name))

    sql("ALTER TABLE {table_name} ADD COLUMN my_new_col numeric(10,0);")
    sql("ALTER TABLE {table_name} ADD COLUMN another_new_col INTEGER NOT NULL;")

但是，如果存在my_new_col，则会出现异常ProgrammingError('column "parent_osm_id" of relation "relations" already exists\n',)，这是意料之中的，但当它尝试添加another_new_col时，会出现异常InternalError('current transaction is aborted, commands ignored until end of transaction block\n',)

psycogpg2 document for the ^{} statement表示with connection.cursor() as cursor:将在事务中包装该代码。这显然没有发生。实验表明，我需要两个级别的with语句来包含pscyopg2.connect调用，然后得到一个事务

我如何传递^{} object并让查询在它们自己的事务中运行以允许这种“优雅的错误处理”？我想保持postgres连接代码独立，采用“干净的体系结构”风格。这可能吗

Answer 1

The psycogpg2 document for the with statement implies that the with connection.cursor() as cursor: will wrap that code in a transaction.

事实并非如此，它说：

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
       curs.execute(SQL)

When a connection exits the with block, if no exception has been raised by the block, the transaction is committed. In case of exception the transaction is rolled back. In no case the connection is closed: a connection can be used in more than a with statement and each with block is effectively wrapped in a transaction.

所以它不是关于由with处理的游标对象，而是连接对象

还值得注意的是，当我们离开with子句时，游标持有的所有资源都将被释放

When a cursor exits the with block it is closed, releasing any resource eventually associated with it. The state of the transaction is not affected.

因此，回到您的代码，您可能会将其重写为：

def main():
    # <snip>
    with psycopg2.connect("") as connection:
        create_columns(connection, args.table)

def create_columns(con, table_name):
    def sql(connection, sql):
        with connection:
            with connection.cursor() as cursor:
                cursor.execute(sql.format(table_name=table_name))

    sql(con, "ALTER TABLE {table_name} ADD COLUMN my_new_col numeric(10,0);")
    sql(con, "ALTER TABLE {table_name} ADD COLUMN another_new_col INTEGER NOT NULL;")

确保为执行的每个查询将连接包装在with中，因此如果失败，连接上下文管理器将恢复事务