Python SQLAlchemy PostgreSQL Deprecated API features

Question

I am using following code to create the function and trigger to update the created_at and updated_at fields. with upgrade of new module getting the deprecated API warning.

How can I replace engine.execute(sa.text(create_refresh_updated_at_func.format(schema=my_schema))) line to remove the warning message?

Code:

mapper_registry.metadata.create_all(engine, checkfirst=True)

    create_refresh_updated_at_func = """
     CREATE OR REPLACE FUNCTION {schema}.refresh_updated_at()
        RETURNS TRIGGER AS $$
        BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
        """

    my_schema = "public"
    engine.execute(sa.text(create_refresh_updated_at_func.format(schema=my_schema)))

Warrning:

RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to "sqlalchemy<2.0". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings. Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) engine.execute(sa.text(create_refresh_updated_at_func.format(schema=my_schema)))

Answer 1

SQLAlchemy no longer supports autocommit at the library level. You need to run the execute within a transaction.

This should work:

with engine.begin() as conn:
   conn.execute(text(create_refresh_updated_at_func.format(schema=my_schema)))

migration-core-connection-transaction

You could also use the driver-level isolation level like this but I think the connections from this pool will all be set to autocommit:

engine2 = create_engine(f"postgresql+psycopg2://{username}:{password}@/{db}", isolation_level='AUTOCOMMIT')
with engine2.connect() as conn:
    conn.execute(text(create_refresh_updated_at_func.format(schema=my_schema)))