SQLite3数据库操作实战：从基础到高级技巧

1. SQLite3数据库操作实战练习指南作为轻量级数据库的代表SQLite3凭借其零配置、无服务端的特性成为嵌入式系统和中小型应用的理想选择。我在实际项目中发现掌握SQLite3的核心操作能显著提升开发效率。下面通过一组精心设计的练习题带你系统掌握SQLite3的实用技巧。提示所有练习建议在Python环境中完成需提前安装sqlite3模块Python标准库自带1.1 基础环境搭建首先创建练习用的数据库环境import sqlite3 conn sqlite3.connect(practice.db) # 创建或连接数据库 cursor conn.cursor() # 获取游标这个简单的连接操作有几个关键点需要注意数据库文件不存在时会自动创建连接对象需要显式关闭建议使用with语句自动管理游标是执行SQL命令的主要接口1.2 基础表操作练习练习1创建学生信息表CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER CHECK(age 0), gender TEXT CHECK(gender IN (M, F)), score REAL DEFAULT 0.0 );这个建表语句包含了几个重要约束PRIMARY KEY设置自增主键NOT NULL非空约束CHECK自定义校验规则DEFAULT默认值设置常见错误排查表已存在时重复创建会报错使用IF NOT EXISTS避免违反约束条件时会抛出sqlite3.IntegrityError字段类型不匹配会导致隐式转换建议显式类型检查2. CRUD操作深度练习2.1 数据插入的多种方式练习2批量插入学生数据data [ (张三, 18, M, 85.5), (李四, 19, F, 92.0), (王五, 20, M, 78.5) ] # 方式1逐条插入 for student in data: cursor.execute(INSERT INTO students (name, age, gender, score) VALUES (?, ?, ?, ?), student) # 方式2批量插入更高效 cursor.executemany(INSERT INTO students (name, age, gender, score) VALUES (?, ?, ?, ?), data)性能对比单条插入简单但效率低每条都需解析SQL批量插入推荐方式减少IO操作事务包装大批量插入时应显式使用事务2.2 复杂查询技巧练习3多条件组合查询# 查询年龄大于18且分数高于80的女生 cursor.execute( SELECT * FROM students WHERE age ? AND score ? AND gender ? ORDER BY score DESC , (18, 80.0, F))查询优化建议使用参数化查询?占位符防止SQL注入复杂查询适当换行保持可读性ORDER BY子句对结果排序大数据量查询应添加LIMIT分页3. 高级特性实战3.1 事务处理练习练习4转账事务模拟def transfer_money(conn, from_id, to_id, amount): try: conn.execute(BEGIN TRANSACTION) # 扣款方 conn.execute(UPDATE accounts SET balance balance - ? WHERE id ?, (amount, from_id)) # 收款方 conn.execute(UPDATE accounts SET balance balance ? WHERE id ?, (amount, to_id)) conn.commit() except Exception as e: conn.rollback() raise e事务要点BEGIN TRANSACTION显式开始事务commit()提交所有操作异常时rollback()回滚使用with语句可自动管理事务3.2 自定义函数扩展练习5注册Python函数为SQL函数def reverse_string(s): return s[::-1] conn.create_function(reverse, 1, reverse_string) cursor.execute(SELECT name, reverse(name) FROM students)扩展能力create_function()注册自定义函数可在SQL中直接调用Python函数支持聚合函数需实现step/finalize方法注意函数线程安全问题4. 性能优化与实战技巧4.1 索引优化练习练习6为高频查询字段创建索引-- 单列索引 CREATE INDEX idx_students_score ON students(score); -- 复合索引 CREATE INDEX idx_students_age_gender ON students(age, gender);索引使用原则为WHERE/JOIN/ORDER BY常用字段创建复合索引注意字段顺序索引会降低写入速度使用EXPLAIN分析查询计划4.2 数据库维护操作练习7数据库备份与恢复# 备份到文件 with open(backup.sql, w) as f: for line in conn.iterdump(): f.write(%s\n % line) # 从备份恢复 with sqlite3.connect(restored.db) as new_conn: new_conn.executescript(open(backup.sql).read())维护技巧iterdump()生成SQL备份executescript()执行批量SQL定期VACUUM优化存储空间PRAGMA命令查看数据库状态5. 综合实战项目5.1 学生管理系统实现结合所学知识实现一个完整的学生成绩管理系统class StudentDB: def __init__(self, db_path): self.conn sqlite3.connect(db_path) self._create_tables() def _create_tables(self): sql CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, class TEXT, subjects TEXT -- JSON存储选修科目 ); CREATE TABLE IF NOT EXISTS scores ( student_id INTEGER, subject TEXT, score REAL, FOREIGN KEY(student_id) REFERENCES students(id) ); self.conn.executescript(sql) def add_student(self, name, class_name, subjects): # 实现添加学生逻辑 pass def update_score(self, student_id, subject, score): # 实现分数更新 pass def get_top_students(self, n10): # 获取前N名学生 pass设计要点使用类封装数据库操作合理设计表关系一对多使用JSON存储复杂字段添加适当的错误处理5.2 性能对比测试练习8批量插入性能优化对比import time def test_performance(): # 测试普通插入 start time.time() # 测试代码... print(f普通插入耗时: {time.time() - start:.3f}s) # 测试事务包装插入 start time.time() with conn: # 测试代码... print(f事务插入耗时: {time.time() - start:.3f}s)优化结果示例插入10000条记录普通插入12.345s事务插入0.876s批量插入0.123s这个对比直观展示了不同写入方式的性能差异在实际项目中应根据数据量选择合适的写入策略。