Configure query/command timeout with sqlalchemy create_engine?

Question

The following Python code snippet illustrates the issue:

print("starting")

# I am trying to configure a query/command timeout of one second.
# sqlalchemy docs suggest execution_options but the documented list of options doesn't include a timeout:
# http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.execution_options
# Below, I am guessing at several likely timeout parameter names:
db_engine = create_engine("postgresql://user:pass@server/catalog",
                          execution_options={"timeout": 1.0,
                                             "statement_timeout": 1.0,
                                             "query_timeout": 1.0,
                                             "execution_timeout": 1.0})

with db_engine.connect() as db_connection:
    print("got db_connection")

    # Artificially force a two second query time with pg_sleep.
    # This is designed to guarantee timeout conditions and trigger an exception.
    result_proxy = db_connection.execute("SELECT pg_sleep(2);")

    # If the timeout worked, this statement will not execute.
    # Currently, it does execute, which means my timeout isn't working.
    print("Query successfully complete. Got result_proxy")

Answer 1

You can set configuration values like statement_timeout via the options parameter in libpq. You can access this parameter in psycopg2 as part of the connect call. You can pass additional parameters to the connect call from SQLAlchemy via the connect_args parameter. So, putting it all together:

engine = create_engine(..., connect_args={"options": "-c statement_timeout=1000"})

Answer 2

It took me sometime to figure out how to set multiple options with create_engine(...) . In case, you are also looking for the same -

engine = create_engine(..., connect_args={'options': '-c lock_timeout=3000 -c statement_timeout=3000'})