set a query timeout on a per query basis

[Brian Cherinka]

What's the best way to set a timeout for specific queries?  I have a custom query tool that uses SQLAlchemy to build and submit queries.  This tool can be used in a local python session with a database.  I'm also using it to allow queries in a Flask web-app. In general, I do not want to apply a time limit to all queries, but I only want to apply a time limit of 1 minute to queries submitted through the web app.  Given a built SQLAlchemy query and a db session instance, is there any way to set a timeout through the query or session objects without having to recreate a database connection?  

[Mike Bayer]

On Mon, Nov 12, 2018 at 2:08 PM Brian Cherinka <havo...@gmail.com> wrote:
>
> What's the best way to set a timeout for specific queries? I have a custom query tool that uses SQLAlchemy to build and submit queries. This tool can be used in a local python session with a database. I'm also using it to allow queries in a Flask web-app. In general, I do not want to apply a time limit to all queries, but I only want to apply a time limit of 1 minute to queries submitted through the web app. Given a built SQLAlchemy query and a db session instance, is there any way to set a timeout through the query or session objects without having to recreate a database connection?

There's no set way to do that at the SQLAlchemy level, this depends
very much on the database and the Python driver you are using and may
not be possible for every driver.

>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Brian Cherinka]

Hmm. Ok.  I'm using a postgres database with the psycopg2 driver.  I'm aware of the `statement_timeout` option in `postgres` which I can pass into the psycopg2 `connect` method.  As far as I can tell there's not a way to pass that keyword in through SQLAlchemy after the db engine has been created.  Is that correct?  Does the `query.execution_options`, or something in session, accept that keyword?    

[Mike Bayer]

On Mon, Nov 12, 2018 at 6:12 PM Brian Cherinka <havo...@gmail.com> wrote:
>
> Hmm. Ok. I'm using a postgres database with the psycopg2 driver. I'm aware of the `statement_timeout` option in `postgres` which I can pass into the psycopg2 `connect` method. As far as I can tell there's not a way to pass that keyword in through SQLAlchemy after the db engine has been created. Is that correct? Does the `query.execution_options`, or something in session, accept that keyword?

if statement_timeout is accepted only on the "connect" method and not
once you have already connected, then it would need to be set for the
Engine globally. You would do this using the "connect" event:
https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connect%20event#sqlalchemy.events.PoolEvents.connect

Otherwise, if it can be set on a cursor, you can use
execution_options(), which you intercept with a before_cursor_execute
event:

@event.listens_for(Engine, "before_cursor_execute")
def _set_timeout(conn, cursor, stmt, params, context, executemany):
timeout = conn._execution_options.get('timeout', None)
if timeout:
cursor.statement_timeout = timeout

query.execution_options() accepts whatever keywords you pass into it
and you can get them out inside that event handler (or anywhere you
have a Connection).

[Jonathan Vanasco]

In postgres, you can execute:

    SET statement_timeout = 60000;

at any point.  It lasts until the end of the "session", which I believe would be the SqlAlchemy connection's lifetime. 

[Mike Bayer]

you can set that then with before_cursor_execute() and then reset it
on after_cursor_execute().