Sqlalchemy: stopping a long term request

Question

I have a seemingly straightforward situation, but I can’t find a straightforward solution.

I am using sqlalchemy to query postgres. If client timeout occurs, I would like to stop / cancel long postgres requests from another thread. The stream has access to the Session or Connection object.

At this point I tried:

session.bind.raw_connection().close() 

and

 session.connection().close() 

and

 session.close 

and

 session.transaction.close() 

But no matter what I try, the postgres request continues to the end. I know this by watching pg in top. Isn’t it so easy to do? Am I missing something? Is this impossible without receiving a pid and sending a stop signal directly?

Answer 1

This seems to work well so far:

 def test_close_connection(self): import threading from psycopg2.extensions import QueryCanceledError from sqlalchemy.exc import DBAPIError session = Session() conn = session.connection() sql = self.get_raw_sql_for_long_query() seconds = 5 t = threading.Timer(seconds, conn.connection.cancel) t.start() try: conn.execute(sql) except DBAPIError, e: if type(e.orig) == QueryCanceledError: print 'Long running query was cancelled.' t.cancel() 

a source