Python_SQLite_数据库开发完全指南
·
Python SQLite 数据库开发完全指南：从基础到 GUI 实战
目录
- SQLite 简介与核心特性
- Python 操作 SQLite 数据库
- 2.1 环境准备与模块导入
- 2.2 创建数据库与数据表
- 2.3 数据插入操作
- 2.4 数据查询操作
- 2.5 数据更新与删除
- 2.6 整合版：完整 CRUD 封装
- 2.7 面向对象版：DatabaseManager 类
- PySide6 操作 SQLite 数据库
- 3.1 Qt SQL 模块简介
- 3.2 SQL 基本语法回顾
- 3.3 PySide6 与 SQLite 集成实战
- 数据库可视化工具推荐
- 总结与最佳实践
1. SQLite 简介与核心特性
1.1 什么是 SQLite
SQLite 是一个基于 C 语言开发的轻量级嵌入式关系型数据库引擎。与传统数据库（如 MySQL、PostgreSQL）不同,SQLite 不需要独立的服务器进程,整个数据库完整地存储在一个单一的磁盘文件中。这种架构设计使其成为嵌入式系统、桌面应用和中小型 Web 应用的理想选择。
Python 标准库内置了 sqlite3 模块,提供了与 SQLite 数据库交互的完整接口,开发者无需额外安装任何第三方依赖即可直接使用。
1.2 核心特性
| 特性 | 说明 |
|---|---|
| 轻量级 | 体积小巧,零配置部署,适合嵌入到各类应用程序中 |
| 易用性 | 语法兼容标准 SQL,学习曲线平缓 |
| 高性能 | 在处理小型至中型数据集时表现优异,读写速度快 |
| 跨平台 | 支持 Windows、Linux、macOS 等主流操作系统 |
| 事务支持 | 完全支持 ACID 事务,确保数据一致性 |
| 零配置 | 无需安装、无需启动服务,开箱即用 |
2. Python 操作 SQLite 数据库
2.1 环境准备与模块导入
SQLite 的 Python 支持已集成在标准库中,直接使用 import sqlite3 即可：
import sqlite3 # 导入 sqlite3 模块,无需 pip 安装
2.2 创建数据库与数据表
使用 sqlite3.connect() 建立数据库连接。若指定文件不存在,SQLite 会自动创建一个新的数据库文件。
import sqlite3
# 连接到 SQLite 数据库（若不存在则自动创建）
conn = sqlite3.connect('students.db')
# 创建游标对象,用于执行 SQL 语句
cur = conn.cursor()
# 创建 students 表（仅当表不存在时创建）
cur.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT, -- 整型主键,自动递增
name TEXT NOT NULL, -- 文本型,非空约束
age INTEGER, -- 整型
grade TEXT -- 文本型
)
''')
# 提交事务并关闭连接
conn.commit()
conn.close()
关键概念解析：
PRIMARY KEY AUTOINCREMENT：主键自动递增,每插入一条记录,id字段自动加 1NOT NULL：非空约束,确保该字段必须有值conn.commit()：提交事务,将内存中的修改持久化到磁盘conn.close()：关闭连接,释放系统资源
2.3 数据插入操作
推荐使用参数化查询（? 占位符）,可有效防止 SQL 注入攻击：
conn = sqlite3.connect('students.db')
cur = conn.cursor()
# 使用参数化查询插入单条记录
cur.execute(
'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)',
('Alice', 20, 'A')
)
cur.execute(
'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)',
('Bob', 22, 'B')
)
cur.execute(
'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)',
('Charlie', 23, 'C')
)
conn.commit()
conn.close()
批量插入可使用 executemany() 方法：
datas = [
('Alice', 20, 'A'),
('Bob', 22, 'B'),
('Charlie', 23, 'C')
]
cur.executemany(
'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)',
datas
)
2.4 数据查询操作
conn = sqlite3.connect('students.db')
cur = conn.cursor()
# 执行查询语句
cur.execute('SELECT * FROM students')
# 获取所有查询结果,返回元组列表
rows = cur.fetchall()
# 遍历结果集
for row in rows:
print(row) # 输出: (1, 'Alice', 20, 'A')
conn.close()
常用查询方法：
fetchone()：获取单条记录fetchall()：获取所有记录fetchmany(size)：获取指定数量的记录
2.5 数据更新与删除
conn = sqlite3.connect('students.db')
cur = conn.cursor()
# 更新数据：将 Bob 的成绩修改为 A+
cur.execute(
'UPDATE students SET grade = ? WHERE name = ?',
('A+', 'Bob')
)
# 删除数据：移除 Charlie 的记录
cur.execute(
'DELETE FROM students WHERE name = ?',
('Charlie',)
)
conn.commit()
conn.close()
⚠️ 注意：更新和删除操作后必须调用
commit()提交事务,否则修改不会生效。
2.6 整合版：完整 CRUD 封装
以下是一个结构化的数据库操作模块,将连接、增删改查操作封装为独立函数：
import sqlite3
def create_connection(db_file):
"""创建数据库连接"""
conn = None
try:
conn = sqlite3.connect(db_file)
print("已成功创建数据库连接")
return conn
except sqlite3.Error as e:
print(f"连接错误: {e}")
return None
def create_table(conn):
"""创建 users 表"""
try:
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
age INTEGER
)
''')
except sqlite3.Error as e:
print(f"建表错误: {e}")
def insert_user(conn, data):
"""插入用户数据,避免重复"""
try:
cursor = conn.cursor()
# 先检查用户是否已存在
cursor.execute("SELECT id FROM users WHERE name = ?", (data[0],))
user_id = cursor.fetchone()
if user_id is None:
cursor.execute(
"INSERT INTO users (name, age) VALUES (?, ?)",
data
)
conn.commit()
else:
print(f"用户 {data[0]} 已存在, ID: {user_id[0]}")
except sqlite3.Error as e:
print(f"插入错误: {e}")
def update_user(conn, data):
"""更新用户年龄"""
try:
cursor = conn.cursor()
cursor.execute(
"UPDATE users SET age = ? WHERE name = ?",
data
)
conn.commit()
except sqlite3.Error as e:
print(f"更新错误: {e}")
def delete_user(conn, name):
"""删除指定用户"""
try:
cursor = conn.cursor()
cursor.execute("DELETE FROM users WHERE name = ?", (name,))
conn.commit()
except sqlite3.Error as e:
print(f"删除错误: {e}")
def select_all_users(conn):
"""查询所有用户"""
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
except sqlite3.Error as e:
print(f"查询错误: {e}")
def main():
database = "students.db"
conn = create_connection(database)
if conn is not None:
create_table(conn)
try:
insert_user(conn, ('Alice', 30))
insert_user(conn, ('Bob', 25))
update_user(conn, (35, 'Alice'))
print("查询到的所有用户:")
select_all_users(conn)
delete_user(conn, 'Bob')
print("\n删除后的用户数据:")
select_all_users(conn)
except sqlite3.Error as e:
print(f"操作错误: {e}")
finally:
conn.close()
if __name__ == '__main__':
main()
2.7 面向对象版：DatabaseManager 类
采用面向对象编程思想,将数据库操作封装为类,提升代码的复用性和可维护性：
import sqlite3
class DatabaseManager:
"""SQLite 数据库管理类"""
def __init__(self, db_file):
self.db_file = db_file
self.conn = None
def create_connection(self):
"""创建数据库连接"""
try:
self.conn = sqlite3.connect(self.db_file)
print("已成功创建数据库连接")
except sqlite3.Error as e:
print(f"连接错误: {e}")
def create_table(self):
"""创建用户表"""
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE,
age INTEGER
)
''')
except sqlite3.Error as e:
print(f"建表错误: {e}")
def insert_user(self, name, age):
"""插入新用户"""
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute(
"SELECT id FROM users WHERE name = ?", (name,)
)
if cursor.fetchone() is None:
cursor.execute(
"INSERT INTO users (name, age) VALUES (?, ?)",
(name, age)
)
self.conn.commit()
else:
print(f"用户 {name} 已存在")
except sqlite3.Error as e:
print(f"插入错误: {e}")
def update_user(self, name, age):
"""更新用户年龄"""
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute(
"UPDATE users SET age = ? WHERE name = ?",
(age, name)
)
self.conn.commit()
except sqlite3.Error as e:
print(f"更新错误: {e}")
def delete_user(self, name):
"""删除用户"""
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute(
"DELETE FROM users WHERE name = ?", (name,)
)
self.conn.commit()
except sqlite3.Error as e:
print(f"删除错误: {e}")
def select_all_users(self):
"""查询所有用户"""
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
except sqlite3.Error as e:
print(f"查询错误: {e}")
def close_connection(self):
"""关闭数据库连接"""
if self.conn:
self.conn.close()
print("数据库连接已关闭")
def main():
db_manager = DatabaseManager("students.db")
db_manager.create_connection()
db_manager.create_table()
try:
db_manager.insert_user('Alice', 30)
db_manager.insert_user('Bob', 25)
db_manager.update_user('Alice', 35)
print("查询到的所有用户:")
db_manager.select_all_users()
db_manager.delete_user('Bob')
print("\n删除后的用户数据:")
db_manager.select_all_users()
except sqlite3.Error as e:
print(f"操作错误: {e}")
finally:
db_manager.close_connection()
if __name__ == '__main__':
main()
3. PySide6 操作 SQLite 数据库
3.1 Qt SQL 模块简介
PySide6 提供了 QtSql 模块,其中包含 QSqlDatabase、QSqlTableModel 等类,允许开发者以**模型-视图（Model-View）**架构直接操作数据库,无需编写原始 SQL 语句即可实现数据的增删改查。
3.2 SQL 基本语法回顾
在深入 GUI 开发前,回顾 SQLite 的核心 SQL 语法：
-- 创建表
CREATE TABLE IF NOT EXISTS tb01 (
date TEXT,
book_name TEXT,
express TEXT,
price REAL
);
-- 插入数据
INSERT INTO tb01 VALUES ('2024-1-20', 'book01', 'beijing', 13.45);
-- 批量插入
INSERT INTO tb01 VALUES (?, ?, ?, ?);
-- 查询数据
SELECT * FROM tb01;
SELECT * FROM tb01 WHERE book_name = 'book01';
-- 更新数据
UPDATE tb01 SET book_name = 'book20' WHERE date = '2024-1-20';
3.3 PySide6 与 SQLite 集成实战
步骤一：设计 UI 界面
使用 Qt Designer 创建主窗口,拖入一个 Table View 控件（位于 Item Views (Model-Based) 分组）,用于展示数据库表格数据。
步骤二：实现窗口逻辑类
from PySide6.QtSql import QSqlDatabase, QSqlTableModel
from PySide6.QtWidgets import QMessageBox
import MainWindow # 由 .ui 文件转换的 Python 模块
class MainWindowImpl(MainWindow.Ui_MainWindow):
def __init__(self, window):
super().__init__()
self.setupUi(window)
self.window = window
# 1. 添加 SQLite 数据库驱动
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName('D:/example.db') # 指定数据库文件路径
# 2. 打开数据库连接
if not db.open():
QMessageBox.information(None, '提示', 'SQLite 连接失败')
return
# 3. 创建数据模型并绑定数据表
model = QSqlTableModel()
model.setTable('tb01')
# 4. 设置编辑策略：字段变更时自动同步到数据库
model.setEditStrategy(QSqlTableModel.OnFieldChange)
# 5. 加载数据（必须调用,否则不显示）
model.select()
# 6. 将模型绑定到 TableView
self.tableView.setModel(model)
关键配置说明：
QSQLITE：指定使用 SQLite 数据库驱动setEditStrategy(QSqlTableModel.OnFieldChange)：当用户在表格中修改数据时,自动提交到数据库model.select()：从数据库加载数据到模型,必须显式调用
步骤三：主程序入口
import sys
from PySide6.QtWidgets import QApplication, QMainWindow
import MainWindowImpl
if __name__ == '__main__':
app = QApplication(sys.argv)
mw = QMainWindow()
impl = MainWindowImpl.MainWindowImpl(mw)
mw.show()
sys.exit(app.exec())
运行效果：TableView 会自动加载 tb01 表中的所有数据,并支持直接在界面中编辑、新增和删除记录,所有修改实时同步到 SQLite 数据库文件。
4. 数据库可视化工具推荐
虽然可以通过代码操作数据库,但在开发调试阶段,使用可视化工具查看 .db 文件内容能大幅提升效率：
| 工具 | 平台 | 特点 |
|---|---|---|
| DB Browser for SQLite | 跨平台 | 开源免费,功能全面,支持数据编辑和 SQL 执行 |
| SimpleSQLiteBrowser | IntelliJ/Android Studio | 插件形式,IDE 内直接预览,无需切换窗口 |
| SQLiteStudio | 跨平台 | 专业级工具,支持多数据库管理和可视化查询构建 |
| DBeaver | 跨平台 | 通用数据库工具,支持 SQLite 及多种其他数据库 |
在 IntelliJ IDEA 或 PyCharm 中,安装 SimpleSQLiteBrowser 插件后,双击 .db 文件即可在 IDE 内部预览表结构和数据内容。
5. 总结与最佳实践
核心要点回顾
- SQLite 零配置：Python 内置
sqlite3模块,无需额外安装,直接import使用 - 参数化查询：始终使用
?占位符传递参数,杜绝 SQL 注入风险 - 事务管理：插入、更新、删除后必须调用
commit(),查询操作无需提交 - 资源释放：操作完成后及时调用
close()关闭连接,或使用上下文管理器 - 模型-视图架构：PySide6 的
QSqlTableModel大幅简化了 GUI 与数据库的绑定开发
代码组织建议
| 场景 | 推荐方案 |
|---|---|
| 简单脚本 | 直接使用 sqlite3 模块,函数式封装 |
| 中型项目 | 采用 DatabaseManager 类,统一管理连接和 CRUD |
| GUI 应用 | 使用 PySide6 QtSql 模块,利用模型-视图分离数据与界面 |
| 多线程环境 | 每个线程独立创建连接,避免共享连接对象 |
进阶方向
- 索引优化：为高频查询字段添加索引,提升检索性能
- 连接池：在高并发场景下使用连接池管理数据库连接
- ORM 框架：考虑使用 SQLAlchemy 等 ORM 工具,进一步抽象数据库操作
- 数据迁移：使用 Alembic 等工具管理数据库 Schema 版本
更多推荐
9
0- 0
已为社区贡献5条内容
所有评论(0)