SQLalchemy 在设置角色时不提交更改 [英] SQLalchemy not committing changes when setting role

问题描述

我正在使用 sqlalchemy 引擎创建表，但是即使我的 create 语句执行没有错误，当我尝试预先设置角色时，这些表也没有显示在数据库中.

I'm creating tables using a sqlalchemy engine, but even though my create statements execute without error, the tables don't show up in the database when I try to set the role beforehand.

url = 'postgresql://{}:{}@{}:{}/{}'
url = url.format(user, password, host, port, db)

engine = sqlalchemy.create_engine(url)

# works fine
engine.execute("CREATE TABLE testpublic (id int, val text); \n\nINSERT INTO testpublic VALUES (1,'foo'), (2,'bar'), (3,'baz');")
r = engine.execute("select * from testpublic")
r.fetchall() # returns expected tuples
engine.execute("DROP TABLE testpublic;")

# appears to succeed/does NOT throw any error
engine.execute("SET ROLE read_write; CREATE table testpublic (id int, val text);")

# throws error "relation testpublic does not exist"
engine.execute("select * FROM testpublic")

对于上下文，我使用的是 python 3.6、sqlalchemy 版本 1.2.17 和 postgres 11.1 并且角色read_write"绝对存在并且具有在公共创建表的所有必要权限(我在运行上面的确切序列时没有问题pgadmin).

For context, I am on python 3.6, sqlalchemy version 1.2.17 and postgres 11.1 and the role "read_write" absolutely exists and has all necessary permissions to create a table in public (I have no problem running the exact sequence above in pgadmin).

有谁知道为什么会这样以及如何解决?

Does anyone know why this is the case and how to fix?

推荐答案

这里的问题是 sqlalchemy 如何决定在每个语句之后发出一次提交.

The issue here how sqlalchemy decides to issue a commit after each statement.

如果文本被传递给 engine.execute，sqlalchemy 将尝试使用以下正则表达式确定文本是 DML 还是 DDL.你可以在这里找到它一个>

if a text is passed to engine.execute, sqlalchemy will attempt to determine if the text is a DML or DDL using the following regex. You can find it in the sources here

AUTOCOMMIT_REGEXP = re.compile(
    r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER)", re.I | re.UNICODE
)

这仅检测位于文本开头的单词，忽略任何前导空格.因此，虽然您第一次尝试 # 工作正常，但第二个示例无法识别需要在语句执行后发出提交，因为第一个单词是 SET.

This only detects the words if they're at the start of the text, ignoring any leading whitespaces. So, while your first attempt # works fine, the second example fails to recognize that a commit needs to be issued after the statement is executed because the first word is SET.

相反，sqlalchemy 发出回滚，因此它# 似乎成功/不抛出任何错误.

Instead, sqlalchemy issues a rollback, so it # appears to succeed/does NOT throw any error.

最简单的解决方案是手动提交.

the simplest solution is to manually commit.

示例:

engine.execute("SET ROLE read_write; CREATE table testpublic (id int, val text); COMMIT;")

或者，将 sql 包裹在 text 中并设置 autocommit=True, 如文档中所示

or, wrap the sql in text and set autocommit=True, as shown in the documentation

stmt = text('set role read_write; create table testpublic (id int, val text);').execution_options(autocommit=True)
e.execute(stmt)

这篇关于SQLalchemy 在设置角色时不提交更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！