Python数据库项目实战教程_SQLiteMySQLPostgreSQL整合

sqlite适合本地轻量开发，mysql适用于高并发Web应用，postgresql强于复杂查询与数据一致性，三者可通过SQLAlchemy统一抽象模型并适配差异。

sqlite：轻量级本地开发首选

SQLite 不需要单独安装服务，直接通过 python 内置的 sqlite3 模块就能操作，适合原型开发、小型工具或单机应用。它把整个数据库存为一个文件，开箱即用。

常见操作示例：

• 连接数据库：conn = sqlite3.connect("app.db")
• 启用外键约束（默认关闭）：conn.execute("PRAGMA foreign_keys = ON")
• 使用 with conn: 自动提交事务，避免手动 commit() 遗漏
• 参数化查询防止 SQL 注入：c.execute("SELECT * FROM users WHERE age > ?", (18,))

MySQL：Web 应用主流选择

MySQL 更适合多用户、高并发的 Web 项目，需搭配 pymysql 或 mysql-connector-python 使用。推荐用 SQLAlchemy 统一管理连接池和 ORM 映射，避免裸写连接逻辑。

关键配置注意点：

立即学习“Python免费学习笔记（深入）”；

• 连接字符串中指定 charset=utf8mb4，确保正确支持 emoji 和中文
• 设置 autocommit=False（默认），由 SQLAlchemy 或手动控制事务边界
• 在 flask/Django 中，用连接池（如 SQLAlchemy(pool_pre_ping=True)）自动检测失效连接
• 避免长连接堆积：设 pool_recycle=3600 定期重连

PostgreSQL：复杂业务与数据一致性保障

PostgreSQL 对 jsON、全文检索、地理空间、窗口函数等支持更完善，适合分析型或强一致性要求的系统。Python 推荐使用 psycopg2（性能好）或 asyncpg（异步场景）。

实用技巧：

• 用 psycopg2.extras.RealDictCursor 获取带字段名的字典结果，不用硬编码索引
• 批量插入用 execute_batch() 或 copy_from()，比循环 execute() 快 5–10 倍
• 利用 ON CONFLICT DO UPDATE 实现“存在则更新，否则插入”，替代先查后插逻辑
• 时间字段统一用 timestamptz 类型，配合 timezone='UTC' 连接参数，避免时区混乱

三库共用一套模型？用 SQLAlchemy 抽象层

不用为每个数据库重写 CRUD，SQLAlchemy 的 Core + ORM 可以屏蔽底层差异。定义一次模型，切换数据库只需改 URL：

• SQLite：sqlite:///app.db
• MySQL：mysql+pymysql://user:pass@localhost/dbname
• PostgreSQL：postgresql+psycopg2://user:pass@localhost/dbname

注意兼容性细节：

• 主键自增：SQLite 用 Integer(primary_key=True)，MySQL/PG 需显式加 autoincrement=True
• 字符串长度：SQLite 忽略 String(50) 限制，MySQL/PG 会校验，建议统一设 Text() 或按需约束
• 迁移脚本用 alembic 管理，不同库生成的 DDL 会自动适配