In this lesson, you will learn to manage PostgreSQL transactions from Python using psycopg2. A transaction is a unit of work that involves a set of database operations.
Further Reading:
In most cases, we want to execute a PostgreSQL database operation only when another process completes. For example, in the Online Bank Transaction system, we can only deposit money to account B from account A only when a withdrawal is successful.
In the transaction, either all of the operations are executed or none of the operations performed. I.e., We need to complete all the operations under a single transaction successfully to call it a successful transaction.
This section covers the following topics: –
- Enabling and Disabling auto-commit Mode.
- Commit transactions to make changes persistent in the database.
- Maintain the ACID property of the transaction.
- Rollback all the operations in case of failure.
- Change PostgreSQL transaction isolation level from Python.
Table of contents
Psycopg2 Transactions control
The connection
object handles the PostgreSQL transactions. The connection object is responsible for making changes persistent in the database or reverting it in transaction failure.
Using the cursor
object, we execute database operations. We can create any number of cursor objects from the connection object. If any of the cursor’s commands fails or the transaction gets aborted, no further commands execute until a call to the connection.rollback()
method.
- The connection object is responsible for terminating its transaction. There are two ways to do that calling either the
commit()
orrollback()
method. - Using the
commit()
method changes are committed and immediately made persistent into the database. - Using the
rollback()
method we can revert the database changes.
Calling a close()
method or any means of destroying the connection object will result in an implicit rollback() call, i.e., all changes get reverted.
Autocommit
The Syntax of AutoCommit
connection.autocommit = True or False
By default, the connection is in auto-commit mode. i.e., the default value is True
. If any query executes successfully, changes are immediately committed to the database, and no rollback is possible.
To run queries inside a transaction, we need to disable auto-commit. Using the connection.autocommit=False
, so we can revert the executed operation in case of intermediate transaction failure.
commit
The syntax of commit()
method
connection.commit()
If all operations complete successfully, then use the connection.commit()
to persist the database changes to maintain data integrity and consistency. If commit()
is not called, the effect of any data manipulation will get lost.
Rollback
If any of the operations fail inside of a running transaction, you can revert the changes made by all of the previous operations using a connection.rollback()
The Syntax of rollback()
connection.rollback()
If any of the operations fail inside of a running transaction, you can revert the changes made by all of the previous operations using a connection.rollback()
.
Steps to manage PostgreSQL transactions from Python
- Connect to database
- Disable Auto-commit mode.
- Run set of SQL queries one by one
- If all operations under a transaction complete successfully, use
commit()
to persist the database’s changes. - If any of the queries fail to execute, then revert the changes made by all operations using a
rollback()
Note: The transaction will remain open until an explicit commit()
or rollback()
.
Python Example to manage PostgreSQL transactions
import psycopg2
try:
connection = psycopg2.connect(user="postgres",
password="pass@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
connection.autocommit = False
cursor = connection.cursor()
amount = 2500
query = """select balance from account where id = 624001562408"""
cursor.execute(query)
record = cursor.fetchone()[0]
balance_account_A = int(record)
balance_account_A -= amount
# Withdraw from account A now
sql_update_query = """Update account set balance = %s where id = 624001562408"""
cursor.execute(sql_update_query, (balance_account_A,))
query = """select balance from account where id = 2236781258763"""
cursor.execute(query)
record = cursor.fetchone()[0]
balance_account_B = int(record)
balance_account_B += amount
# Credit to account B now
sql_update_query = """Update account set balance = %s where id = 2236781258763"""
cursor.execute(sql_update_query, (balance_account_B,))
# commiting both the transction to database
connection.commit()
print("Transaction completed successfully ")
except (Exception, psycopg2.DatabaseError) as error:
print("Error in transction Reverting all other operations of a transction ", error)
connection.rollback()
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
Output
Transaction completed successfully PostgreSQL connection is closed
You could get the following output if any of the operations within a transaction failed.
Error in transction Reverting all other operations of a transction PostgreSQL connection is closed
With
Statement to control Transactions
If you want to create a transaction within a specific block in code, use the with
statement.
If you want to create a transaction within a specific block in code, use the with
statement.
Psycopg2’s connections and cursors are nothing but context managers and can be used with
the with statement. The main advantage of using with
block is you done don’t need to do any explicit commit and rollback.
Syntax:
with psycopg2.connect(connection_arguments) as conn:
with conn.cursor() as cursor:
cursor.execute(Query)
When a connection exit the with
block and the query is executed without any error and exception, the transaction automatically gets committed, else the transaction is rolled back.
When you get out of the with
block’s scope, it doesn’t close the connection, But the cursor and other resources associated with it get closed. A connection is available to use for other with
blocks.
Each with
block is nothing but a different transaction.
Let see the demo. In this example, we will perform an online shopping transaction. Purchase an item, deduct the balance from the customer’s wallet and add it to the company’s account. We are placing all queries inside the with
block.
import psycopg2
connection = psycopg2.connect(user="postgres",
password="pass@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
with connection:
with connection.cursor() as cursor:
query = """select price from itemstable where itemid = 876"""
cursor.execute(query)
record = cursor.fetchone()[0]
Itemprice = int(record)
# find customer's ewallet balance
query = """select balance from ewallet where userId = 23"""
cursor.execute(query)
record = cursor.fetchone()[0]
ewalletBalance = int(record)
new_EwalletBalance = ewalletBalance
new_EwalletBalance -= Itemprice
# Withdraw from ewallet now
sql_update_query = """Update ewallet set balance = %s where id = 23"""
cursor.execute(sql_update_query, (new_EwalletBalance,))
# add to company's account
query = """select balance from account where accountId = 2236781258763"""
cursor.execute(query)
record = cursor.fetchone()
accountBalance = int(record)
new_AccountBalance = accountBalance
new_AccountBalance += Itemprice
# Credit to company account now
sql_update_query = """Update account set balance = %s where id = 2236781258763"""
cursor.execute(sql_update_query, (new_AccountBalance,))
print("Transaction completed successfully ")
Psycopg2’s Isolation level constants
In database systems, Using Isolation levels, we can determine what level of transaction integrity is visible to other users and systems.
For example, when a user is performing some action or operation and the operation is not completed yet, that operation details are available for other users to perform some concurrent actions. For example, a user purchasing some items, these operation details get shared with other system users for preparing bills, item receipts and calculate discounts to speed up the process.
If the isolation level is low, many users can access the same data at the same time. But it can also lead to many concurrency issues such as dirty read and lost update. So it would be best if you use the isolation level considering all these points. A higher isolation level may block other users or transactions from completing themselves first.
psycopg2.extensions
provide us the following isolation levels:
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT
psycopg2.extensions.ISOLATION_LEVEL_READ_UNCOMMITTED
psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED
psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ
psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
psycopg2.extensions.ISOLATION_LEVEL_DEFAULT
Read more Psycopg2 Isolation level constants.
How to set isolation levels
You can set the isolation level using a connection
class:
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
Also, you can set it using the connectio.set_session
method.
connectio.set_session(isolation_level=None, readonly=None, deferrable=None, autocommit=None)
Here isolation_level can be READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, or the equivalent constant defined in the above list.
Next Steps:
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.
Hi
I am following the same but in my case while inserting data from table A to table B Redshift connection is not closing and it is not inserting the data also.
Will “RETURNING id;” work with the property auto-commit in False?
Hi Vishal,
Thanks for this really nice job.
I observed line 15 (
) returns a tuple, this causes lines 15 and 16 to generate type errors. I’ll suggest you parse only the first element of the tuple to the variable record, just like this:
Cheers Bro!
Thank you, Seyi Daniel, for your observation
You have an error here, default auto-commit property is actually False. It means all queries are run in a transaction by default and unless they are explicitly committed they will be rolled back.
proof: http://initd.org/psycopg/docs/connection.html#connection.autocommit