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

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

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.