SQLalchemy设置角色时不提交更改

Question

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

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中运行上面的确切序列没有问题）。

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

Answer 1

此处的问题是sqlalchemy如何决定在每个语句之后发出提交。

如果将文本传递给engine.execute，则sqlalchemy将尝试使用以下正则表达式确定该文本是DML还是DDL。您可以在这里的资源中找到它

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

仅当单词在文本开头时才检测这些单词，而忽略任何前导空格。因此，在您第一次尝试时# works fine，第二个示例无法识别出执行语句后需要发出提交，因为第一个单词是SET。

相反，sqlalchemy发出回滚，因此它# appears to succeed/does NOT throw any error。

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

例：

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

或者，将sql包装在textset中autocommit=True，如文档所示

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