In this tutorial we will see how to manage PostgreSQL transactions from Python using psycopg2. A transaction is a unit of work that affects a set of database operations.
In most cases, you need to perform a database operation only after some other operation is complete. For example, in a banking system, you can only transfer an amount from account A to account B after withdrawing funds from account A. In a transaction, either all transactions are executed or none of them are executed. Thus, it is necessary to perform all transactions in one transaction for it to be successful. This is what we are going to talk about next:
- Enabling and disabling the auto-commit (auto-save) mode.
- Transactions for saving changes in the database.
- Support for ACID property of a transaction.
- Rollback of all transactions in case of failure.
- Changing the isolation level of a PostgreSQL transaction from Python.
Transaction management psycopg2
PostgreSQL transactions are handled by the connection object. It is responsible for saving changes or undoing them if they fail. The cursor object is used to perform transactions in the database. You can create an unlimited number of
cursor objects from the
connection object. If any of the cursor object commands fails or the transaction is undone, the following commands are not executed until the
connection.rollback() method is called.
- The connection object is responsible for stopping the transaction. This can be done using the
- After using the
commit() method the changes are saved in the database.
- With the
rollback()method you can roll back the changes.
Note: A call to the
callose() method or any other method responsible for destroying the connection object results in an implicit call to
rollback(), causing all changes to be rolled back.
By default, the connection works in autocommit mode, that is, the auto-commit property is
True. This means that if the query succeeds, the changes are immediately saved in the database, and rollback becomes impossible. To execute queries in a transaction, this property must be disabled. To do that, make
connection.autocommit=False. In this case it will be possible to rollback the executed query to the original state in case of failure. Syntax
connection.autocommit=True or Falsec
If all transactions in a transaction are complete, use
connection.commit() to save changes to the database. If you don’t use the method, all data interaction effects will not be applied. The syntax for
If at least one transaction fails,
connection.rollback() is used to undo the changes. The syntax of
PostgreSQL transaction management example from Python
- Disable auto-commit mode.
- If all operations were completed successfully, use
connection.commit()to save them to the database.
- If any of the operations failed, use
connection.rollback()to roll back to the last state.
Note: a transaction remains open until
rollback() is called explicitly. Let’s downgrade one phone and upgrade the other.
import psycopg2 from psycopg2 import Error try: # Connect to an existing database connection = psycopg2.connect(user="postgres", # password that you used when you installed PostgreSQL password="1111", host="127.0.0.1", port="5432", database="postgres_db") connection.autocommit=False cursor = connection.cursor() amount = 200 query = """select price from mobile where id = 1""" cursor.execute(query) record = cursor.fetchone()  price_a = int(record) price_a -= amount # Decrease price of the first sql_update_query = """update mobile set price = %s where id = 1"" cursor.execute(sql_update_query,(price_a,)) query = """select price from mobile where id = 2"" cursor.execute(query) record = cursor.fetchone()  price_b = int(record) price_b += amount # Increase price of the second sql_update_query = """Update mobile set price = %s where id = 2"" cursor.execute(sql_update_query, (price_b,)) # transaction execution connection.commit() print("The transaction was successfully completed") except (Exception, psycopg2.DatabaseError) as error : print ("Transaction error. Cancel all other transactions", error) connection.rollback() finally: if connection: cursor.close() connection.close() print("Connection to PostgreSQL closed")
You will get the following output after all transactions in the transaction are successfully completed.
The transaction has been successfully closed Connection with PostgreSQL closed If at least one of the operations completes with an error, the output will be as follows.
Error in transaction. Cancel all other transactions Connection with PostgreSQL closed
With instruction to control transactionsUse the
withinstruction to create a transaction within a certain block in your code. What does the
withinstruction do? Connections and cursor objects in psycopg2 are just context managers that can be used with
with. The main advantage is that it allows you not to explicitly write
with psycopg2.connect(connection_arguments) as conn: with conn.cursor() as cursor: cursor.execute(Query)
When the connection exits the
withblock and the request is executed without errors or exceptions, the transaction is saved. If there is a problem in the process, the transaction is rolled back. A connection is not closed when it leaves the block, but cursor objects and any other related objects are. A connection can be used in more than one
with, and each
withblock is just a transaction. In this example, let's execute a transaction in an online trading platform. Let's buy one item, reduce the buyer's balance, and transfer the appropriate amount to the company's account.
import psycopg2 from psycopg2 import Error connection = psycopg2.connect(**params) with connection: with connection.cursor() as cursor: # Search for the price of the item query = """select price from itemstable where itemid = 876"" cursor.execute(query) record = cursor.fetchone()  item_price = int(record) # Getting the balance of the account query = """select balance from ewallet where userId = 23"" cursor.execute(query) record = cursor.fetchone()  ewallet_balance = int(record) new_ewallet_balance -= item_price # Balance update with item_price sql_update_query = """Update ewallet set balance = %s where id = 23""" cursor.execute(sql_update_query,(new_ewallet_balance,)) # company balance entry query = """select balance from account where accountId = 2236781258763""" cursor.execute(query) record = cursor.fetchone() account_balance = int(record) new_account_balance += item_price # Company account update sql_update_query = """Update account set balance = %s where id = 132456"" cursor.execute(sql_update_query, (new_account_balance,)) print("Transaction completed successfully")
Isolation levels psycopg2
In database systems, isolation levels can be used to determine what level of transaction integrity will be visible to other users and systems. For example, when a user performs an action or transaction that has not yet been completed, details about it are available to other users who can perform sequential actions. For example, a consumer purchases an item. The information about this becomes known to other systems, which is necessary to prepare the invoice, receipt and discount calculations, which speeds up the entire process. If the level of isolation is low, multiple users can access the same data at the same time. However, this can also lead to some problems, up to and including lost updates. So you have to consider everything. A higher level of isolation can block other users or transactions before completion.
psycopg2.extensionsprovides the following levels of isolation:
How to set isolation levels
This can be done with the
You can also use the
connectio.set_session(isolation_level=None, readonly=None, deferrable=None, autocommit=None)
In this case
the isolation_levelcan be
SERIALIZEor others from the list of constants.