如何通过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屋!