How do I check for open transactions on a psycopg2 connection?

Question

How can I check for open transactions on a psycopg2 connection? I intend to add it to my unit/functional tests since Python's DB API uses implicit transactions.

Answer 1

You can check the connection's status attribute:

from psycopg2.extensions import STATUS_BEGIN, STATUS_READY

if conn.status == STATUS_READY:
    print("No transaction in progress.")
elif conn.status == STATUS_BEGIN:
    print("A transaction is in progress.")

Alternatively, the transaction status can be obtained with connection.get_transaction_status().

To manually check for in-progress transaction you could use PostgreSQL's statistics collector:

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';