In this article, we will see how to manage PostgreSQL transaction from Python using psycopg2. A transaction is a unit of work which involves a set of database operations.
Prerequisites Before we start
In most of the cases, we want to execute a PostgreSQL database operation only when another operation 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 from account A.
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.
Psycopg2 Transactions control
The PostgreSQL transactions handled by the connection object. The connection object is responsible for making changes persistent in the database or reverting it in case of transaction failure.
Using the cursor object, we execute database operations. We can create any number of cursor object from the connection object. If any of the cursor’s command fails or the transaction gets aborted, then 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() or rollback() 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.
By default, the connection is in auto-commit mode. i.e., default auto-commit property is True. That means if any query executed 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 we can revert the executed queries result back to the original state in case of failure.
The Syntax of AutoCommit
connection.autocommit=True or False
If all operations under a transaction completed, then use
connection.commit() to persist the changes in the database to maintain the data integrity and consistency. If commit() is not called, the effect of any data manipulation will get lost.
The syntax of Commit
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
Steps to manage PostgreSQL transactions from Python
- Disable Auto-commit mode.
- If all operations under a transaction completed successfully then use connection.commit() to persist the changes in the database.
- If any of the operations fails then revert the changes made by all of the operations using a connection.rollback()
Python Example to manage PostgreSQL transactions
import psycopg2 from psycopg2 import Error 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()  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()  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")
You should get the following output after all operations within a transaction completed successfully.
Transaction completed successfully PostgreSQL connection is closed
You should 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
with statement does? Psycopg2’s connections and cursors are nothing but context managers and can be used with the
with statement. So the main advantage of using
withblock is you don’t need to do any explicit commit and rollback.
with psycopg2.connect(connection_arguments) as conn: with conn.cursor() as cursor: cursor.execute(Query)
When a connection exit the
with block, and query executed without any error and exception, the transaction is committed. If the query raises an error or exception, the transaction is rolled back.
When you get out of the scope of with block, it doesn’t close the connection, But cursor and any resource eventually associated with it get closed. A connection is available to use in more than a
with statement, and each
with block is nothing but a different transaction.
Let see the demo. In this example, we will perform an online shopping transaction. we purchase one item and deduct the balance from customer’s wallet and add it to the company’s account
we are placing all this code inside
import psycopg2 from psycopg2 import Error 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: # Find item price query = """select price from itemstable where itemid = 876""" cursor.execute(query) record = cursor.fetchone()  Itemprice = int(record) # find customer's ewallet balance query = """select balance from ewallet where userId = 23""" cursor.execute(query) record = cursor.fetchone()  ewalletBalance = int(record) 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 += 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 the 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 you should use the isolation level considering all these points. A higher isolation level may block other users or transactions to complete itself first.
psycopg2.extensions provide us the following isolation levels:
You can read around them in details here Psycopg2 Isolation level constants.
How to set isolation levels
You can set the isolation level using connection class:
Also, you can set using
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 i.e. psycopg2.extensions isolation constants.
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.