4. 用户表创建与操作：用 Python 的 sqlite3 模块玩转数据
好，咱们进入正题。上一章我们把数据库环境搭好了，现在该动手了。这一章我会带你用 Python 的 sqlite3 模块，把用户表建起来，然后往里塞数据、查数据、改数据、删数据。说白了，就是数据库的增删改查（CRUD）。
我个人习惯，在写任何数据库操作之前，先把表结构想清楚。你想想看，表结构就像房子的地基，地基歪了，后面装修再漂亮也白搭。
4.1 创建用户表：先搭好架子
我们先创建一个用户表，用来存药盒用户的信息。这张表我设计成包含用户ID、用户名、密码哈希、邮箱、注册时间这几个字段。
嗯，这里要注意：密码绝对不能明文存！我在项目中遇到过有人把密码直接存成字符串，结果数据库一泄露，用户全遭殃。所以我们用哈希值。
import sqlite3
import hashlib
# 连接数据库（如果文件不存在会自动创建）
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
# 创建用户表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
email TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
print("用户表创建成功！")
conn.close()
小提示：
IF NOT EXISTS 是个好东西。我第一次写 SQLite 时没加这个，结果脚本跑了两次就报错。加上它，表存在时就不重复创建，省心。
这段代码干了什么？说白了就是：
- 连接数据库文件
medicine_box.db - 创建一张叫
users的表 - 字段包括 id（自增主键）、username（唯一）、password_hash、email、created_at（自动填当前时间）
- 提交事务，关闭连接
4.2 插入测试数据：往表里塞点东西
表建好了，空荡荡的。我们插几条测试数据进去。我建议你至少插 3-5 条，后面查询时才有东西可看。
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
# 准备几条测试数据
test_users = [
('alice', hashlib.sha256('pass123'.encode()).hexdigest(), 'alice@example.com'),
('bob', hashlib.sha256('bob2024'.encode()).hexdigest(), 'bob@test.com'),
('charlie', hashlib.sha256('charlie!@#'.encode()).hexdigest(), 'charlie@demo.org')
]
# 批量插入
cursor.executemany('''
INSERT INTO users (username, password_hash, email)
VALUES (?, ?, ?)
''', test_users)
conn.commit()
print(f"成功插入 {cursor.rowcount} 条数据")
conn.close()
注意： 这里用
? 作为占位符，而不是直接拼接字符串。我曾经见过有人用 f-string 拼 SQL，结果被 SQL 注入攻击了。用参数化查询，这是底线。
你可能会问：为什么用 executemany 而不是一条条 execute？我告诉你，批量操作效率高得多。如果插 1000 条数据，一条条插要来回 1000 次网络交互，而 executemany 一次搞定。
4.3 查询数据：把数据捞出来看看
数据插进去了，怎么查？最基础的是查全部，但实际项目中很少这么干。我一般会加条件过滤。
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
# 查询所有用户
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
print("=== 所有用户 ===")
for user in all_users:
print(f"ID: {user[0]}, 用户名: {user[1]}, 邮箱: {user[3]}, 注册时间: {user[4]}")
# 按条件查询：找邮箱是 example.com 结尾的用户
cursor.execute('SELECT * FROM users WHERE email LIKE ?', ('%@example.com',))
filtered_users = cursor.fetchall()
print("\n=== 邮箱为 example.com 的用户 ===")
for user in filtered_users:
print(f"用户名: {user[1]}, 邮箱: {user[3]}")
conn.close()
这里有个细节：fetchall() 返回的是列表，每条记录是一个元组。如果你只想取一条，用 fetchone()。我刚开始学的时候老搞混，后来记住了一个口诀：all 是全部，one 是一条。
| 方法 | 返回值 | 适用场景 |
|---|---|---|
| fetchone() | 单个元组或 None | 查唯一记录（如按ID查） |
| fetchall() | 列表（可能为空） | 查多条记录 |
| fetchmany(n) | 最多 n 条记录的列表 | 分页查询 |
4.4 更新数据：改错了怎么办？
用户信息不是一成不变的。比如用户改了邮箱，或者重置了密码。这时候就要用 UPDATE。
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
# 更新 alice 的邮箱
cursor.execute('''
UPDATE users
SET email = ?
WHERE username = ?
''', ('alice_new@example.com', 'alice'))
if cursor.rowcount > 0:
print(f"更新成功，影响 {cursor.rowcount} 行")
else:
print("没有找到匹配的用户")
conn.commit()
conn.close()
关键点： 更新时一定要带 WHERE 条件！我犯过最蠢的错误就是忘了写 WHERE，结果整个表的邮箱全改成一样的了。那场面，简直灾难。
另外，cursor.rowcount 可以告诉你影响了多少行。如果返回 0，说明条件没匹配到任何记录。这个在调试时很有用。
4.5 删除数据：清理不需要的记录
删除操作要格外小心。我一般会在删除前先查一遍，确认要删的是对的。
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
# 先确认要删除的用户
cursor.execute('SELECT * FROM users WHERE username = ?', ('charlie',))
user = cursor.fetchone()
if user:
print(f"即将删除用户: {user[1]}, 邮箱: {user[3]}")
confirm = input("确认删除？(y/n): ")
if confirm.lower() == 'y':
cursor.execute('DELETE FROM users WHERE username = ?', ('charlie',))
conn.commit()
print("删除成功")
else:
print("取消删除")
else:
print("用户不存在")
conn.close()
警告： 生产环境中，我建议不要物理删除数据。更好的做法是加一个
is_deleted 字段，标记为已删除。这样数据还能恢复，也方便审计。这叫「软删除」。
4.6 完整示例：一个简单的用户管理脚本
把上面这些串起来，就是一个简单的用户管理工具。我写了个小脚本，你可以直接跑跑看。
import sqlite3
import hashlib
def init_db():
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
email TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
conn.close()
def add_user(username, password, email):
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
password_hash = hashlib.sha256(password.encode()).hexdigest()
try:
cursor.execute('''
INSERT INTO users (username, password_hash, email)
VALUES (?, ?, ?)
''', (username, password_hash, email))
conn.commit()
print(f"用户 {username} 添加成功")
except sqlite3.IntegrityError:
print(f"用户名 {username} 已存在")
finally:
conn.close()
def list_users():
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
cursor.execute('SELECT id, username, email, created_at FROM users')
for user in cursor.fetchall():
print(f"ID:{user[0]} | {user[1]} | {user[2]} | {user[3]}")
conn.close()
def delete_user(username):
conn = sqlite3.connect('medicine_box.db')
cursor = conn.cursor()
cursor.execute('DELETE FROM users WHERE username = ?', (username,))
if cursor.rowcount > 0:
conn.commit()
print(f"用户 {username} 已删除")
else:
print(f"用户 {username} 不存在")
conn.close()
if __name__ == '__main__':
init_db()
add_user('test1', '123456', 'test1@mail.com')
add_user('test2', 'abcdef', 'test2@mail.com')
print("\n当前用户列表：")
list_users()
delete_user('test1')
print("\n删除后用户列表：")
list_users()
这个脚本虽然简单，但已经包含了我们这一章讲的所有操作。你可以在自己电脑上跑一下，看看效果。
我的经验： 刚开始学数据库时，别急着写复杂的查询。先把增删改查这四个基本操作练熟，每个操作都亲手敲一遍代码。我当年就是这么过来的，基础打牢了，后面学 JOIN、子查询什么的就顺了。
好了，这一章就到这里。下一章我们会讲如何把日志数据也存进数据库，到时候两张表之间还要建立关联。做好准备！