SoFunction
Updated on 2025-03-02

Python operation method for handling database transactions

How to handle database transactions in Python

A database transaction refers to a process in which a set of operations is either executed successfully or rolled back. Transactions are an important means to ensure database consistency, especially when dealing with scenarios that require multiple steps, it can avoid the situation where some data is successfully updated and some data fails. This article will explain in detail what a database transaction is and how to use it in Python in various ways (for example, usingsqlite3、MySQL、PostgreSQLetc.) handle transactions.

What is a database transaction?

1. The concept of transactions

A database transaction refers to a set of operations that are regarded as a single logical unit, either all executed successfully or all failed. Transactions have the following four characteristics, referred to asACIDcharacteristic:

  • Atomicity: All operations in the transaction either succeed or all fail and rollback. Transactions cannot be in a partially successful or partially failed state.
  • Consistency: The state of the database must be consistent before and after the transaction begins, and any operations that violate database rules cannot be submitted.
  • Isolation: Transactions execute independently of each other, and the intermediate state of transactions is invisible to other transactions.
  • Durability: Once a transaction is committed, the data will be saved for a long time and will not be lost even if a system failure occurs.

2. Why do transactions need?

Transactions are crucial to ensuring the reliability of database operations and data consistency. Here are several common usage scenarios:

  • Multi-step operation: For example, in an e-commerce platform, when a user purchases a product, it involves deducting inventory, creating orders, and deducting user account balances. These operations must be performed as a whole. If one of these steps fails, you need to roll back all completed steps to ensure that the system data does not be inconsistent.
  • Prevent partial updates: In the same operation, assuming there are multiple update operations. Once one of the operations fails, the impact of other operations should be cancelled to ensure that the database will not be in an incomplete update state.Concurrent control: When multiple transactions are carried out at the same time, the isolation of transactions can avoid problems such as dirty reading, non-repeatable reading and fantasy reading.

Handling transactions in Python

Handling database transactions in Python usually involves using database drivers (e.g.sqlite3、psycopg2、PyMySQL), these drivers usually provide built-in transaction management capabilities. Here are some examples of common database libraries and transaction management.

1. Use SQLite to handle transactions

SQLite is a lightweight database that is widely used in small applications and development environments. In Python,sqlite3The module provides transaction support.

Basic operations of transactions

sqlite3By default, it runs in "autocommit" mode, which means that each SQL statement is a transaction. However, you can manually control the start and commit of transactions.

import sqlite3
# Connect to the databaseconn = ('')
try:
    # Create a cursor    cursor = ()
    # Start a transaction (the default starts automatically, display calls () or () to end)    ("BEGIN")
    # Execute multiple SQL statements    ("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
    ("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")
    # Submit transaction    ()
except Exception as e:
    # Roll back the transaction when an error occurs    ()
    print(f"Transaction failed: {e}")
finally:
    # Close the connection    ()

In this example, we execute two SQL statements - insert user data and update account balance. These two statements are considered a transaction and are submitted to the database only when both succeed. Once any of these operations fails, the transaction will be rolled back, canceling the completed operation.

Automatic submission mode

existsqlite3, if the transaction is not manually controlled, it will be in automatic commit mode. This means that each SQL statement is submitted immediately after execution.

If you need to execute multiple statements in the same connection and make sure they execute as a transaction, you need to manually control the transaction using()and()。

2. Use MySQL to handle transactions

In MySQL, we can usePyMySQLorMySQL Connector/PythonWait for the library to handle transactions.

Use PyMySQL to handle transactions

import pymysql
# Connect to MySQL databaseconn = (
    host='localhost',
    user='root',
    password='password',
    db='test_db'
)
try:
    # Create a cursor    cursor = ()
    # Execute multiple SQL statements    ("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 25))
    ("UPDATE accounts SET balance = balance - 100 WHERE user_id = 2")
    # Submit transaction    ()
except Exception as e:
    # If there is any error, roll back the transaction    ()
    print(f"Transaction failed: {e}")
finally:
    # Close the connection    ()

In PyMySQL, transactions are manually controlled by default. After performing a set of operations, use()Submit transaction; if an error occurs, use()Roll back the transaction.

Use MySQL Connector/Python to handle transactions

MySQL Connector is a Python library provided by MySQL. Transaction operations andPyMySQLsimilar.

import 
# Connect to the databaseconn = (
    host='localhost',
    user='root',
    password='password',
    database='test_db'
)
try:
    cursor = ()
    # Execute multiple SQL statements    ("INSERT INTO orders (order_id, user_id) VALUES (%s, %s)", (101, 1))
    ("UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1")
    # Submit transaction    ()
except Exception as e:
    # Roll back the transaction    ()
    print(f"Transaction failed: {e}")
finally:
    ()

In MySQL, the basic steps of transaction control andPyMySQLSimilarly, SQL operations are performed first, and then transactions are manually committed or rolled back.

3. Use PostgreSQL to handle transactions

PostgreSQL is a powerful open source database that supports rich transactional capabilities. In Python,psycopg2It is the most commonly used library to interact with PostgreSQL.

usepsycopg2Handle transactions

import psycopg2
# Connect to PostgreSQL databaseconn = (
    dbname="test_db",
    user="user",
    password="password",
    host="localhost"
)
try:
    cursor = ()
    # Perform SQL operations    ("INSERT INTO employees (name, department) VALUES (%s, %s)", ("John", "HR"))
    ("UPDATE payroll SET salary = salary + 500 WHERE employee_id = 3")
    # Submit transaction    ()
except Exception as e:
    # Roll back when a transaction fails    ()
    print(f"Transaction failed: {e}")
finally:
    ()

psycopg2Transaction processing in it is also manually controlled, and transaction commit and rollback need to be called explicitly()or()。

4. Transaction processing in Django

If you are using a high-level web framework like Django, transaction management will be more concise. Django comes with transaction management functions and has two main transaction processing methods: automatic management and manual management.

Automatic transaction management

In Django, by default, all database operations run in auto-commit mode. Each database operation is submitted immediately.

Manual transaction management

For more complex transactions, Django provides()This context manager allows you to manually control transactions.

from  import transaction
try:
    with ():
        user = (username="alice")
        (user=user, balance=1000)
except Exception as e:
    print(f"Transaction failed: {e}")

In this example,()Will ensureUserandAccountThe created operation is an atomic operation. If any operation fails, the entire transaction will be rolled back.

5. Transaction processing in SQLAlchemy

If you use the ORM library,SQLAlchemy, transaction management is also very concise. SQLAlchemy allows you to manage transactions at the session level.

from sqlalchemy import create_engine
from  import sessionmaker
from models import User, Account  # Assume that the ORM model has been defined# Connect to the databaseengine = create_engine('sqlite:///')
Session = sessionmaker(bind=engine)
session = Session()
try:
    # Create users and accounts    new_user = User(name="Alice")
    (new_user)
    new
_account = Account(user=new_user, balance=1000)
    (new_account)
    # Submit transaction    ()
except Exception as e:
    # Transaction rollback    ()
    print(f"Transaction failed: {e}")
finally:
    ()

SQLAlchemyProvides transaction management bound to the database session to ensure that all operations are executed as one transaction.

Frequently Asked Questions in Transaction Processing

1. Concurrent transaction issues

When multiple users operate the database simultaneously, concurrent transactions may occur. Common concurrency problems are:

  • Dirty Read: One transaction reads data that is not committed by another transaction.
  • Non-repeatable Read: In a transaction, the same data is read twice, and the results are different.
  • Phantom Read: In a transaction, read data with the same condition twice, and the number of rows obtained is different.

To solve these problems, the database provides differentIsolation level,like:

  • Read Uncommitted: Allow dirty reading.
  • Read Committed: Only the committed transaction data is allowed to be read.
  • Repeatable Read: Prevent it from being read repeatedly.
  • Serializable: Prevent phantom reading and ensure transactions are executed serially.

Different databases have different implementations of isolation levels, and developers should choose the appropriate isolation level according to the specific application scenario.

2. Deadlock problem

A deadlock refers to two or more transactions waiting for resources held by each other, causing the system to be unable to continue execution. To solve the deadlock problem, the database usually has a mechanism to automatically detect and unlock deadlocks, and developers can also avoid deadlocks by reducing the lock holding time and access order.

Summarize

Transactions are the key mechanism to ensure database consistency and reliability. In Python, database transactions can be easily managed by using native database drivers or using ORM tools such as Django and SQLAlchemy. Developers need to choose appropriate transaction management methods based on actual needs to avoid common concurrency and deadlock problems.

This is the end of this article about how Python handles database transactions. For more related content of Python database transactions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!