4、数据库设计与SQLite实战：SQLite基础、表结构设计、Python操作SQLite

好，咱们进入第四章。这一章，说白了就是给咱们的项目管理系统搭个“骨架”——数据库。

你想想看，一个项目管理系统，每天要处理多少信息？项目信息、任务分配、版本迭代、Bug追踪、文档归档……如果没有一个靠谱的数据库，这些数据就是一盘散沙。我个人习惯，在项目一开始就把数据库结构想清楚，后面能省掉80%的返工时间。

4.1 SQLite基础：为什么选它？

嵌入式系统里选数据库，SQLite几乎是唯一的选择。为什么？

  • 零配置：不需要安装、不需要配置服务，一个文件就是一个数据库。
  • 轻量级：整个库只有几百KB，嵌入式设备上跑得飞起。
  • 可靠性高：支持事务、支持ACID，数据不会莫名其妙丢。

我记得有一次，一个客户要求把数据库部署在只有2MB Flash的MCU上。MySQL？想都别想。SQLite，完美搞定。

小提示：SQLite虽然轻，但并发写性能一般。咱们的项目管理系统，用户量不大，完全够用。

4.2 表结构设计：五张核心表

咱们的项目管理系统，核心就五张表。我直接给你看设计，然后逐一说。

表名 中文含义 核心字段
projects 项目表 id, name, description, status, created_at
tasks 任务表 id, project_id, title, assignee, priority, deadline, status
versions 版本表 id, project_id, version_name, release_date, changelog
bugs 缺陷表 id, task_id, severity, description, reporter, status
documents 文档表 id, project_id, title, file_path, upload_time

4.2.1 projects 表

这是最顶层。每个项目有一个唯一ID，名字、描述、状态（比如：进行中、已结项）。created_at 记录创建时间，方便排序。

4.2.2 tasks 表

任务表是核心中的核心。每个任务属于一个项目（通过 project_id 关联）。assignee 是负责人，priority 分高、中、低三级。我建议加一个 deadline 字段，不然任务容易拖。

注意：我曾经在一个项目里，忘了给 tasks 表加 deadline 字段。结果项目延期了两个月，复盘时才发现，任务根本没有截止时间概念。嗯，这个坑我踩过，你别踩。

4.2.3 versions 表

版本管理。每个项目可以有多个版本，比如 v1.0、v1.1。changelog 记录版本更新内容，方便追溯。

4.2.4 bugs 表

缺陷管理。每个Bug关联到一个任务（task_id）。severity 分致命、严重、一般、建议四级。reporter 是报告人，方便追责。

4.2.5 documents 表

文档归档。只存文件路径，不存文件本身。这样数据库不会太大，文件系统负责存储，各司其职。

4.3 Python操作SQLite：实战代码

好，理论说完了，咱们直接上代码。Python 操作 SQLite，用内置的 sqlite3 模块就行，不需要装任何第三方库。

4.3.1 创建数据库和表

import sqlite3

# 连接数据库（如果文件不存在，会自动创建）
conn = sqlite3.connect('project_manager.db')
cursor = conn.cursor()

# 创建 projects 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT '进行中',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')

# 创建 tasks 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    assignee TEXT,
    priority TEXT DEFAULT '中',
    deadline DATE,
    status TEXT DEFAULT '待办',
    FOREIGN KEY (project_id) REFERENCES projects(id)
)
''')

# 创建 versions 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    version_name TEXT NOT NULL,
    release_date DATE,
    changelog TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id)
)
''')

# 创建 bugs 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS bugs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id INTEGER NOT NULL,
    severity TEXT DEFAULT '一般',
    description TEXT,
    reporter TEXT,
    status TEXT DEFAULT '待修复',
    FOREIGN KEY (task_id) REFERENCES tasks(id)
)
''')

# 创建 documents 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    file_path TEXT,
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id)
)
''')

conn.commit()
conn.close()
print("数据库和表创建成功！")
关键点：外键约束（FOREIGN KEY）保证了数据完整性。比如，你不能给一个不存在的项目添加任务。这是数据库设计的底线。

4.3.2 插入数据

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

# 插入一个项目
cursor.execute("INSERT INTO projects (name, description) VALUES (?, ?)",
               ('智能家居控制系统', '基于ESP32的智能家居项目'))

# 插入一个任务
cursor.execute("INSERT INTO tasks (project_id, title, assignee, priority, deadline) VALUES (?, ?, ?, ?, ?)",
               (1, '完成WiFi模块驱动开发', '张三', '高', '2025-06-01'))

conn.commit()
conn.close()

注意，我用的是 ? 占位符，而不是直接拼接字符串。为什么？防止SQL注入。我见过有人直接写 f"INSERT INTO ... VALUES ('{name}')"，结果被黑客注入了恶意代码。嗯，这个习惯一定要养成。

4.3.3 查询数据

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

# 查询所有项目
cursor.execute("SELECT * FROM projects")
projects = cursor.fetchall()
for p in projects:
    print(f"项目ID: {p[0]}, 名称: {p[1]}, 状态: {p[3]}")

# 查询某个项目下的所有任务
cursor.execute("SELECT * FROM tasks WHERE project_id = ?", (1,))
tasks = cursor.fetchall()
for t in tasks:
    print(f"任务: {t[2]}, 负责人: {t[3]}, 优先级: {t[4]}")

conn.close()

4.3.4 更新和删除

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

# 更新任务状态
cursor.execute("UPDATE tasks SET status = ? WHERE id = ?", ('已完成', 1))

# 删除一个Bug
cursor.execute("DELETE FROM bugs WHERE id = ?", (5,))

conn.commit()
conn.close()
警告：删除操作一定要谨慎。我曾经手滑执行了 DELETE FROM tasks 忘了加 WHERE 条件，整个表的数据全没了。还好有备份，不然就凉了。建议：生产环境里，用“软删除”（加一个 is_deleted 字段）代替物理删除。

4.4 避坑指南：我踩过的三个坑

  • 坑一：忘记提交事务。执行了 INSERT 或 UPDATE 后，一定要 conn.commit()，不然数据不会真正写入。我刚开始学的时候，查了半天数据怎么不见了，结果就是没 commit。
  • 坑二：字段类型不匹配。SQLite 是弱类型，但 Python 这边是强类型。比如 deadline 字段，你传一个字符串 '2025-06-01' 没问题，但传一个整数 20250601，排序就会乱。建议统一用字符串格式的日期。
  • 坑三：并发写冲突。SQLite 不支持高并发写。如果你的系统有多个线程同时写数据库，可能会报 database is locked。解决方案：用连接池，或者加一个写锁队列。

4.5 本章小结

这一章，咱们把数据库的骨架搭好了。五张表，各自分工明确。Python 操作 SQLite 的增删改查，你也应该能上手了。

下一章，咱们会把这些表串起来，做一个完整的项目管理系统后端。到时候，你会看到这些表是怎么协同工作的。

嗯，今天就到这里。代码别光看，自己跑一遍，印象更深。

← 上一章 📖 返回目录 下一章 →