CODEWITHSUNDEEP
pythonpostgresqlpsycopg2python-db-api
jwoolard
jwoolard

Reputation: 6284

Cancel query execution in pyscopg2

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

As 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?

Upvotes: 9

Views: 4205

Answers (2)

user123664
user123664

Reputation:

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

Upvotes: 2

piro
piro

Reputation: 13931

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

Upvotes: 7

Related Questions