如何通过 Python 在 PostgreSQL 中插入列注释? [英] How can I insert column comments in PostgreSQL via Python?

问题描述

我想为 PostgreSQL 数据库中 Python 的多个列添加注释.如果我手动运行 Python 脚本在数据库客户端中生成的语句，则一切正常.如果我让 Python 通过 sqlalchemy 引擎运行语句，则不会更新任何内容.

I want to add a comment to a number of columns from Python in a PostgreSQL database. If I run the statement that my Python script produces in a database client manually everything works as it should. If I let Python run the statement via an sqlalchemy engine, nothing is updated.

我有一个 Python 字典，格式为 { 'column name': 'column comment with some text'}.

I have a dictionary in Python of the form { 'column name': 'column comment with some text'}.

我想将此注释添加到 Postgres 数据库中的现有表中.

I want to add this comments to an existing table in a Postgres database.

我会在 Postgres 中手动运行以下命令:

Manually I would run the following command in Postgres:

comment on column schema.table."column name" is 'column comment with some text'

现在在 Python 中我想运行相同的，但然后循环字典.这是我使用的代码:

Now in Python I want to run the same, but then looping over the dictionary. This is the code I use:

from sqlalchemy import create_engine, text

db = create_engine(f"postgresql://PGUSER:PGPASSWORD@PGHOST/PGDATABASE")

coldict = {'col1': 'col1 contains this data, etc... some more comments', 'col2': 'col2 shows something.'}

with db.connect() as con:
        for key in coldict:
            colname = key
            # Convert a single quote to two single quotes as that is what SQL likes.
            colcomm = coldict[key].translate(str.maketrans({"'": "''"}))
            stmt = f"comment on column schema.table."{colname}" is '{colcomm}';"
            # print the statement that will be run for debugging
            print(stmt)
            # execute statement in database
            con.execute(text(stmt))

打印出来:

comment on column schema.table."col1" is 'col1 contains this data, etc... some more comments';
comment on column schema.table."col2" is 'col2 shows something.';

当我使用来自此答案的查询检查列评论时，实际上并未设置任何评论.

When I check the column comments with the query from this answer no comments are actually set.

如果我将打印出来的内容复制粘贴到数据库客户端并在那里运行，则列注释会按原样更新.

If I copy-paste what is printed out into a database client and run it there, the column comment is updated as it should.

如何通过 Python 中的循环更新列注释?

How can I update the column comment via a loop in Python?

推荐答案

您没有提交更改，因此它们会自动回滚.一种方法是:

You did not commit your changes, so they are automatically rolled back. One way to do it is:

con.execute(text(stmt).execution_options(autocommit=True))

这篇关于如何通过 Python 在 PostgreSQL 中插入列注释?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！