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.
Python3
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:
Terminal Output
Changes in the Table
Output
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.