pin_drop当前位置:知识文库 ❯ 图文
Python SQLite数据库操作教程 - sqlite3模块使用指南
一、SQLite概述
SQLite是一个轻量级的嵌入式关系型数据库，它无需独立的服务器进程，整个数据库存储在一个单一的文件中。Python标准库内置了sqlite3模块，使得Python开发者可以方便地进行SQLite数据库操作。
SQLite的核心优势在于其零配置、无服务器、自包含的特性，非常适合小型项目、原型开发、移动应用以及本地数据存储场景。
二、创建数据库连接
使用sqlite3.connect()函数可以创建数据库连接。如果指定的数据库文件不存在，SQLite会自动创建它。
基本连接语法
代码示例
import sqlite3
# 连接到数据库（如果文件不存在则自动创建）
conn = sqlite3.connect('example.db')
# 创建游标对象，用于执行SQL语句
cursor = conn.cursor()
print("数据库连接成功！")
# 操作完成后记得关闭连接
cursor.close()
conn.close()内存数据库
SQLite还支持内存数据库，数据存储在内存中，程序结束后自动消失，适合临时数据处理和测试。
代码示例
import sqlite3
# 创建内存数据库
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# 在内存中创建表和执行操作
cursor.execute('CREATE TABLE temp_data (id INTEGER PRIMARY KEY, value TEXT)')
conn.commit()
# 内存数据库关闭后数据自动销毁
conn.close()小贴士
使用with语句可以自动管理连接的关闭，避免资源泄漏。例如：with sqlite3.connect('example.db') as conn:
三、创建数据表
使用cursor.execute()方法执行SQL的CREATE TABLE语句来创建数据表。
代码示例
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 创建用户表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 创建订单表
cursor.execute('''
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
product_name TEXT NOT NULL,
amount REAL NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
)
''')
conn.commit()
print("数据表创建成功！")
cursor.close()
conn.close()
上述SQL语句中，我们定义了两个表：users表存储用户信息，orders表存储订单信息并通过外键关联到users表。使用IF NOT EXISTS可以避免表已存在时报错。
四、数据增删改查（CRUD）操作
CRUD是指数据库最基本的四种操作：Create（创建/插入）、Read（读取/查询）、Update（更新）、Delete（删除）。
1. 插入数据（Create）
代码示例
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 插入单条数据 - 使用参数化查询防止SQL注入
cursor.execute(
'INSERT INTO users (username, email, age) VALUES (?, ?, ?)',
('张三', 'zhangsan@example.com', 28)
)
# 插入多条数据 - 使用executemany
users_data = [
('李四', 'lisi@example.com', 32),
('王五', 'wangwu@example.com', 25),
('赵六', 'zhaoliu@example.com', 30)
]
cursor.executemany(
'INSERT INTO users (username, email, age) VALUES (?, ?, ?)',
users_data
)
conn.commit()
print(f"成功插入 {cursor.rowcount} 条数据")
cursor.close()
conn.close()2. 查询数据（Read）
代码示例
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 查询所有用户
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
print("所有用户：")
for user in all_users:
print(user)
# 条件查询 - 使用参数化查询
cursor.execute('SELECT * FROM users WHERE age > ?', (26,))
filtered_users = cursor.fetchall()
print("\n年龄大于26岁的用户：")
for user in filtered_users:
print(user)
# 查询单条记录
cursor.execute('SELECT * FROM users WHERE username = ?', ('张三',))
one_user = cursor.fetchone()
print(f"\n查询结果：{one_user}")
# 使用字典方式访问列名
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
row = cursor.fetchone()
print(f"\n用户名：{row['username']}, 邮箱：{row['email']}")
cursor.close()
conn.close()3. 更新数据（Update）
代码示例
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 更新用户年龄
cursor.execute(
'UPDATE users SET age = ? WHERE username = ?',
(29, '张三')
)
print(f"更新了 {cursor.rowcount} 条记录")
# 批量更新
cursor.executemany(
'UPDATE users SET age = age + 1 WHERE username = ?',
[('李四',), ('王五',)]
)
conn.commit()
cursor.close()
conn.close()4. 删除数据（Delete）
代码示例
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 删除指定用户
cursor.execute('DELETE FROM users WHERE username = ?', ('赵六',))
print(f"删除了 {cursor.rowcount} 条记录")
# 删除所有年龄小于25的用户
cursor.execute('DELETE FROM users WHERE age < ?', (25,))
conn.commit()
cursor.close()
conn.close()五、事务处理
事务是数据库操作的基本单元，保证一组操作要么全部成功，要么全部失败。在sqlite3中，conn.commit()提交事务，conn.rollback()回滚事务。
代码示例
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
try:
# 开启事务
cursor.execute('BEGIN')
# 执行多个相关操作
cursor.execute(
'INSERT INTO users (username, email, age) VALUES (?, ?, ?)',
('孙七', 'sunqi@example.com', 35)
)
user_id = cursor.lastrowid
cursor.execute(
'INSERT INTO orders (user_id, product_name, amount) VALUES (?, ?, ?)',
(user_id, 'Python编程入门', 59.9)
)
# 所有操作成功，提交事务
conn.commit()
print("事务提交成功！")
except Exception as e:
# 任何操作失败，回滚事务
conn.rollback()
print(f"事务回滚：{e}")
finally:
cursor.close()
conn.close()
提示：Python 3.12+中，sqlite3默认启用自动提交模式，可以使用conn.autocommit = False显式开启事务控制。
六、完整示例代码
以下是一个完整的SQLite操作示例，包含了数据库连接、表创建、数据插入、查询、更新和删除的完整流程。
代码示例
"""
SQLite完整操作示例 - 图书管理系统
"""
import sqlite3
from contextlib import contextmanager
@contextmanager
def get_db_connection(db_path='library.db'):
"""数据库连接上下文管理器"""
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row # 启用字典式访问
try:
yield conn
finally:
conn.close()
def init_database():
"""初始化数据库，创建数据表"""
with get_db_connection() as conn:
cursor = conn.cursor()
# 创建图书表
cursor.execute('''
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT NOT NULL,
isbn TEXT UNIQUE,
price REAL,
stock INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
print("数据库初始化完成！")
def add_book(title, author, isbn, price, stock):
"""添加图书"""
with get_db_connection() as conn:
cursor = conn.cursor()
try:
cursor.execute(
'INSERT INTO books (title, author, isbn, price, stock) VALUES (?, ?, ?, ?, ?)',
(title, author, isbn, price, stock)
)
conn.commit()
print(f"图书《{title}》添加成功！")
except sqlite3.IntegrityError:
print(f"ISBN {isbn} 已存在，添加失败！")
def search_books(keyword):
"""搜索图书"""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute(
'SELECT * FROM books WHERE title LIKE ? OR author LIKE ?',
(f'%{keyword}%', f'%{keyword}%')
)
books = cursor.fetchall()
print(f"找到 {len(books)} 本图书：")
for book in books:
print(f" ID:{book['id']} 《{book['title']}》 - {book['author']} - ￥{book['price']}")
return books
def update_book_stock(isbn, new_stock):
"""更新图书库存"""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute(
'UPDATE books SET stock = ? WHERE isbn = ?',
(new_stock, isbn)
)
conn.commit()
print(f"ISBN {isbn} 的库存已更新为 {new_stock}")
def delete_book(book_id):
"""删除图书"""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute('DELETE FROM books WHERE id = ?', (book_id,))
conn.commit()
print(f"ID为 {book_id} 的图书已删除")
def show_all_books():
"""显示所有图书"""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM books ORDER BY created_at DESC')
books = cursor.fetchall()
print(f"\n图书列表（共{len(books)}本）：")
print("-" * 60)
for book in books:
print(f" 《{book['title']}》 | {book['author']} | ￥{book['price']} | 库存:{book['stock']}")
print("-" * 60)
if __name__ == '__main__':
# 初始化数据库
init_database()
# 添加图书
add_book('Python编程从入门到实践', '埃里克·马瑟斯', '978-7-115-54543-2', 89.0, 50)
add_book('流畅的Python', '卢西亚诺·拉马略', '978-7-115-51256-5', 129.0, 30)
add_book('数据结构与算法分析', '马克·艾伦·维斯', '978-7-111-60802-4', 99.0, 20)
# 显示所有图书
show_all_books()
# 搜索图书
search_books('Python')
# 更新库存
update_book_stock('978-7-115-54543-2', 45)
# 再次显示
show_all_books()七、注意事项
⚠️ 注意1：始终使用参数化查询
不要使用字符串拼接来构建SQL语句，这会导致SQL注入漏洞。始终使用
?占位符或命名参数。
⚠️ 注意2：及时关闭连接
每次操作完成后都应关闭游标和连接，推荐使用上下文管理器（
with语句）自动管理资源。
⚠️ 注意3：SQLite的并发限制
SQLite同一时间只允许一个写操作，高并发写入场景不适合使用SQLite。建议使用MySQL或PostgreSQL等支持高并发的数据库。
⚠️ 注意4：数据类型
SQLite使用动态类型系统，列声明的类型只是亲和力建议，实际存储时可以是任意类型。建议在应用层做好类型验证。
常见问题
Python中如何查看SQLite数据库文件的内容？
可以使用SQLite自带的命令行工具sqlite3 database.db打开数据库，或使用DB Browser for SQLite等图形化工具查看。在Python中也可以通过cursor.execute()查询数据来查看。
SQLite支持哪些数据类型？
SQLite支持五种存储类：NULL、INTEGER、REAL、TEXT、BLOB。需要注意的是SQLite使用动态类型，列的类型声明只是"类型亲和力"，实际存储时不强制类型匹配。
如何处理SQLite的数据库锁定错误？
可以在连接时设置超时参数：sqlite3.connect('db.sqlite', timeout=10)。同时确保及时关闭连接，避免长时间持有锁。对于高并发场景，建议换用其他数据库。
如何在SQLite中实现分页查询？
使用LIMIT和OFFSET实现分页。例如获取第2页（每页10条）：SELECT * FROM users LIMIT 10 OFFSET 10。OFFSET = (页码-1) × 每页数量。
sqlite3模块需要额外安装吗？
不需要。sqlite3是Python标准库的一部分，从Python 2.5开始就内置了。只需import sqlite3即可使用，无需pip安装。
八、练习题
练习1：学生成绩管理系统
编写一个程序，使用SQLite创建学生成绩管理系统。要求：创建students表（包含id、姓名、班级、语文成绩、数学成绩、英语成绩），实现添加学生、查询学生、计算总分和平均分、按成绩排序等功能。
练习2：简易日记本应用
编写一个函数，实现一个基于SQLite的简易日记本应用。要求：创建diary表（包含id、标题、内容、创建时间、标签），支持添加日记、按日期范围查询、按标签搜索、删除日记等功能，并使用事务保证操作的原子性。
小结
-
sqlite3模块：Python标准库内置，无需额外安装，通过sqlite3.connect()创建连接
-
CRUD操作：使用cursor.execute()执行INSERT、SELECT、UPDATE、DELETE语句完成增删改查
-
安全最佳实践：始终使用参数化查询（?占位符）防止SQL注入，及时关闭连接避免资源泄漏
-
事务处理：使用conn.commit()提交事务，conn.rollback()回滚事务，保证数据一致性
本文涉及AI创作
内容由AI创作，请仔细甄别
