python - SQLalchemy not committing changes when setting role

Question

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); 

INSERT 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")

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?

Answer 1

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)