python - sqlalchemy 未对架构执行注释

Question

这个问题已经有答案了:

SQLalchemy not committing changes when setting role (1 个回答)

Executing multiple statements with Postgresql via SQLAlchemy does not persist changes (3 个回答)

Using python sqlalchemy to execute raw queries with WITH statement (2 个回答)

How can I insert column comments in PostgreSQL via Python? (1 个回答)

已关闭 3 年前。

我不确定为什么对模式的评论不能通过 sqlalchemy 工作，但在 psql 控制台上完美运行，

尝试了各种设置仍然没有成功，架构已创建但无法添加描述。

import sqlalchemy

engine = sqlalchemy.create_engine('postgresql://postgres@localhost/posgres')
engine.execute("CREATE SCHEMA IF NOT EXISTS myschema")
engine.execute("COMMENT ON SCHEMA myschema IS 'Seemly Random Description'")

使用 psql 检查

postgres=# \dn+
                           List of schemas
   Name   │  Owner   │  Access privileges   │      Description       
──────────┼──────────┼──────────────────────┼────────────────────────
 myschema │ postgres │                      │ 
 public   │ postgres │ postgres=UC/postgres↵│ standard public schema
          │          │ =UC/postgres         │ 
(2 rows)

Answer 1

不完全确定原因，但一旦创建连接并运行COMMIT，它似乎就可以工作:

In [8]: conn = engine.connect()                                                                                                                                                                              

In [9]: conn.execute("COMMENT ON SCHEMA myschema IS 'Seemly Random Description'")                                                                                                                            
Out[9]: <sqlalchemy.engine.result.ResultProxy at 0x7fd109991cd0>

In [10]: conn.execute("COMMIT;")                                                                                                                                                                             
Out[10]: <sqlalchemy.engine.result.ResultProxy at 0x7fd0ff3b7d10>

postgres=# \dn+
                            List of schemas
   Name   |  Owner   |  Access privileges   |        Description        
----------+----------+----------------------+---------------------------
 myschema | postgres |                      | Seemly Random Description
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |