61. SQLite 与 MySQL

数据不能只存在内存里，程序一关就全没了。数据库负责持久化存储，让你放心地增删改查。这节先讲零配置的 SQLite，再讲生产环境常用的 MySQL。

SQLite：开箱即用的本地数据库

SQLite 是世界上部署最广的数据库。它不需要独立的服务器进程，整个数据库就是一个 .db 文件，Python 内置支持。

连接与建表

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS user (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
conn.commit()

• connect('test.db') 打开数据库文件，不存在则自动创建。
• cursor() 获取游标对象，所有 SQL 都通过它执行。
• commit() 提交事务，真正把改动写进磁盘。

增删改查

插入数据：

cursor.execute("INSERT INTO user (name, age) VALUES (?, ?)", ('Alice', 30))
cursor.executemany("INSERT INTO user (name, age) VALUES (?, ?)", [
    ('Bob', 25),
    ('Charlie', 35)
])
conn.commit()
print(f"最后插入的行号: {cursor.lastrowid}")

查询数据：

cursor.execute("SELECT * FROM user WHERE age > ?", (25,))
rows = cursor.fetchall()
for row in rows:
    print(row)  # 返回的是元组

fetchall() 一次拿完，fetchone() 拿一条，fetchmany(size) 拿指定条数。

更新和删除：

cursor.execute("UPDATE user SET age = ? WHERE name = ?", (31, 'Alice'))
cursor.execute("DELETE FROM user WHERE id = ?", (2,))
conn.commit()
print(f"影响行数: {cursor.rowcount}")

把行变成字典

默认返回的每行是元组，按索引取值容易写错。可以改返回格式为字典：

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()
cursor.execute("SELECT * FROM user")
print(cursor.fetchone())  # {'id': 1, 'name': 'Alice', 'age': 31}

用完记得关闭连接：

cursor.close()
conn.close()

或者用上下文管理器更省心：

with sqlite3.connect('test.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM user")
    print(cursor.fetchall())

# 注意：with 语句只自动提交事务，不会自动关闭连接
conn.close()

MySQL：生产环境的主力

SQLite 适合单用户、小数据量场景。一旦需要多用户并发、远程访问、海量数据，就得请 MySQL（或 PostgreSQL）出场。

安装驱动

Python 连接 MySQL 需要第三方驱动，最常用的是 PyMySQL：

pip install pymysql

连接与操作

import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='your_password',
    database='testdb',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS book (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(100) NOT NULL,
                price DECIMAL(10, 2)
            )
        ''')

        cursor.execute("INSERT INTO book (title, price) VALUES (%s, %s)", ('Python 入门', 59.00))
        cursor.executemany("INSERT INTO book (title, price) VALUES (%s, %s)", [
            ('进阶指南', 89.00),
            ('实战案例', 79.00)
        ])
        conn.commit()

        cursor.execute("SELECT * FROM book WHERE price > %s", (60,))
        for row in cursor.fetchall():
            print(row)
finally:
    conn.close()

MySQL 的占位符是 %s，不是 ?。PyMySQL 会自动把参数转成合适的 SQL 类型。

用字典游标

PyMySQL 也支持字典形式的返回结果：

with conn.cursor(pymysql.cursors.DictCursor) as cursor:
    cursor.execute("SELECT * FROM book")
    for row in cursor.fetchall():
        print(f"书名: {row['title']}, 价格: {row['price']}")

数据库连接建议

• 小程序直接用 sqlite3，无需安装和维护。
• Web 应用、多人协作项目用 MySQL 或 PostgreSQL。
• 连接信息不要写死在代码里，放环境变量或配置文件。
• 操作数据库始终使用参数化查询，杜绝 SQL 注入。
• 多线程环境下，每个线程应该有自己的数据库连接，或者使用连接池。

小结

• sqlite3 是 Python 内置模块，零配置，单文件存储。
• PyMySQL 是连接 MySQL 的纯 Python 驱动，API 和 sqlite3 接近。
• 两者都支持参数化查询、事务提交、批量插入和字典游标。
• 记住口诀：改数据要 commit，查数据用 fetch，参数永远用占位符。

---