Python 数据库编程

几乎所有的现代应用程序都需要与数据库进行交互以存储、检索和管理数据。Python 提供了强大的库来连接各种类型的数据库，从业余项目常用的小型数据库到企业级的大型数据库。

数据库类型

• 关系型数据库 (SQL): 数据存储在结构化的表中，表与表之间可以建立关系。常见的有 SQLite, PostgreSQL, MySQL, SQL Server。它们使用结构化查询语言 (SQL) 进行操作。
• NoSQL 数据库: 数据存储方式更加灵活，不限于固定的表结构。常见的有 MongoDB (文档型), Redis (键值型), Cassandra (列族型)。

DB-API 2.0 规范

为了统一与关系型数据库的交互方式，Python 社区制定了 PEP 249，即 DB-API 2.0 规范。大多数 Python 的 SQL 数据库库都遵循这个规范，这意味着一旦你学会了如何使用一个库（如 sqlite3），你就能很容易地切换到另一个库（如 psycopg2 for PostgreSQL），因为它们的核心 API 是相似的。

DB-API 的核心对象包括：

• 连接对象 (Connection Object): 代表与数据库的连接。负责事务管理（提交 commit 或回滚 rollback）。
• 游标对象 (Cursor Object): 用于执行 SQL 查询并获取结果。一个连接可以有多个游标。

使用 sqlite3 (内置库)

SQLite 是一个轻量级的、无服务器的、自包含的 SQL 数据库引擎。因为它不需要单独的服务器进程，并且数据库就是一个单一的文件，所以非常适合小型应用、原型开发和学习。Python 内置了 sqlite3 模块，无需安装。

基本工作流程

1. 连接到数据库: 使用 sqlite3.connect()。如果文件不存在，它会自动创建。
2. 创建游标: 使用 connection.cursor()。
3. 执行 SQL: 使用 cursor.execute() 或 cursor.executemany()。
4. 获取结果: 使用 cursor.fetchone(), cursor.fetchall() 或 cursor.fetchmany()。
5. 提交事务: 使用 connection.commit() 来保存更改。
6. 关闭连接: 使用 connection.close()。

示例代码

import sqlite3

# 1. 连接到数据库 (如果 'example.db' 不存在，则会创建它)
conn = sqlite3.connect('example.db')

# 2. 创建一个游标对象
cursor = conn.cursor()

# 3. 执行 SQL 创建一个表
# 使用 '''...''' 可以方便地写多行 SQL
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)
''')

# 4. 插入数据
# 使用 ? 作为占位符可以防止 SQL 注入攻击，这是最佳实践！
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))

# 插入多条数据
data_to_insert = [
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com')
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", data_to_insert)

# 5. 提交更改到数据库
conn.commit()

# 6. 查询数据
print("All users:")
cursor.execute("SELECT * FROM users")

# 获取所有查询结果
all_users = cursor.fetchall()
for user in all_users:
    print(user)

# 7. 关闭连接
conn.close()

其他数据库

对于其他数据库，你需要安装相应的第三方库，但基本模式仍然遵循 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 (对象关系映射)

对于复杂的应用程序，直接编写 SQL 语句可能会变得繁琐和容易出错。对象关系映射（Object-Relational Mapping, ORM）库允许你使用 Python 对象来操作数据库记录，而无需编写 SQL。

最流行的 Python ORM 是 SQLAlchemy。它将数据库表映射到 Python 类，将表中的行映射到类的实例。

SQLAlchemy 示例 (概念性):

# 定义一个映射到 'users' 表的 User 类
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

# 创建一个新用户 (这会自动生成 INSERT SQL)
new_user = User(name='David', email='david@example.com')
session.add(new_user)
session.commit()

# 查询用户 (这会自动生成 SELECT SQL)
all_users = session.query(User).all()