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.
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
connection object handles the PostgreSQL transactions. The connection object is responsible for making changes persistent in the database or reverting it in transaction failure.
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
- The connection object is responsible for terminating its transaction. There are two ways to do that calling either the
- Using the
commit()method changes are committed and immediately made persistent into the database.
- Using the
rollback()method we can revert the database changes.
close() method or any means of destroying the connection object will result in an implicit rollback() call, i.e., all changes get reverted.
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.
The syntax of
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.
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
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
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
Note: The transaction will remain open until an explicit
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() 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")
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
If you want to create a transaction within a specific block in code, use the
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.
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 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
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() 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 = 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
Also, you can set it using the
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.
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.