Python内置SQLite数据库操作全解析：从建表到事务处理实战指南

本文系统讲解Python操作SQLite数据库的全流程技术，涵盖数据库连接、数据表创建与结构修改、数据增删改查的多种实现方式、事务控制机制及查询结果处理方法，通过具体代码示例演示字段约束设置、占位符参数绑定、批量数据插入等关键技术点，为开发者提供基于Python的轻量级数据库开发完整解决方案。

夜松云

1174人浏览 · 2025-03-03 22:06:47

夜松云 · 2025-03-03 22:06:47 发布

前言
一、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()

六、事务

原子性：指事务的不可分割性，一个事务的所有操作要么不间断地全部被执行，要么一个也没有执行。

增删改操作都属于被事务控制的操作，要么全都执行，要么全不执行。所有的增删改操作都是先在缓存区执行的，并没有真正的操作数据库。

如果最后没有使用commit提交事务，之前缓存区累积的增删改操作都失效；
如果最后使用commit提交事务，之前缓存区的增删改操作都同步提交到真正的数据库中。

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完整操作的实战指导。