← Previous Next →

Databases with Python

Python's versatility makes it an excellent choice for interacting with databases, whether for small applications or large-scale data-driven systems. Databases are structured collections of data, and Python provides multiple libraries and frameworks to communicate, manage, and manipulate this data efficiently. In this lesson, we will explore advanced concepts in working with databases using Python, covering relational databases (like SQLite, PostgreSQL, MySQL), NoSQL databases (like MongoDB), Object-Relational Mapping (ORM) frameworks (like SQLAlchemy and Django ORM), connection management, transactions, query optimization, and best practices for production-ready applications.

💡 A Simple Analogy: Databases as Digital Filing Cabinets

Imagine a database as a massive, meticulously organized digital filing cabinet. Each drawer represents a table, each folder inside a drawer is a row (record), and the labels on folders are the columns (fields). Python acts as a skilled assistant who knows exactly which drawer and folder to open, how to add new files, or reorganize existing ones quickly and efficiently.

🎯 Real-World Use Case: Building a Scalable Web Application Backend

When developing a web application backend, you need to store user information, transactions, logs, and other data persistently. Python, combined with a relational database like PostgreSQL or a NoSQL solution like MongoDB, allows you to design scalable, reliable, and maintainable data layers. Using ORMs like SQLAlchemy or Django ORM helps you interact with databases using Python objects, making development faster and less error-prone.

1

Choosing the Right Database Type Understand the difference between relational and NoSQL databases. Relational databases store data in structured tables with relationships, while NoSQL databases offer flexible, schema-less storage for unstructured or hierarchical data.

2

Connecting to Databases from Python Learn how to establish connections using native libraries like sqlite3 for SQLite, psycopg2 for PostgreSQL, or pymongo for MongoDB. Proper connection management is critical for performance and reliability.

3

Executing Queries and Transactions Use SQL commands or NoSQL query syntax to insert, update, delete, and fetch data. Understand transactions to ensure data integrity, and learn how to commit or rollback operations safely.

4

Using Object-Relational Mappers (ORMs) Explore ORMs such as SQLAlchemy and Django ORM to interact with databases using Python classes and objects, abstracting away raw SQL and improving code maintainability.

5

Optimizing Database Interactions Learn query optimization techniques, indexing strategies, connection pooling, and caching to maximize application performance.

6

Handling Migrations and Schema Evolution Understand tools and best practices for evolving database schemas over time without data loss, using migration frameworks like Alembic or Django migrations.

Architecture of Databases with Python
Architecture of Databases with Python

📌 Deep Dive: Connecting and Querying SQLite with Python

PYTHON 

# Import the built-in sqlite3 library
import sqlite3

# Establish a connection to the database file (creates file if not exists)
conn = sqlite3.connect('example.db')

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# Create a new table named "users"
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT NOT NULL
    )
''')

# Insert sample data into the users table
users_to_insert = [
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com')
]

cursor.executemany('INSERT INTO users (username, email) VALUES (?, ?)', users_to_insert)

# Commit the transaction to save changes
conn.commit()

# Query the database to retrieve all users
cursor.execute('SELECT id, username, email FROM users')

# Fetch all results from the query
all_users = cursor.fetchall()

# Iterate and print each user
for user in all_users:
    print(f"User ID: {user[0]}, Username: {user[1]}, Email: {user[2]}")

# Close the cursor and connection
cursor.close()
conn.close()
Output
User ID: 1, Username: alice, Email: alice@example.com
User ID: 2, Username: bob, Email: bob@example.com
User ID: 3, Username: charlie, Email: charlie@example.com

📌 Deep Dive: Using SQLAlchemy ORM for PostgreSQL

PYTHON 

# Install SQLAlchemy and psycopg2-binary before running this example:
# pip install sqlalchemy psycopg2-binary

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Define the database URL for PostgreSQL
DATABASE_URL = "postgresql+psycopg2://username:password@localhost:5432/mydatabase"

# Create the SQLAlchemy engine
engine = create_engine(DATABASE_URL, echo=True)

# Base class for declarative class definitions
Base = declarative_base()

# Define a User model mapped to the 'users' table
class User(Base):
    __tablename__ = 'users'

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

    def __repr__(self):
        return f""

# Create all tables in the database (if not already created)
Base.metadata.create_all(engine)

# Create a configured "Session" class and a session instance
Session = sessionmaker(bind=engine)
session = Session()

# Create new User objects
new_users = [
    User(username='dave', email='dave@example.com'),
    User(username='emma', email='emma@example.com')
]

# Add new users to the session
session.add_all(new_users)

# Commit the transaction to persist changes
session.commit()

# Query the database to retrieve all users
all_users = session.query(User).all()

for user in all_users:
    print(user)

# Close the session
session.close()
Output
<User(id=1, username='dave', email='dave@example.com')>
<User(id=2, username='emma', email='emma@example.com')>

📌 Deep Dive: Interacting with MongoDB using PyMongo

PYTHON 

# Install PyMongo before running:
# pip install pymongo

from pymongo import MongoClient

# Connect to the MongoDB server (default localhost:27017)
client = MongoClient('mongodb://localhost:27017/')

# Select the database
db = client['mydatabase']

# Select the collection (like a table)
users_collection = db['users']

# Insert multiple documents (records) into the collection
users = [
    {"username": "frank", "email": "frank@example.com", "roles": ["admin", "user"]},
    {"username": "grace", "email": "grace@example.com", "roles": ["user"]},
]

result = users_collection.insert_many(users)
print(f"Inserted document IDs: {result.inserted_ids}")

# Query documents where 'roles' contains 'user'
query = {"roles": "user"}
cursor = users_collection.find(query)

for document in cursor:
    print(document)

# Close the connection
client.close()
Output
Inserted document IDs: [ObjectId('...'), ObjectId('...')]
{'_id': ObjectId('...'), 'username': 'frank', 'email': 'frank@example.com', 'roles': ['admin', 'user']}
{'_id': ObjectId('...'), 'username': 'grace', 'email': 'grace@example.com', 'roles': ['user']}

⚠️ Common Pitfall: Forgetting to Close Database Connections

Failing to close connections or sessions properly can lead to resource leaks, exhausted connection pools, and degraded application performance. Always use context managers (like Python’s with statement) or ensure explicit connection closing in finally blocks to maintain healthy database communication.

⚠️ Common Pitfall: SQL Injection Vulnerabilities

Avoid building SQL queries by concatenating user input strings directly. Always use parameterized queries or ORM methods that safely handle escaping. This prevents attackers from injecting malicious SQL code that can compromise your database.

⚠️ Common Pitfall: Ignoring Transactions

When multiple related changes must be atomic, use transactions to ensure either all succeed or none do. Ignoring transaction management can lead to inconsistent or partial data states, especially in concurrent environments.

← Previous Next →