Data Integrity, Transactions, and Connection Pooling in Python

Table of Contents

• Introduction
• What is Data Integrity?
• Ensuring Data Integrity in Python Applications
• What are Database Transactions?
  • ACID Properties
  • Using Transactions in Python (SQLite, PostgreSQL)
• Connection Pooling
  • Why is Connection Pooling Important?
  • Implementing Connection Pooling in Python
• Best Practices
• Conclusion

Introduction

When building database-driven applications, maintaining data integrity, handling transactions, and managing connections efficiently are crucial for stability and performance.
Ignoring these aspects often leads to issues like data corruption, race conditions, performance bottlenecks, and even application crashes.

In this module, we will dive deep into how to ensure data integrity, properly use transactions, and efficiently manage connections through pooling — all from a Python developer’s perspective.

What is Data Integrity?

Data Integrity refers to the accuracy, consistency, and reliability of data over its lifecycle.
It ensures that data remains correct and unaltered during operations like creation, storage, retrieval, update, and deletion.

Types of Data Integrity:

• Entity Integrity: Ensuring unique and non-null primary keys.
• Referential Integrity: Maintaining correct foreign key relationships.
• Domain Integrity: Validating that data entered falls within a predefined domain (like date ranges, enum types).
• User-Defined Integrity: Enforcing business rules, like “an order cannot exist without a customer.”

Ensuring Data Integrity in Python Applications

1. Proper Database Schema Design:
   Define proper constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK in your tables.
2. Input Validation:
   Validate user inputs at both the client and server side.
3. Use ORM Tools:
   Tools like SQLAlchemy automatically enforce many integrity rules through models.
4. Error Handling:
   Gracefully handle errors like IntegrityError, ValidationError, etc.

Example with SQLAlchemy:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, nullable=False)

This schema ensures that:

• Each user has a unique email.
• Email cannot be NULL.

What are Database Transactions?

A transaction is a sequence of database operations executed as a single logical unit of work.
Transactions ensure that either all operations succeed, or none do, maintaining the database’s consistent state.

ACID Properties

• Atomicity: All operations succeed or none.
• Consistency: Transitions from one valid state to another.
• Isolation: Simultaneous transactions do not interfere.
• Durability: Once committed, changes are permanent.

Using Transactions in Python

Most database libraries (like psycopg2, SQLAlchemy) manage transactions automatically, but manual control is often needed.

SQLite Example (manual transaction):

import sqlite3

conn = sqlite3.connect('example.db')
try:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name) VALUES (?)", ('Alice',))
    cursor.execute("INSERT INTO users (name) VALUES (?)", ('Bob',))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Transaction failed: {e}")
finally:
    conn.close()

PostgreSQL Example with psycopg2:

import psycopg2

conn = psycopg2.connect(database="testdb", user="user", password="password", host="localhost")
try:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO employees (name) VALUES (%s)", ("John",))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Error: {e}")
finally:
    conn.close()

Connection Pooling

Why is Connection Pooling Important?

• Database Connections are Expensive:
  Opening and closing connections frequently is costly in terms of time and resources.
• Connection Limits:
  Databases often have maximum connection limits. Exhausting them can crash the system.
• Performance Boost:
  Reusing existing connections saves latency and improves scalability.

In short, connection pooling maintains a cache of database connections that your application can reuse.

Implementing Connection Pooling in Python

Using SQLAlchemy Connection Pool:

SQLAlchemy uses connection pools by default, but you can configure it manually:

from sqlalchemy import create_engine

engine = create_engine(
    'postgresql+psycopg2://user:password@localhost/mydb',
    pool_size=5,       # Maximum number of connections
    max_overflow=10,   # Additional connections beyond pool_size
    pool_timeout=30,   # Wait time before throwing an error
    pool_recycle=1800  # Recycle connections after 30 minutes
)

connection = engine.connect()
# Use the connection
connection.close()

Using psycopg2 with a Pooling Library:

from psycopg2 import pool

db_pool = pool.SimpleConnectionPool(1, 10,
    user="user",
    password="password",
    host="localhost",
    database="testdb"
)

conn = db_pool.getconn()

try:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM employees")
finally:
    db_pool.putconn(conn)

Popular Pooling Libraries:

• SQLAlchemy’s built-in pools
• psycopg2.pool
• pgbouncer (external tool for PostgreSQL)

Best Practices

• Always Use Transactions: Especially when modifying data.
• Catch Exceptions and Rollback: Prevent partial writes.
• Use Connection Pooling in Production: Especially in high-traffic applications.
• Validate at Multiple Layers: Both at application and database level.
• Monitor Connection Usage: Use monitoring tools to observe connection pool health.

Conclusion

Mastering data integrity, transactions, and connection pooling is essential for building production-grade, database-driven Python applications.
These concepts not only prevent catastrophic data loss and corruption but also significantly boost your application’s performance and scalability.