Transactions management in PostgreSQL Python

Psycopg is a PostgreSQL database adapter package for Python. It is a medium to communicate with PostgreSQL databases from Python applications. Transactions are a very essential feature of any database management system, including PostgreSQL. Psycopg helps with transactions, which allows to execution of a series of SQL commands as a single unit of work.

Modules Required:

psycopg==3.1.8
pip install --upgrade pip           # upgrade pip to at least 20.3
pip install "psycopg[binary]"

Necessary Concepts:

• Transactions: A transaction is a series of SQL statements that must be executed as a single unit of work. If any of the SQL statements fail, the entire transaction is rolled back, and the database is left in the state it was in before the transaction began.
• Commit: A commit is a SQL command that saves the current transaction to the database.
• Rollback: A rollback is a SQL command that undoes any changes made during the current transaction and returns the database to the state it was in before the transaction began.

Transactions Management

Working with databases requires proper transaction management, which enables the management of various database activities as a single logical unit of work. Transactions offer the following essential characteristics:

• Atomicity: Transactions guarantee that many database operations are handled as a single piece of work. The database is kept in a consistent state by rolling back the entire transaction if any of the operations fail, undoing all changes made during the transaction.
• Consistency: Transactions guarantee that the database is consistent both during and after a transaction. As a result, the database must begin in a consistent state and must return to that state after the transaction is committed.
• Isolation: Transactions ensure that operations of one transaction are not visible to those of other transactions until the transaction is committed. This provides isolation between concurrent transactions. Conflicts are avoided, and data integrity is preserved.
• Durability: Once a transaction is committed, the modifications made therein are irreversible and cannot be undone, even in the event of a system failure.

Let's Understand with examples to know how transaction management work in Postgres. At the first, the database table will be created.

PostgreSQL Create Database

First, we will create the PostgreSQL database. The Table we are using is named psy. The table has Three Columns ID, Num, and Data.

Autocommit Transactions

Database management systems (DBMS) provide the ability to automatically commit each transaction as it is carried out, negating the need for explicit commit statements from the user. In other words, any database changes performed during a transaction are saved to the database right away and cannot be undone. Many DBMSs normally have auto-commit transactions enabled by default, however, they can also be set up to be disabled. Each SQL query that an application executes is handled as a separate transaction when using auto-commit transactions. Any INSERT, UPDATE, or DELETE statements are immediately committed to the database as they are executed, as are any other changes made to the database. This indicates that the changes are irreversible after the statement is implemented. When dealing with tiny, straightforward transactions that do not have intricate error handling or rollback procedures, or in certain circumstances when transactional integrity is not crucial, auto-commit transactions might be helpful. They can improve database interactions by lowering the overhead of transaction management and simplify application code by obviating the requirement for explicit commit statements.

Example

In the code, a connection is established to a PostgreSQL database using psycopg.connect() function. The name, user, password, and host parameters are provided to specify the database connection details. The with statement is used to automatically manage the connection, ensuring that it is properly closed after the block is executed. Inside the outer block, the _autocommit property of the connection object is set to True, which enables auto-commit mode. This means that each SQL statement is executed using the conn.cursor() object will be automatically committed without the need for explicit commit statements. Within the inner with block, a for loop iterates from 0 to 9, and for each iteration, a new transaction is started using the conn.transaction() context manager. Inside the transaction, an INSERT statement is executed to insert data into the "psy" table, followed by a SELECT statement to fetch data from the same table. The fetched data is printed to the console. If the loop variable i exceeds 5, a psycopg. A rollback exception is raised, simulating an error condition. In the except block, conn. rollback() is called to roll back the transaction in case of an error. Finally, the cur.close() and conn.close() statements are executed to close the cursor and the connection, respectively.

import psycopg

with psycopg.connect("dbname=fla user=postgres password=root host=localhost") as conn:
    conn._autocommit=True
    with conn.cursor() as cur:
        try:
            for i in range(0,9,1):
                with conn.transaction() as tx:
                    print(i)
                    cur.execute("INSERT INTO psy (num, data) VALUES (%s, %s)",(i+200, "data"))
                    if i>5:
                        raise psycopg.Rollback
                    cur.execute("SELECT * from psy")
                    data=cur.fetchall()
                    print(data)
                # conn.commit()
            
            print("up is in the try block")
        except psycopg.Rollback:
            conn.rollback()
        finally:
            cur.close()
            conn.close()    
            

Output:

Transactional Context

The scope or boundary that a transaction operates within in a database management system (DBMS) is referred to as a transaction context. A transaction context specifies a transaction's lifecycle and behavior, as well as how it interacts with the database, modifies the data, and uses transactional features like isolation level and transaction characteristics. Transaction contexts are often managed using a statement when using a database library like psycopg2 to work with databases that support transactions (such as PostgreSQL). The statement offers a simple way to create and manage a transaction context. Depending on whether an exception happens or not, the transaction is automatically initiated at the beginning of the block and committed or rolled back at the end.

Example

The Code uses the psycopg library in Python to connect to a PostgreSQL database. It establishes a connection, creates a cursor object, and then enters a try block to handle exceptions. Inside the try block, a loop iterates from 0 to 8, executing SQL queries to insert data into a table, fetch data, and commit changes. If an exception occurs, such as when i is greater than 5, it raises a psycopg.Rollback exception, which triggers a rollback of the transaction. Finally, the cursor and connection are closed in a final block. Understanding how to connect to databases, use cursors, handle exceptions, and manage transactions is crucial when working with databases in Python.

import psycopg

with psycopg.connect("dbname=fla user=postgres password=root host=localhost") as conn:
    with conn.cursor() as cur:
        try:
            for i in range(0,9,1):
                with conn.transaction() as tx:
                    print(i)
                    cur.execute("INSERT INTO psy (num, data) VALUES (%s, %s)",(i, "data"))
                    if i>5:
                        raise psycopg.Rollback
                    cur.execute("SELECT * from psy")
                    data=cur.fetchall()
                    print(data)
                conn.commit()
            
            print("up is in the try block")
        except psycopg.Rollback:
            conn.rollback()
        finally:
            cur.close()
            conn.close()    
            

Output:

Changes in the Table

Transaction Characteristics

For the transactions that Psycopg manages, you can set transaction parameters. They have an impact on both the transactions begun explicitly by Transaction for both auto-commit and non-auto-commit transactions as well as the transactions initiated implicitly by non-auto-commit transactions. The server's default behavior, which is governed by server settings like Isolation, will be used if these parameters are left at None. In order to set these parameters you can use the connection attributes isolation_level, read_only, and deferrable. 

1. Isolation_Level: The isolation level controls how changes made by one transaction are visible to other transactions and how concurrent transactions interact with one another. When creating a database connection or beginning a transaction, the isolation level can be changed using the isolation_level argument.

Here's a brief explanation of the different isolation levels:

• READ COMMITTED: Multiple transactions are permitted to read and edit the same data concurrently at this isolation level, but each transaction only sees committed data from previous transactions. In PostgreSQL, this is the standard isolation level.
• SERIALIZABLE: This level of isolation guarantees that transactions are carried out sequentially, one after the other, without allowing for concurrent access. Although it offers the highest level of isolation, the lower concurrency can also affect performance.
• REPEATABLE READ: With this level of isolation, a transaction is guaranteed to see a consistent snapshot of the database at the outset and is shielded from any alterations made by subsequent transactions.
• READ UNCOMMITTED: This isolation level allows a transaction to see uncommitted changes made by other transactions, which can lead to dirty reads and inconsistent data.

2. Deferrable: A transaction's constraints should either be verified right away or postponed until the transaction is going to be committed, according to the deferrable transaction characteristic. The connection object's deferrable attribute can be used to set this.

3. Read_only: A read-only transaction must only read data, it must not modify it, according to the read-only transaction characteristic. The connection object's read_only attribute can be used to set this.

Example:

In the code, we will discuss read-only. A connection is established to a PostgreSQL database using psycopg2.connect() function. The name, user, password, and host parameters are provided to specify the database connection details. The with statement is used to automatically manage the connection, ensuring that it is properly closed after the block is executed. Inside the with block, a cursor is created using the conn.cursor() method, which represents a database cursor for executing SQL statements. The with statement is also used to automatically close the cursor after the block is executed. The _read_only property of the connection object is set to True, which sets the connection to read-only mode. This means that any attempts to execute statements that modify the data in the database, such as INSERT, UPDATE, or DELETE, will result in an error. The cur.execute() method is then called to execute an INSERT statement, inserting data with a value of 9999 for the "num" column and "data" for the "data" column into the "psy" table. After the statement is executed, the conn.commit() method is called to explicitly commit the transaction and save the changes to the database.

import psycopg
import psycopg2
##isolation_level = psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE

with psycopg.connect("dbname=fla user=postgres password=root host=localhost") as conn:
    with conn.cursor() as cur:
        conn._read_only=True
        cur.execute("INSERT INTO psy (num, data) VALUES (%s, %s)",(9999, "data"))
        conn.commit()
            
        print("up is in the try block")    
            

Output: