从标题看第三次作业这四个字确实平平无奇但如果你经历过那种前两次作业都是小打小闹第三次直接上完整应用的课程设计就会明白这个标题背后藏着多少东西。我这次要拆解的是某高校数据库课程设计的第三次作业——用Python连接数据库独立完成一个带前端界面、能跑通增删改查的图书租借管理系统。作业本身不难但它是从会写SQL到会做系统的分水岭很多同学恰恰栽在这一步。这篇文把整个设计过程、踩坑记录和避坑清单全整理出来给正在跟同类作业较劲的人做一个参考。1. 作业背景与整体设计思路1.1 三次作业的递进关系与这次的目标先说背景。这门课的第一次作业是拿一张销售订单表做SQL练习核心考点是 SELECT、JOIN、GROUP BY 这些基础查询第二次作业是在订单库上写存储过程、触发器和视图开始接触数据库编程。到了第三次作业老师只给了一句话基于数据库做一个完整的应用系统表结构自己设计后端至少三个接口前端至少两个页面数据量不少于20条功能自选。这个命题方式其实是有设计逻辑的——前两次作业把单点技能都考完了第三次就是综合应用题。它的真实考察点有三个第一你会不会根据业务需求设计合理的表结构第二你会不会在应用层正确处理数据的完整性和一致性第三你会不会把SQL、后端逻辑和页面串成一个完整闭环。我选的是图书租借管理系统。理由很朴素图书租借的业务场景非常清晰核心实体就三个——用户、图书、借阅记录天然覆盖多表关联、库存校验、事务提交这些关键知识点又不会像电商系统那样复杂到超出作业范围。选项目的时候有个判断标准实体关系清楚的项目才容易把分数拿到手实体关系模糊的项目光是理清逻辑就能耗掉一半时间。1.2 技术方案选型与我的取舍逻辑当时考虑过三套方案分别做了对比方案优点缺点我的结论Python Flask SQLite环境轻量无需安装数据库服务SQL语句完全可见不适合高并发场景作业规模下最稳Java Spring Boot MySQL工程规范MySQL通用性强环境配置重启动慢容易被环境问题拖死过度设计Node.js Express MongoDB前后端同语言数据用JSON存评分老师不熟悉偏离作业方向最后选了 Python Flask SQLite核心原因就一条课程机房的机器配置有限Java那套光启动就要吃几百兆内存而且MySQL服务一旦没配好作业演示就成了修环境演示。SQLite把数据库直接放在项目目录里拷贝整个文件夹就能跑这对评分和演示都是最友好的。关于数据库访问层我没有用 SQLAlchemy ORM而是直接写原生 sqlite3。一来是作业要求里明确写了不能隐藏SQL语句二来是我觉得这个阶段应该把SQL基本功展现在评分老师面前。用ORM虽然代码更少但和这门课的核心考点是背道而驰的。2. 数据库设计三张表、约束与索引的取舍2.1 用户表与图书表的结构数据库设计是整个作业的地基地基不稳后面接口写得再漂亮也可能出逻辑漏洞。我用的是最经典的三张表设计用户表 user、图书表 book、借阅流水表 borrow_record。用户表结构如下CREATE TABLE user ( user_id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, password TEXT NOT NULL, real_name TEXT NOT NULL, role TEXT NOT NULL DEFAULT student CHECK (role IN (student, admin)), created_at TEXT NOT NULL DEFAULT (datetime(now, localtime)) );username 加 UNIQUE 约束是给登录功能和注册功能兜底避免出现两个同名账号。role 字段用 CHECK 约束限定只能是 student 或 admin这是给后续扩展管理员可以新增图书这类权限功能留的口子。created_at 直接取数据库本地时间比在应用层写时间更可靠这是第一个小技巧。图书表结构如下CREATE TABLE book ( book_id INTEGER PRIMARY KEY AUTOINCREMENT, isbn TEXT NOT NULL UNIQUE, title TEXT NOT NULL, author TEXT NOT NULL, category TEXT, total_copies INTEGER NOT NULL DEFAULT 1 CHECK (total_copies 0), available_copies INTEGER NOT NULL DEFAULT 1 CHECK (available_copies 0 AND available_copies total_copies) );这里有一个很关键的取舍我故意在 book 表里冗余了一个 available_copies 字段用来记录当前可借数量而不是每次借书时用total_copies 减去已借数量的聚合查询去算。这个设计的代价是业务层每次借还书都要多更新一行数据但换来的是查询极快、逻辑直观——首页显示图书列表时直接读 available_copies 就能判断可借/已借完不用写子查询。这是典型的读多写少场景用冗余换性能在低并发的作业系统里非常合理。有一个细节必须说明数据库的 CHECK 约束只能保证 available_copies 不小于0且不超过 total_copies但无法保证两个字段之和是否符合真实业务。比如我借出一本书结果忘了更新 available_copies两个字段在数值上依然满足约束但业务上已经错了。所以应用层的事务处理才是保障一致性的关键后面接口部分会细讲。2.2 借阅流水表的结构与主键设计借阅流水表是这题的得分关键也是最容易设计出错的地方CREATE TABLE borrow_record ( record_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, book_id INTEGER NOT NULL, borrow_date TEXT NOT NULL, due_date TEXT NOT NULL, return_date TEXT, status TEXT NOT NULL DEFAULT borrowed CHECK (status IN (borrowed, returned, overdue)), FOREIGN KEY (user_id) REFERENCES user (user_id), FOREIGN KEY (book_id) REFERENCES book (book_id) );很多人会纠结既然一个用户一次只能借一本为什么不直接拿 (user_id, book_id, borrow_date) 做联合主键还要单独加一个 record_id答案是联合主键在还书后再借同一本的场景下会撞车。假设 A 同学今天借了《数据库原理》还掉之后明天又想借这时候如果主键是 (user_id, book_id, borrow_date)两天日期不同倒是不冲突但如果同一天内借了再还、再借borrow_date 精确到天就会把两条记录当成同一条数据。借阅流水是典型的流水型数据要求每条记录都能被唯一标记单独设自增 record_id 是最稳妥的方案。另外我还加了一个部分唯一索引防止同一个人同时对同一本书处于借出状态CREATE UNIQUE INDEX idx_active_borrow ON borrow_record (book_id, user_id) WHERE status borrowed;这是 SQLite 支持的部分索引写法。它的意思是只在 status 为 borrowed 的记录上保证 (book_id, user_id) 不重复。这个索引一加就算后端接口漏写了库存判断数据库层也能挡住一个人借同一本书两次的错误属于双保险。2.3 索引、约束与初始数据外键字段虽然写在表定义里但 SQLite 默认不会自动为它们创建索引。如果后续要按 user_id 查某人的借阅记录不加索引就是全表扫描。所以我额外加了两个普通索引CREATE INDEX idx_record_user ON borrow_record (user_id); CREATE INDEX idx_record_book ON borrow_record (book_id);索引不是越多越好作业规模下这两个就够了。status 字段我其实也可以加索引但考虑到数据量小且不是主要查询条件就省了。初始数据方面我插入了3个用户和20本书。有个实操小技巧插入书本数据时故意把两本书设置成 total_copies 为 2、available_copies 为 2这样演示时能展示同一本书有两个副本的库存扣减逻辑再留一两本 available_copies 为 0 的书用来展示已借完不可借的提示分支。这些边界数据不是凑数的是专门为作业验收时的各种提问准备的。3. 后端接口实现从登录到借还书3.1 项目结构与数据库连接后端我用 Flask项目结构很干净library_app/ ├── app.py # 主应用所有路由都在这 ├── db.py # 数据库连接与初始化 ├── init_data.py # 建表 插入初始数据 ├── templates/ │ ├── login.html # 登录页 │ ├── index.html # 图书列表首页 │ └── add_book.html # 新增图书页 ├── static/ │ └── style.css # 页面样式 ├── library.db # SQLite 数据库文件 └── requirements.txt # 依赖清单db.py 里的连接函数是全局复用的关键import sqlite3 from flask import g DATABASE library.db def get_db(): if db not in g: g.db sqlite3.connect(DATABASE) g.db.row_factory sqlite3.Row g.db.execute(PRAGMA foreign_keys ON) return g.db有三处细节值得展开。第一每次请求都用 Flask 的 g 对象缓存连接请求结束再关闭避免反复创建连接。第二row_factory 必须设为 sqlite3.Row否则取出来的数据只能按数字下标访问写代码极其痛苦且容易出错。第三PRAGMA foreign_keys ON 这行一定要加——SQLite 默认不开启外键约束不加这行前面设计的外键和级联逻辑全部形同虚设。这个坑我见过太多人踩了。init_data.py 负责建表和插入数据核心逻辑是先 DROP 再 CREATE保证重复执行不会报错from db import DATABASE import sqlite3 def init_db(): conn sqlite3.connect(DATABASE) conn.executescript( DROP TABLE IF EXISTS borrow_record; DROP TABLE IF EXISTS book; DROP TABLE IF EXISTS user; -- 建表语句省略见第二章 ) # 插入初始用户、图书 conn.commit() conn.close()这里我故意把 DROP 顺序写成了先删子表borrow_record、再删父表book、user因为外键约束下删除顺序反了会报错。这个细节在项目演示前检查一遍很重要。3.2 登录校验与访问控制登录接口最大的坑在于SQL拼接。我刚写第一版时直接用了 f-string 拼接用户名和密码虽然简单但这是明显的注入漏洞入口。后来改成了参数化查询app.route(/login, methods[POST]) def login(): username request.form.get(username, ).strip() password request.form.get(password, ).strip() if not username or not password: return 用户名和密码不能为空, 400 db get_db() user db.execute( SELECT user_id, username, real_name, role FROM user WHERE username ? AND password ?, (username, password) ).fetchone() if not user: return 用户名或密码错误, 401 session[user_id] user[user_id] session[username] user[username] session[role] user[role] return redirect(url_for(index))使用?占位符加参数元组让数据库驱动去处理转义这是最基础也最重要的安全习惯。关于密码存储说实话作业里用明文是可接受的因为评分老师需要直接查库看数据。但我要提醒一句真实项目中绝对不能用明文存密码至少要加盐后做 PBKDF2 或 bcrypt 哈希。作业可以讲道理开发必须讲安全。登录后的页面访问控制我用了一个 before_request 钩子统一拦截而不是在每个路由里重复写判断app.before_request def require_login(): allowed {login, static} if request.endpoint not in allowed and user_id not in session: return redirect(url_for(login))这样新加页面路由时不用想着补权限判断钩子会自动兜底。所有需要登录的页面都在进路由之前被检查规范且省事。3.3 借书和还书的事务处理借书接口是整个作业的重头戏逻辑顺序很容易漏。我的实现如下app.route(/borrow/int:book_id, methods[POST]) def borrow_book(book_id): db get_db() user_id session.get(user_id) today datetime.date.today() due_date today datetime.timedelta(days30) try: book db.execute( SELECT total_copies, available_copies FROM book WHERE book_id ?, (book_id,) ).fetchone() if not book: return 图书不存在, 404 if book[available_copies] 0: return 该书暂无可借副本, 400 active db.execute( SELECT record_id FROM borrow_record WHERE book_id ? AND user_id ? AND status borrowed, (book_id, user_id) ).fetchone() if active: return 你已借用该书请先归还, 400 db.execute( INSERT INTO borrow_record (user_id, book_id, borrow_date, due_date, status) VALUES (?, ?, ?, ?, borrowed), (user_id, book_id, today.isoformat(), due_date.isoformat()) ) db.execute( UPDATE book SET available_copies available_copies - 1 WHERE book_id ?, (book_id,) ) db.commit() return redirect(url_for(index)) except sqlite3.IntegrityError: db.rollback() return 操作失败请稍后重试, 400先说判断顺序。第一条查图书是否存在第二条判库存第三条判当前用户是否已借过同本书全部通过才写入借阅记录并扣库存。顺序不能乱如果先判重复再查库存某个用户同时触发两个请求时可能都通过检查造成超借。再说事务。SQLite 默认每条 DML 语句是自动提交的如果不手动控制事务INSERT 成功但 UPDATE 失败时库存就被扣了。我在 try 里执行多条写操作最后统一 commit任何一步抛异常就 rollback。这是小型系统里最朴素也最有效的事务用法。我还测试过一个细节available_copies 会不会被扣成负数。理论上库存判断已经挡住但为了绝对保险后来的版本把扣减语句改成了条件更新db.execute( UPDATE book SET available_copies available_copies - 1 WHERE book_id ? AND available_copies 0, (book_id,) )然后检查 rowcount如果为0说明更新没生效说明中间有竞态。这一步等于把判库存和扣库存合并成了一个原子操作。既是代码层面的最佳实践也是答辩时能说给老师听的亮点。还书接口是借书的逆过程有一个容易被忽略的判断点app.route(/return/int:record_id, methods[POST]) def return_book(record_id): db get_db() today datetime.date.today() record db.execute( SELECT book_id, due_date, status FROM borrow_record WHERE record_id ?, (record_id,) ).fetchone() if not record: return 借阅记录不存在, 404 if record[status] returned: return 该记录已归还, 400 db.execute( UPDATE borrow_record SET return_date ?, status returned WHERE record_id ?, (today.isoformat(), record_id) ) db.execute( UPDATE book SET available_copies available_copies 1 WHERE book_id ?, (record[book_id],) ) db.commit() return redirect(url_for(index))注意我没有把超期判断写进状态更新而是保持 status 为 borrowed只在页面显示时计算是否超期。原因很简单一条借阅记录的核心语义是借了没还超期是一种业务标注单独改状态会把简单模型搞复杂。真要统计超期时一条WHERE due_date date(now) AND status borrowed就够用了不需要提前改状态。3.4 图书检索的参数化查询图书列表页支持按书名模糊搜索和按分类筛选。模糊搜索用 LIKE 时照样用参数化app.route(/) def index(): keyword request.args.get(q, ).strip() category request.args.get(category, ).strip() sql SELECT * FROM book WHERE 1 1 params [] if keyword: sql AND title LIKE ? params.append(f%{keyword}%) if category: sql AND category ? params.append(category) sql ORDER BY book_id DESC books db.execute(sql, params).fetchall() return render_template(index.html, booksbooks)注意这里的 LIKE 拼接参数本身用%包裹而不是把%写进 SQL 再拼字符串。这样%是查询条件的一部分不是 SQL 语法的一部分不会破坏语句结构。4. 前端页面与联调踩坑记录4.1 页面与接口的对应设计前端我只写了三个页面登录页、图书列表页、新增图书页完全满足作业要求。页面之间用最简单的 HTML 表单 Jinja2 模板渲染没有引入任何前端框架因为这门课的考点是数据库不是前端工程化没必要给自己加戏。index.html 的核心表格片段长这样table tr th标题/thth作者/thth分类/th th库存/thth操作/th /tr {% for book in books %} tr td{{ book[title] }}/td td{{ book[author] }}/td td{{ book[category] }}/td td{{ book[available_copies] }}/{{ book[total_copies] }}/td td {% if book[available_copies] 0 %} form action/borrow/{{ book[book_id] }} methodpost button typesubmit借书/button /form {% else %} span已借完/span {% endif %} /td /tr {% endfor %} /table这里有个很重要的细节借书按钮我用了form methodpost而不是a href/borrow/1。这两种写法的差别很多人意识不到但实际影响很大。用链接的方式本质是 GET 请求浏览器刷新页面时会重新发起请求用户不小心刷新一下就重复借了一次书。改成 POST 表单后刷新会触发浏览器的确认重新提交提示从交互层面就挡住了误操作。这个知识点在答辩时非常加分因为多数同学想都没想过。4.2 联调顺序与典型问题联调阶段我按这个顺序测登录 → 图书检索 → 借书 → 重复借同一本 → 库存不足 → 还书 → 超期判断。这个顺序的策略是先测主链路、再测异常分支保证每个环节只依赖前面已经验证过的部分。实际联调中遇到一个典型的样式问题登录成功后页面正常跳转但 CSS 全部丢失页面变成了纯文本。排查后发现不是代码问题而是浏览器从登录页跳转到首页时 URL 从/login变成了/静态文件的相对路径解析发生了变化。解决办法是在模板里用 Flask 的url_for(static, filenamestyle.css)生成完整路径而不是写相对路径。这也是新手最容易忽略的环节。另一个问题是 session 时长。Flask 默认的 session 是浏览器会话级关掉浏览器就失效。作业演示时如果演示到一半去调代码重新打开页面又要登录体验很糟。我在 app.py 里加了两行app.config[SECRET_KEY] some-random-key app.config[PERMANENT_SESSION_LIFETIME] datetime.timedelta(hours2)并且登录成功时把session.permanent设为 True这样演示两小时内不用反复登录。SECRET_KEY 我用了随机字符串生产环境必须用环境变量注入不能写死在代码里。5. 常见问题与避坑速查表5.1 报错与排查对照表我把这次作业里最容易出现的报错和排查方向整理成一张对照表遇到问题先对号入座报错现象可能原因排查方向sqlite3.OperationalError: no such table没执行 init_data.py或表名拼写不一致先跑一次初始化脚本再核对建表语句sqlite3.IntegrityError: UNIQUE constraint failed插入了重复的 username 或 isbn查初始数据和表单输入是否撞车登录后 session 没生效app.secret_key 未配置检查是否设置了 SECRET_KEY点击借书按钮直接 500book_id 这个参数没正确传到路由模板里确认 form action 已绑定 book_id中文乱码模板没声明字符编码在 HTML head 里加 meta charsetutf-85000 端口被占用上次 Flask 进程没有正常退出查找进程并 kill或改用其他端口available_copies 出现负数借书接口的库存判断和扣减不在一个事务里检查 UPDATE 是否带 available_copies 0 条件另外补一条外人很少提的SQLite 的 datetime 函数在不同系统上返回格式可能略有差异比如有的带毫秒有的不带。为了避免这种不可控因素我所有时间字段都是用 Python 的date.today().isoformat()直接生成字符串写入格式统一为 YYYY-MM-DD数据库端不做任何转换。这样跨平台演示也不会出问题。5.2 我实际踩过的三个坑第一个坑是外键约束没生效。最初我写完所有表结构和高高兴兴开始写接口结果测试删除一个用户时借阅记录居然还在。查了半天才发现 SQLite 默认外键是关闭的不执行PRAGMA foreign_keys ON外键约束全是摆设。这个坑最隐蔽因为建表语句没有报错一切看起来正常只有实际删除时才暴露。第二个坑是我前面提到过的库存扣成负数。其实我在测试时故意用两个浏览器标签页同时点借书按钮纯靠先查询再更新的逻辑确实会产生两次查询都读到库存为1、然后两次都执行扣减最后可用库存变成-1。后来改成带条件的 UPDATE 加 rowcount 判断这个漏洞才堵死。多人同时操作的并发问题在单机演示时看不出来但一旦写到简历上就是硬伤。第三个坑比较反直觉删除图书时要考虑借阅记录里的外键引用。如果某本书还有未归还的借阅记录直接 DELETE book 会被外键约束挡住报错。我一开始想用ON DELETE CASCADE这样删书时自动删掉相关借阅记录。但仔细一想不对——如果管理员误删了一本仍在借的书连带借阅记录全没了学生的借书状态就丢了。最后我选择了最保守的规则有未归还记录的书不允许删除只能把 total_copies 设为0停用。这个设计适合作业答辩时老师问为什么不用级联删除的问题逻辑自洽且有业务考量。结尾做完这次作业我最深的体会是数据库课设的难点从来不在写代码而在设计决策。要不要冗余库存字段、要不要加部分唯一索引、外键冲突时怎么取舍这些选择没有绝对的对错但每个选择都要能说出理由。第三次作业之所以让人头疼就是因为它逼着你去思考这些为什么而不是照着教材敲一遍 SQL。最后分享一个扩展方向后续如果想让这个系统更完整可以加一个统计报表页面用一条SELECT book_id, COUNT(*) FROM borrow_record WHERE status returned GROUP BY book_id统计每本书的借阅次数排行。这是最自然的扩展也正好用上全文里一直在强调的 JOIN 和 GROUP BY 基本功。另外提交作业前记得把演示录屏和截图整理好光有能跑的代码评分老师没时间在你电脑上挨个点预留一份操作说明能让验收顺畅很多。