代码收藏家 Python SQLite数据库操作详解：建表、查询、事务处理实战指南
目录
前言
SQLite体积比较小，因此Python直接内置了SQLite数据库，可以无需额外安装就直接使用。
一、Python连接SQLite
import sqlite3 # 导入数据库功能
# 连接到数据库，在项目中创建数据库文件
# 参数为数据库文件
# 返回值为数据库连接对象
conn = sqlite3.connect('class25011.db')
print(conn)
# 通过连接对象获得游标对象
cursor = conn.cursor()
print(cursor)
# 使用数据库
# 使用完了要关闭数据库
cursor.close()
conn.close()
运行后发现成功创建了文件
conn = sqlite3.connect('class25011.db')如果没有db文件会创建
如果有会打开
但是现在这只是一个空文件，需要后续建表和插入数据
二、建表
import sqlite3 # 导入 SQLite 数据库模块
# 1. 连接数据库
conn = sqlite3.connect('class25011.db') # 创建或连接名为 class25011 的数据库
print(conn) # 打印数据库连接对象（验证连接是否成功）
# 2. 创建游标
cursor = conn.cursor() # 创建游标对象（用来执行SQL命令）
print(cursor) # 打印游标对象（验证游标是否创建）
# 3. 创建数据表
sql = """
CREATE TABLE nb(
mid INTEGER UNIQUE, -- 唯一整数编号
name TEXT NOT NULL, -- 非空姓名
time TEXT DEFAULT CURRENT_TIME, -- 默认当前时间
birth TEXT DEFAULT CURRENT_DATE, -- 默认当前日期
age INTEGER CHECK(age>=18),-- 年龄必须≥18
sex TEXT DEFAULT '男女', -- 默认性别为'男女'
home TEXT PRIMARY KEY, -- 主键（唯一且非空）
sal REAL CHECK(sal>=5000), -- 工资≥5000
job TEXT DEFAULT '无业游民' -- 默认职业
);
"""
cursor.execute(sql) # 执行建表语句（注意：只能执行一次，重复执行会报错）
# 4. 修改表结构
cursor.execute("ALTER TABLE nb ADD COLUMN university TEXT DEFAULT '清华大学';") # 新增大学列
# 5. 关闭连接
cursor.close() # 关闭游标
conn.close() # 关闭数据库连接在Python中三个引号实际上不是多行注释，在Python中没有专门的多行注释语法
执行之后文件大小发生了改变
用SQLiteSpy打开
三、插入数据
import sqlite3 # 导入数据库功能
# 连接到数据库，在项目中创建数据库文件
# 参数为数据库文件
# 返回值为数据库连接对象
conn = sqlite3.connect('class25011.db')
print(conn)
# 通过连接对象获得游标对象
cursor = conn.cursor()
print(cursor)
# 建表
sql = """
CREATE TABLE nb(
mid INTEGER UNIQUE,
name TEXT NOT NULL,
time TEXT DEFAULT CURRENT_TIME,
birth TEXT DEFAULT CURRENT_DATE,
age INTEGER CHECK(age>=18),
sex TEXT DEFAULT('男女'),
home TEXT PRIMARY KEY,
sal REAL CHECK(sal>=5000),
job TEXT DEFAULT '无业游民'
);
"""
# 执行建表语句
# cursor.execute(sql) # 只能成功执行一次
# 增加一列
# cursor.execute("ALTER TABLE nb ADD COLUMN university TEXT DEFAULT '清华大学';")
# 直接执行插入语句
sql = '''
INSERT INTO nb
VALUES(1,'杨龙霄','now','2003-07-22 00:00:01',21,'男','山东省潍坊市寿光市',60000,'老板','北大')
'''
cursor.execute(sql)
# 拼接数据插入：在SQL语句中使用?占位符表示要替换的参数
mid = int(input('请输入您要录入的学生序号：'))
name = input('请输入您要录入的学生姓名')
# 组装数据
data = (mid, name)
# 参数1：带占位符的SQL语句
# 参数2：占位符的具体数据
cursor.execute('''
INSERT INTO nb
VALUES(?,?,DATETIME('now'),'2001-02-21 00:00:01',23,'男','河北省河间市沧州区',15000,'总裁','科大')
''', data)
# 增删改操作如果要生效，必须执行提交
conn.commit()
# 使用完了要关闭数据库
cursor.close()
conn.close()
插入语句也支持同时执行多条：
import sqlite3 # 导入数据库功能
# 连接到数据库，在项目中创建数据库文件
# 参数为数据库文件
# 返回值为数据库连接对象
conn = sqlite3.connect('class25011.db')
# 通过连接对象获得游标对象
cursor = conn.cursor()
data = []
# 连续输入三个数据
for i in range(3):
mid = int(input('请输入您要录入的学生序号：'))
name = input('请输入您要录入的学生姓名：')
address = input('请输入您要录入的学生地址：')
# 加入列表
data.append((mid, name, address))
print(data)
# 把多个学生信息与SQL语句结合并执行
# 参数1：带占位符的SQL语句
# 参数2：合并多个数据的列表
cursor.executemany('''
INSERT INTO nb
VALUES(?,?,DATETIME('now'),'2001-02-21 00:00:01',23,'男',? ,15000,'总裁','科大')
''', data)
# 增删改操作如果要生效，必须执行提交
conn.commit()
# 使用完了要关闭数据库
cursor.close()
conn.close()
四、删除数据
import sqlite3 # 导入数据库功能
# 连接到数据库，在项目中创建数据库文件
# 参数为数据库文件
# 返回值为数据库连接对象
conn = sqlite3.connect('class25011.db')
# 通过连接对象获得游标对象
cursor = conn.cursor()
id = int(input('您要删除id为几的人:'))
cursor.execute("DELETE FROM nb WHERE mid=?", (id,))
# 增删改操作如果要生效，必须执行提交
conn.commit()
# 使用完了要关闭数据库
cursor.close()
conn.close()
五、更新数据
import sqlite3 # 导入数据库功能
# 连接到数据库，在项目中创建数据库文件
# 参数为数据库文件
# 返回值为数据库连接对象
conn = sqlite3.connect('class25011.db')
# 通过连接对象获得游标对象
cursor = conn.cursor()
# 把id号为?的人的大学改为?
id = int(input('您要修改mid为几的数据？'))
university = input("请输入新更改的大学:")
data = (university, id) # 此处的顺序应该是?的顺序
# 提交更新操作
cursor.execute("UPDATE nb SET university=? WHERE mid=?;", data)
# 增删改操作如果要生效，必须执行提交
conn.commit()
# 使用完了要关闭数据库
cursor.close()
conn.close()
六、事务
原子性：指事务的不可分割性，一个事务的所有操作要么不间断地全部被执行，要么一个也没有执行。
增删改操作都属于被事务控制的操作，要么全都执行，要么全不执行。所有的增删改操作都是先在缓存区执行的，并没有真正的操作数据库。
SQLiteSpy实际上简化了事务，相当于每次执行增删改语句后自动提交事务。
七、查询操作
import sqlite3 # 导入数据库功能
# 连接到数据库，在项目中创建数据库文件
# 参数为数据库文件
# 返回值为数据库连接对象
conn = sqlite3.connect('class25011.db')
# 通过连接对象获得游标对象
cursor = conn.cursor()
age = int(input("请输入要查询的年龄下限："))
# 单元素元组必须增加逗号
print(type((age)))
print(type((age,)))
# 执行查询
cursor.execute("SELECT * FROM nb WHERE age>?", (age,))
# 拿到查询结果(很多行)
rows = cursor.fetchall()
print(rows)
# 返回结果的格式
# 最外层是一个列表，每一行是一个元组，每一行的列数据是元组中的元素
# 循环遍历输出
for row in rows:
print(row)
print("")
# 输出所有人的姓名
for row in rows:
print(row[1])
# 增删改操作如果要生效，必须执行提交
conn.commit()
# 使用完了要关闭数据库
cursor.close()
conn.close()
总结
本文全面解析Python内置SQLite数据库操作技术，详细演示了数据库连接、游标获取及资源释放流程，重点讲解数据表创建时的字段约束设置与表结构修改方法，通过具体案例展示数据插入的三种方式（直接插入、占位符拼接、批量插入）及删除更新操作实现，阐释事务的原子性特性与commit提交机制，最后解析查询操作的执行流程与结果集处理技巧，涵盖字段默认值设置、数据类型校验、主键约束等核心知识点，提供从环境搭建到CRUD完整操作的实战指导。
作者：夜松云
