Python Database Programming

Almost all modern applications need to interact with databases to store, retrieve, and manage data. Python provides powerful libraries for connecting to various types of databases, from small databases commonly used in hobbyist projects to enterprise-level large databases.

Database Types

• Relational Databases (SQL): Data is stored in structured tables, and relationships can be established between tables. Common ones include SQLite, PostgreSQL, MySQL, SQL Server. They use Structured Query Language (SQL) for operations.
• NoSQL Databases: Data storage is more flexible and not limited to fixed table structures. Common ones include MongoDB (document-oriented), Redis (key-value), Cassandra (column-family).

DB-API 2.0 Specification

To standardize the way to interact with relational databases, the Python community developed PEP 249, the DB-API 2.0 specification. Most Python SQL database libraries follow this specification, meaning once you've learned how to use one library (such as sqlite3), you can easily switch to another library (such as psycopg2 for PostgreSQL) because their core APIs are similar.

The core objects of DB-API include:

• Connection Object: Represents a connection to the database. Responsible for transaction management (commit or rollback).
• Cursor Object: Used to execute SQL queries and fetch results. A connection can have multiple cursors.

Using sqlite3 (Built-in Library)

SQLite is a lightweight, serverless, self-contained SQL database engine. Because it doesn't require a separate server process and the database is a single file, it's perfect for small applications, prototype development, and learning. Python includes the sqlite3 module built-in, requiring no installation.

Basic Workflow

1. Connect to Database: Use sqlite3.connect(). If the file doesn't exist, it will be automatically created.
2. Create Cursor: Use connection.cursor().
3. Execute SQL: Use cursor.execute() or cursor.executemany().
4. Fetch Results: Use cursor.fetchone(), cursor.fetchall(), or cursor.fetchmany().
5. Commit Transaction: Use connection.commit() to save changes.
6. Close Connection: Use connection.close().

Example Code

import sqlite3

# 1. Connect to database (if 'example.db' doesn't exist, it will be created)
conn = sqlite3.connect('example.db')

# 2. Create a cursor object
cursor = conn.cursor()

# 3. Execute SQL to create a table
# Using '''...''' makes it convenient to write multi-line SQL
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)
''')

# 4. Insert data
# Using ? as placeholders helps prevent SQL injection attacks; this is best practice!
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))

# Insert multiple rows of data
data_to_insert = [
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com')
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", data_to_insert)

# 5. Commit changes to database
conn.commit()

# 6. Query data
print("All users:")
cursor.execute("SELECT * FROM users")

# Fetch all query results
all_users = cursor.fetchall()
for user in all_users:
    print(user)

# 7. Close connection
conn.close()

Other Databases

For other databases, you need to install the corresponding third-party libraries, but the basic pattern still follows DB-API.

• PostgreSQL: psycopg2

  import psycopg2
  conn = psycopg2.connect(database="mydb", user="myuser", password="mypass", host="127.0.0.1", port="5432")

• MySQL: mysql-connector-python

  import mysql.connector
  conn = mysql.connector.connect(user='myuser', password='mypass', host='127.0.0.1', database='mydb')

ORM (Object-Relational Mapping)

For complex applications, writing SQL statements directly can become tedious and error-prone. Object-Relational Mapping (ORM) libraries allow you to use Python objects to manipulate database records without writing SQL.

The most popular Python ORM is SQLAlchemy. It maps database tables to Python classes and table rows to class instances.

SQLAlchemy Example (Conceptual):

# Define a User class that maps to the 'users' table
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

# Create a new user (this automatically generates INSERT SQL)
new_user = User(name='David', email='david@example.com')
session.add(new_user)
session.commit()

# Query users (this automatically generates SELECT SQL)
all_users = session.query(User).all()