Python PostgreSQL Transaction management using Commit and Rollback

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.

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() 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.

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.

• Read More on Database isolation
• PostgreSQL transaction isolation

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.