Python操作SQLite数据库：从基础语法到完整项目实战 - 详解

文章目录

• • 一、什么是SQLite数据库？
  • 二、Python操作SQLite数据库的基本步骤
  • • 1、导入SQLite模块
    • 2、数据库连接
  • 三、创建和管理数据库表
  • • 1、创建数据表
    • 2、常见数据类型
    • 3、约束条件
  • 四、基本数据操作
  • • 1、插入数据
    • 2、查询数据
    • 3、更新数据
    • 4、删除数据
  • 五、函数版的数据库基本操作
  • 六、面向对象版的增删改查操作
  • 结语

一、什么是SQLite数据库？

SQLite 是一个轻量级的、开源的、嵌入式的 SQL 数据库引擎。与 MySQL、PostgreSQL 等传统数据库不同，SQLite
不需要单独的服务器进程，整个数据库就是一个单独的文件。这种设计让 SQLite 在小型应用、移动应用、嵌入式系统中特别受欢迎。

SQLite 的核心特点：
1、零配置：无需安装和配置，Python内置支持
2、无服务器：数据库就是文件，不需要数据库服务器
3、跨平台：数据库文件可以在不同操作系统间移植
4、事务支持：支持ACID（原子性、一致性、隔离性、持久性）事务
5、小巧高效：整个库只有几百KB，但功能完整

那为什么要学习SQLite呢?
对于 Python 初学者来说，SQLite 是学习数据库操作的最佳选择：

• 学习曲线平缓：语法简单，易于理解
• 环境简单：不需要安装额外软件
• 实践性强：可以在本地快速创建和测试数据库
• 应用广泛：很多移动应用和小型网站使用 SQLite
• 过渡桥梁：学会 SQLite 后，再学其他数据库会更容易

二、Python操作SQLite数据库的基本步骤

1、导入SQLite模块

Python 标准库中已经包含了 SQLite 模块，无需额外安装：import sqlite3
注：需要在PyCharm中安装一个插件，因为IDE需要数据库插件才能直接查看数据库中的内容：
在PyCharm设置中找到插件选项，在应用市场搜索SimpleSqliteBrowser插件安装即可查看数据库内容。

2、数据库连接

创建和连接数据库 在 SQLite 中，数据库连接是操作数据库的起点：

# 连接到数据库（如果不存在则创建）
connection = sqlite3.connect('my_database.db')
# 创建一个游标对象（用于执行SQL语句）
cursor = connection.cursor()
print("数据库连接成功！")
print(f"数据库文件: my_database.db")
print(f"游标对象: {cursor}")
# 不要忘记关闭连接（在实际应用中应该在 finally 中关闭）
connection.close()

注：连接（connection）：代表与数据库文件的连接，一个应用程序可以有多个连接
游标：用于执行SQL语句和获取结果，每个连接可以有多个游标

# 导入sqlite3模块，用于SQLite数据库操作
import sqlite3
def demonstrate_connection_cursor():
"""演示 sqlite3 连接对象和游标对象之间的关系"""
# 创建一个连接
conn = sqlite3.connect('example.db')
# 创建多个游标
cursor1 = conn.cursor()
cursor2 = conn.cursor()
print(f"连接对象: {conn}")
print(f"游标1: {cursor1}")
print(f"游标2: {cursor2}")
# 输出False，因为两个游标是不同的对象
print(f"两个游标是否相同: {cursor1 is cursor2}")
# 使用不同的游标执行操作
cursor1.execute("CREATE TABLE IF NOT EXISTS test1 (id INTEGER)")
cursor2.execute("CREATE TABLE IF NOT EXISTS test2 (id INTEGER)")
# 提交更改
conn.commit()
# 关闭连接（会自动关闭所有游标）
conn.close()
# 演示数据库连接游标的方法
demonstrate_connection_cursor()

在数据库中有两个表test1和test2

三、创建和管理数据库表

1、创建数据表

在数据库中，数据存储在表中。表由行和列组成：

# 导入sqlite3模块，用于操作SQLite数据库
import sqlite3
# 创建与SQLite数据库的连接
conn = sqlite3.connect('my_school.db')
# 创建一个游标对象，用于执行SQL语句
crs = conn.cursor()
# 使用多行字符串执行SQL语句，创建student表（如果不存在）
# 表包含以下字段：
# id: 整型主键，自动递增
# name: 文本类型，不能为空
# age: 整型，不能为空
# address: 文本类型，不能为空
# grade: 文本类型，不能为空
crs.execute('''
create table if not exists student(
id integer primary key  autoincrement,
name text not null ,
age integer not null ,
address text not null,
grade text not null
)
''')
# 提交事务，将更改保存到数据库
conn.commit()
# 关闭数据库连接
conn.close()

会在数据库student表中显示id、name、age、address、grade字段（因为还未曾向表中添加数据，因此表中没有数据，是一个空数据表格）。

2、常见数据类型

SQLite 支持以下主要数据类型：

数据类型	描述	示例
integer	整数	1,100,-50
real	浮点数	3.14, 2.718
text	文本字符串	‘Hello’,‘张三’
blob	二进制	图片、文件等
NULL	空值	NULL

3、约束条件

约束用于限制表中数据的规则：

# 导入sqlite3模块，用于SQLite数据库操作
import sqlite3
def create_table_with_constraints():
"""创建带约束的表"""
conn = sqlite3.connect('constraints_demo.db')
cursor = conn.cursor()
create_table_sql = '''
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,      -- 主键约束
employee_id TEXT UNIQUE NOT NULL,          -- 唯一且非空约束
name TEXT NOT NULL,                        -- 非空约束
age INTEGER CHECK (age >= 18 AND age <= 65),  -- 检查约束
department TEXT DEFAULT '未分配',           -- 默认值约束
salary REAL CHECK (salary >= 0),           -- 检查约束
hire_date DATE                             -- 日期类型
)
'''
# 执行创建表的SQL语句
cursor.execute(create_table_sql)
# 提交数据库事务，确保表被创建
conn.commit()
# 关闭数据库连接
conn.close()
# 调用创建表的函数
create_table_with_constraints()

四、基本数据操作

1、插入数据

向表中添加新记录：

# 导入sqlite3模块，用于操作SQLite数据库
import sqlite3
# 创建与SQLite数据库的连接，如果数据库不存在则创建，存在则打开
connection = sqlite3.connect('my_school.db')
# 创建游标对象，用于执行SQL语句
crs = connection.cursor()
# 使用多行字符串执行SQL语句，创建student表（如果不存在）
crs.execute('''
create table if not exists student(
id integer primary key  autoincrement,
name text not null ,
age integer not null ,
address text not null,
grade text not null
)
''')
# 新增(插入)数据： insert into  table_name  values
crs.execute("insert into student(name,age,address,grade) values('张三', 18, '深圳', '一年级' )")
crs.execute("insert into student(name,age,address,grade) values('李四', 19, '成都', '二年级' )")
crs.execute("insert into student(name,age,address,grade) values('王五', 20, '长沙', '三年级' )")
# 提交事务
connection.commit()
# 关闭数据库连接
connection.close()

2、查询数据

从表中检索数据：

# 导入sqlite3模块，用于操作SQLite数据库
import sqlite3
# 创建与SQLite数据库的连接，如果数据库不存在则创建，存在则打开
connection = sqlite3.connect('my_school.db')
# 创建游标对象，用于执行SQL语句
crs = connection.cursor()
# 使用多行字符串执行SQL语句，创建student表（如果不存在）
crs.execute('''
create table if not exists student(
id integer primary key  autoincrement,
name text not null ,
age integer not null ,
address text not null,
grade text not null
)
''')
# 查询数据： select 列名（或者 *） from table_name
# * 表示查询所有列
crs.execute("select * from student ")
# 获取所有数据,返回一个元组列表
rows = crs.fetchall()
# print(rows)
for row in rows:
print(row)

3、更新数据

修改现有记录：

# 导入sqlite3模块，用于操作SQLite数据库
import sqlite3
# 创建与SQLite数据库的连接，如果数据库不存在则创建，存在则打开
connection = sqlite3.connect('my_school.db')
# 创建游标对象，用于执行SQL语句
crs = connection.cursor()
# 使用多行字符串执行SQL语句，创建student表（如果不存在）
crs.execute('''
create table if not exists student(
id integer primary key  autoincrement,
name text not null ,
age integer not null ,
address text not null,
grade text not null
)
''')
# 修改数据(一定要带有条件): update table_name set  where
# where 是要满足的条件，set 是要修改的字段的，以下的语句是将王五的年龄改为21
crs.execute("update student set age = 21 where name = '王五'")
# 提交事务
connection.commit()
# 关闭数据库连接
connection.close()

4、删除数据

从表中删除记录：

# 导入sqlite3模块，用于操作SQLite数据库
import sqlite3
# 创建与SQLite数据库的连接，如果数据库不存在则创建，存在则打开
connection = sqlite3.connect('my_school.db')
# 创建游标对象，用于执行SQL语句
crs = connection.cursor()
# 使用多行字符串执行SQL语句，创建student表（如果不存在）
crs.execute('''
create table if not exists student(
id integer primary key  autoincrement,
name text not null ,
age integer not null ,
address text not null,
grade text not null
)
''')
# 删除数据(一定要带有条件)： delete from  table_name  where
# where 是要满足的条件，以下的语句是将李四这一行数据删除
crs.execute("delete from student where name = '李四'")
# 删除整个表格：drop table table_name
# 提交事务
connection.commit()
# 关闭数据库连接
connection.close()

五、函数版的数据库基本操作

import sqlite3
# 创建数据库并进行连接 sqlite3.connect()
def create_connection(strs):
"""
创建与SQLite数据库的连接
参数:
strs (str): 数据库文件的路径或名称
返回:
Connection: 成功时返回SQLite数据库连接对象
None: 失败时返回None
异常:
sqlite3.Error: 当连接数据库时发生错误，会打印错误信息
"""
try:
# 尝试建立与SQLite数据库的连接
conn = sqlite3.connect(f"{strs}")
# 返回连接对象
return conn
except sqlite3.Error as e:
# 捕获并打印SQLite错误
print(f"错误:{e}")
# 创建表
def create_table(conn):
"""
创建学生信息表
参数:
conn: sqlite3数据库连接对象
功能:
如果student表不存在，则创建该表，包含以下字段:
- id: 整型主键，自动递增
- name: 文本类型，不能为空
- age: 整型，不能为空
- address: 文本类型，不能为空
- grade: 文本类型，不能为空
异常处理:
捕获sqlite3.Error并打印错误信息
"""
try:
crs = conn.cursor()  # 创建游标对象
crs.execute('''  -- 执行SQL语句创建表
create table if not exists student(
id integer primary key  autoincrement,  -- 设置id为主键，自动递增
name text not null ,  -- 姓名字段，文本类型，非空
age integer not null ,  -- 年龄字段，整型，非空
address text not null,  -- 地址字段，文本类型，非空
grade text not null  -- 成绩字段，文本类型，非空
)
''')
except sqlite3.Error as e:  # 捕获数据库操作可能出现的错误
print(f"错误:{e}")  # 打印错误信息
def insert_data(conn, data):
"""
向数据库中插入学生数据
参数:
conn: 数据库连接对象
data: 包含学生信息的元组，格式为(name, age, address, grade)
返回:
无返回值
异常:
捕获并打印sqlite3.Error异常
"""
try:
# 创建游标对象
crs = conn.cursor()
# 执行SQL插入语句，使用参数化查询防止SQL注入
crs.execute("insert into student(name, age, address, grade) values(?, ?, ?, ?)", data)
except sqlite3.Error as e:
# 捕获并打印数据库错误
print(f"错误:{e}")
def delete_data(conn, data):
"""
删除数据库中指定学生记录的函数
参数:
conn: 数据库连接对象
data: 要删除的学生姓名
返回:
无返回值
异常:
当数据库操作出现错误时，会捕获sqlite3.Error并打印错误信息
"""
try:
# 创建游标对象
crs = conn.cursor()
# 执行删除SQL语句，使用参数化查询防止SQL注入
crs.execute("delete from student where name=?", data)
except sqlite3.Error as e:
# 捕获并打印数据库操作中的错误
print(f"错误:{e}")
def update_data(conn, data):
"""
更新学生数据的函数
:param conn: 数据库连接对象
:param data: 包含学生信息的元组，格式为(年龄, 地址, 姓名)
:return: 无返回值
"""
try:
# 创建游标对象
crs = conn.cursor()
# 执行SQL更新语句，使用参数化查询防止SQL注入
# 更新指定姓名的学生的年龄和地址
crs.execute("update student set age=? , address=? where name=?", data)
except sqlite3.Error as e:
# 捕获并打印数据库操作中的错误
print(f"错误:{e}")
def select_data(conn):
"""
从数据库中查询学生数据并打印
参数:
conn: 数据库连接对象
异常处理:
捕获并打印sqlite3数据库操作可能出现的错误
"""
try:
# 创建游标对象
crs = conn.cursor()
# 执行SQL查询语句，获取所有学生数据
crs.execute("select * from student")
# 获取查询结果的所有行
cs = crs.fetchall()
# 遍历并打印每一条学生记录
for c in cs:
print(c)
except sqlite3.Error as e:
# 打印数据库操作错误信息
print(f"错误:{e}")
if __name__ == "__main__":
connect = create_connection("my_school.db")
create_table(connect)
# 插入数据
insert_data(connect, ("张三", 18, "北京", "一年级"))
insert_data(connect, ("李四", 19, "上海", "二年级"))
# # 删除数据
# delete_data(connect, "张三")
# # 更新数据
# update_data(connect, ("李四", 20, "北京"))
# 查询数据
select_data(connect)
# 关闭数据库连接
connect.commit()
# 关闭数据库连接
connect.close()

六、面向对象版的增删改查操作

import sqlite3
class DBHelper:
def __init__(self, my_file):
"""
初始化数据库连接
:param my_file: 数据库的名称
self.connection:与数据库连接的对象
"""
self.my_file = my_file
self.connection = None
# 创建数据库  并连接数据
def create_database(self):
"""
创建数据库连接的方法
尝试创建与SQLite数据库的连接，如果发生错误则打印错误信息
"""
try:
# 尝试创建与SQLite数据库的连接，连接文件名为self.my_file
self.connection = sqlite3.connect(self.my_file)
except sqlite3.Error as e:
# 捕获SQLite错误并打印错误信息
print(f"创建数据库错误：{e}")
def create_table(self):
"""
创建学生信息表的方法
该方法尝试在数据库中创建一个名为student的表，如果表已存在则不会重复创建。
表包含以下字段：
- id: 整型主键，自动递增
- name: 文本类型，不允许为空
- age: 整型，不允许为空
- address: 文本类型，不允许为空
- grade: 文本类型，不允许为空
如果创建过程中发生错误，会捕获sqlite3.Error异常并打印错误信息。
"""
try:
# 创建数据库游标对象
crs = self.connection.cursor()
# 执行SQL创建表语句
crs.execute("""
create table if not exists student(
id integer primary key  autoincrement,
name text not null ,
age integer not null ,
address text not null,
grade text not null
)
""")
# 提交事务
self.connection.commit()
except sqlite3.Error as e:
# 捕获并打印数据库错误
print(f"创建表错误：{e}")
def insert_table(self, name, age, address, grade):
try:
# 创建数据库游标对象，用于执行SQL语句
crs = self.connection.cursor()
# 先查询数据库中是否有相同的用户名
# 使用参数化查询防止SQL注入，查询name字段是否与传入的name参数相同
crs.execute("select * from student where name = ?", (name,))
# 当返回的数据为空，则代表可以新增数据，不为空则用户名已存在
# fetchall()获取查询结果的所有行
sd_tuple = crs.fetchall()
# 判断查询结果是否为空
if len(sd_tuple) == 0:
# 如果用户名不存在，则执行插入操作
# 使用参数化查询插入新数据到student表
crs.execute("insert into student(name, age, address, grade) values(?, ?, ?, ?)", (name, age, address,
grade,))
else:
# 如果用户名已存在，打印提示信息
print("用户名已存在")
# 提交事务，将更改保存到数据库
self.connection.commit()
# 捕获sqlite3数据库操作可能出现的异常
except sqlite3.Error as e:
# 打印错误信息
print(f"新增数据错误：{e}")
# 删除数据
def delete_table(self, name):
"""
删除指定学生记录的方法
参数:
name (str): 要删除的学生姓名
异常:
sqlite3.Error: 当数据库操作出错时捕获并打印错误信息
"""
try:
# 创建数据库游标对象
crs = self.connection.cursor()
# 先查询数据库中是否有相同的用户名
# 使用参数化查询防止SQL注入，删除name指定的学生记录
crs.execute("delete from student where name = ?", (name,))
# 提交事务，使删除操作生效
self.connection.commit()
except sqlite3.Error as e:
# 捕获并打印数据库操作错误
print(f"删除数据错误：{e}")
# 修改数据
def update_table(self, new_name, address, old_name):
"""
更新学生表中指定学生的姓名和地址信息
参数:
new_name (str): 要更新的新姓名
address (str): 要更新的新地址
old_name (str): 要查找的原始姓名
异常:
sqlite3.Error: 当数据库操作出现错误时捕获并打印错误信息
"""
try:
# 创建数据库游标对象
crs = self.connection.cursor()
# 执行SQL更新语句，使用参数化查询防止SQL注入
crs.execute("update student set name = ?,address = ? where  name = ?", (new_name, address, old_name,))
# 提交事务，使更改永久生效
self.connection.commit()
except sqlite3.Error as e:
# 捕获并打印数据库操作错误
print(f"删除数据错误：{e}")
# 查询数据
def select_table(self):
try:
# 创建数据库游标对象
crs = self.connection.cursor()
# 执行查询所有学生数据的SQL语句
list_data = crs.execute("select * from student")
# 遍历查询结果并打印每个学生的信息
# 假设学生表有三列，分别打印每列的值
for i in list_data:
print(i[0], i[1], i[2])
# 提交事务
self.connection.commit()
except sqlite3.Error as e:
# 捕获并处理SQLite数据库操作中的错误
print(f"删除数据错误：{e}")
def close_connection(self):
"""
关闭数据库连接的方法
如果当前存在活动的数据库连接，则关闭该连接，并打印关闭成功的提示信息
"""
if self.connection:  # 检查是否存在活动的数据库连接
self.connection.close()  # 执行关闭数据库连接的操作
print("数据库连接已关闭")  # 输出连接已关闭的提示信息
# 创建数据库辅助类实例，指定数据库文件名为"my_database1.db"
db = DBHelper("my_database1.db")
# 调用实例方法创建数据库
db.create_database()
# 调用实例方法创建数据表
db.create_table()
# 新增数据
db.insert_table("张三", 18, "怀化", "一年级")
db.insert_table("李四", 20, "常德", "二年级")
db.insert_table("王五", 22, "长沙", "三年级")
# 删除数据
db.delete_table("张三")
# 修改数据
db.update_table("赵六", "株洲", "李四")
# 查询数据
db.select_table()
# 关闭连接
db.close_connection()

结语

SQLite以其轻量级和零配置的特性，为Python初学者打开了数据库编程的大门。从基础的数据类型到完整的增删改查操作，我们看到了如何将数据持久化存储的完整过程。无论是函数式的简洁实现还是面向对象的封装设计，SQLite都展现出其灵活性和实用性。掌握SQLite不仅意味着学会了数据库的基础操作，更是理解了数据管理的核心思想。它为我们在未来的软件开发中处理更复杂的数据场景，打下了坚实的技术基础。