The issue here how sqlalchemy decides to issue a commit after each statement.
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
)
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
.
Instead, sqlalchemy issues a rollback, so it # appears to succeed/does NOT throw any error
.
the simplest solution is to manually commit.
example:
engine.execute("SET ROLE read_write; CREATE table testpublic (id int, val text); COMMIT;")
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)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…