Cancel query execution in pyscopg2

How would one go about cancelling execution of a query statement using pyscopg2 (the python Postgres driver)?

A开发者_StackOverflows an example, let's say I have the following code:

import psycopg2
cnx_string = "something_appropriate"

conn = psycopg2.connect(cnx_string)
cur = conn.cursor()
cur.execute("long_running_query")

Then I want to cancel the execution of that long running query from another thread - what method would I have to call on the connection/cursor objects to do this?

You can cancel a query by calling the pg_cancel_backend(pid) PostgreSQL function in a separate connection.

You can know the PID of the backend to cancel from the connection.get_backend_pid() method of psycopg2 (available from version 2.0.8).

The connection object has a cancel member. Using this and threading you could use

sqltimeout = threading.Timer(sql_timeout_seconds, conn.cancel)
sqltimeout.start()

When the timer expires, the cancel is sent to the connection and an exception will be raised by the server. Don't forget to cancel the timer when the query normally finishes....

sqltimeout.cancel()

psycopg2's async execution support has been removed.

If you can use py-postgresql and its transactions (it's py3k), the internal implementation is asynchronous and supports being interrupted.