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

univerio · Accepted Answer · 2017-07-05 22:29:12Z

30

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

answered Jul 5, 2017 at 22:29

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dilip Over a year ago

I'm not able to do this with mysql. I can't find anywhere in the mysql documents about statement timeout.

univerio Over a year ago

@Dilip MySQL is much more limited in functionality, but the closest equivalent (that only works on read-only SELECT statements) is max_execution_time.

Prosunjit Biswas · Accepted Answer · 2022-02-23 19:24:23Z

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

answered Feb 23, 2022 at 19:24

Prosunjit Biswas

1,18311 silver badges17 bronze badges

1 Comment

vmf91 Over a year ago

According to the documentation:

If statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first.

Collectives™ on Stack Overflow

Configure query/command timeout with sqlalchemy create_engine?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related