SQLAlchemy 2.0 `with engine.connect() …` does not automatically commit

SQLAlchemy 2.0 `with engine.connect() …` does not automatically commit

Problem Description:

I’m currently experimenting with the new way to execute statements using sqlalchemy and updated it to 2.0.0.b4. According to the documentation I shall not use a string as an input variable for the execute function:

Deprecated since version 2.0: passing a string to Connection.execute() is deprecated and will be removed in version 2.0. Use the text() construct with Connection.execute(), or the Connection.exec_driver_sql() method to invoke a driver-level SQL string.

When I try the "old way" with a string as input I run into the expected Error:
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object:

Therefore I tried 3 ways according to the documentation:

from sqlalchemy import text

stmt = 'CREATE SCHEMA IF NOT EXISTS someschema;'

with pg_engine.connect() as connection:
    # connection.exec_driver_sql(stmt)
    # connection.execute(text(stmt))
    connection.execute(text(stmt).execution_options(autocommit=True))

The code runs without any Error, sadly no new schema is created.

Am I the only one with this problem?
Or what am I doing wrong?

Solution – 1

As explained in the SQLAlchemy tutorial here, with engine.connect() as conn: — the "commit as you go" method — does not automatically commit on exiting the with block, while with engine.begin() as conn: — the "begin once" method — does automatically commit (unless an error has occurred).