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.")To manually check for in-progress transaction you could use PostgreSQL's statistics collector:
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.,The connection can be also set in “autocommit” mode: no transaction is automatically open, commands have immediate effect. See Transactions control for details.,Roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.,The default level is ISOLATION_LEVEL_DEFAULT: at this level a transaction is automatically started the first time a database command is executed. If you want an autocommit mode, switch to ISOLATION_LEVEL_AUTOCOMMIT before executing any command:
conn = psycopg2.connect(DSN) with conn: with conn.cursor() as curs: curs.execute(SQL1) with conn: with conn.cursor() as curs: curs.execute(SQL2) # leaving contexts doesn 't close the connection conn.close()
conn.set_session(readonly = True)
conn.set_session(readonly = True, autocommit = True)
>>> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>>> cur.execute("CREATE TABLE foo (id serial PRIMARY KEY);") >>>
pprint(conn.notices)['NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"\n',
'NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"\n']>>>
import ctypes
>>>
import ctypes.util >>>
libpq = ctypes.pydll.LoadLibrary(ctypes.util.find_library('pq')) >>>
libpq.PQserverVersion.argtypes = [ctypes.c_void_p] >>>
libpq.PQserverVersion.restype = ctypes.c_int >>>
libpq.PQserverVersion(conn.pgconn_ptr)
906115 days ago Sep 25, 2015 · 1 Answer. Sorted by: 5. You can check the connection's status attribute: from psycopg2.extensions import STATUS_BEGIN, STATUS_READY if conn.status == … ,You can check the connection's status attribute:,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., In psycopg, the connection class is responsible for handling transactions. When you issue the first SQL statement to the PostgreSQL database using a cursor object, psycopg creates a new transaction.
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.")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.")SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';In psycopg, the connection class is responsible for handling transactions. When you issue the first SQL statement to the PostgreSQL database using a cursor object, psycopg creates a new transaction.,Starting from psycopg 2.5, the connection and cursor are Context Managers and therefore you can use them with the with statement:,From that moment, psycopg executes all the subsequent statements in the same transaction. If any statement fails, psycopg will abort the transaction.,Alternatively, you can set the autocommit attribute of the connection object to True. This ensures that psycopg executes every statement and commits it immediately.
The following shows a typical pattern for handling a transaction in psycopg:
.wp - block - code {
border: 0;
padding: 0;
}
.wp - block - code > div {
overflow: auto;
}
.shcb - language {
border: 0;
clip: rect(1 px, 1 px, 1 px, 1 px); -
webkit - clip - path: inset(50 % );
clip - path: inset(50 % );
height: 1 px;
margin: -1 px;
overflow: hidden;
padding: 0;
position: absolute;
width: 1 px;
word - wrap: normal;
word - break: normal;
}
.hljs {
box - sizing: border - box;
}
.hljs.shcb - code - table {
display: table;
width: 100 % ;
}
.hljs.shcb - code - table > .shcb - loc {
color: inherit;
display: table - row;
width: 100 % ;
}
.hljs.shcb - code - table.shcb - loc > span {
display: table - cell;
}
.wp - block - code code.hljs: not(.shcb - wrap - lines) {
white - space: pre;
}
.wp - block - code code.hljs.shcb - wrap - lines {
white - space: pre - wrap;
}
.hljs.shcb - line - numbers {
border - spacing: 0;
counter - reset: line;
}
.hljs.shcb - line - numbers > .shcb - loc {
counter - increment: line;
}
.hljs.shcb - line - numbers.shcb - loc > span {
padding - left: 0.75 em;
}
.hljs.shcb - line - numbers.shcb - loc::before {
border - right: 1 px solid #ddd;
content: counter(line);
display: table - cell;
padding: 0 0.75 em;
text - align: right; -
webkit - user - select: none; -
moz - user - select: none; -
ms - user - select: none;
user - select: none;
white - space: nowrap;
width: 1 % ;
}
#!/usr/bin / python
import psycopg2
conn = None
try:
conn = psycopg2.connect(dsn)
cur = conn.cursor()
# execute 1 st statement
cur.execute(statement_1)
# execute 2n d statement
cur.execute(statement_1)
# commit the transaction
conn.commit()
# close the database communication
cur.close()
except psycopg2.DatabaseError as error:
print(error)
finally:
if conn is not None:
conn.close()
Code language: Python(python)Suppose you need to add a new part and assign the vendors who supply the part at the same time. To do this, first, you insert a new row into the parts table and get the part id. Then, you insert rows into the vendor_parts table. The following add_part() function demonstrates the idea:
#!/usr/bin/python
import psycopg2
from config
import config
def add_part(part_name, vendor_list):
# statement
for inserting a new row into the parts table
insert_part = "INSERT INTO parts(part_name) VALUES(%s) RETURNING part_id;"
# statement
for inserting a new row into the vendor_parts table
assign_vendor = "INSERT INTO vendor_parts(vendor_id,part_id) VALUES(%s,%s)"
conn = None
try:
params = config()
conn = psycopg2.connect( ** params)
cur = conn.cursor()
# insert a new part
cur.execute(insert_part, (part_name, ))
# get the part id
part_id = cur.fetchone()[0]
# assign parts provided by vendors
for vendor_id in vendor_list:
cur.execute(assign_vendor, (vendor_id, part_id))
# commit changes
conn.commit()
except(Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
Code language: Python(python)To test the add_part() function, we call it to insert some parts and assign them to the respective vendors as follows:
if __name__ == '__main__':
add_part('SIM Tray', (1, 2))
add_part('Speaker', (3, 4))
add_part('Vibrator', (5, 6))
add_part('Antenna', (6, 7))
add_part('Home Button', (1, 5))
add_part('LTE Modem', (1, 5)) Code language: Python(python)Let’s insert another part, but this time, we use an invalid vendor id purposefully for the demonstration purpose. The program should not add a new part without assigning it to a vendor.
# no rows inserted into the parts and vendor_parts tables
add_part('Power Amplifier', (99, )) Code language: Python(python)An exception occurred.
insert or update on table "vendor_parts"
violates foreign key constraint "vendor_parts_vendor_id_fkey"
DETAIL: Key(vendor_id) = (99) is not present in table "vendors".Code language: JavaScript(javascript)The connection can be also set in “autocommit” mode: no transaction is automatically open, commands have immediate effect. See Transactions control for details.,By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.,Read/write attribute: if True, no transaction is handled by the driver and every statement sent to the backend has immediate effect; if False a new transaction is started at the first command execution: the methods commit() or rollback() must be manually invoked to terminate the transaction.,Read or set the transaction isolation level for the current session. The level defines the different phenomena that can happen in the database between concurrent transactions.
>>> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>>> cur.execute("CREATE TABLE foo (id serial PRIMARY KEY);") >>>
pprint(conn.notices)['NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"\n',
'NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"\n']