第122集 SQLite数据库操作

一、SQLite简介

1.1 什么是SQLite

SQLite是一款轻量级的嵌入式关系型数据库管理系统，它不需要独立的服务器进程，而是直接读写普通磁盘文件。SQLite是世界上使用最广泛的数据库引擎之一，被应用于各种设备和平台。

1.2 SQLite的特点

  • 轻量级：整个SQLite库只有一个文件，体积不到1MB
  • 无需安装：不需要复杂的安装和配置过程
  • 零配置：不需要设置用户名、密码等
  • 跨平台：支持Windows、Linux、macOS等所有主流操作系统
  • ACID兼容：支持事务处理，保证数据的原子性、一致性、隔离性和持久性
  • 支持标准SQL：可以使用大部分标准SQL语法
  • Python内置支持：Python的标准库中包含sqlite3模块，无需额外安装

1.3 SQLite的应用场景

  • 移动应用（Android、iOS）
  • 嵌入式设备
  • 小型Web应用
  • 桌面应用
  • 数据分析和原型开发
  • 需要本地数据存储的场景

二、Python中使用SQLite

2.1 sqlite3模块

Python标准库中的sqlite3模块提供了与SQLite数据库交互的接口。使用该模块，我们可以轻松地在Python程序中创建、查询和管理SQLite数据库。

2.2 数据库连接

在使用SQLite之前，我们需要先建立与数据库的连接。如果指定的数据库文件不存在，SQLite会自动创建它。

import sqlite3

# 连接到SQLite数据库
# 如果数据库文件不存在，会自动在当前目录创建
conn = sqlite3.connect('test.db')

# 关闭数据库连接
conn.close()

2.3 游标对象

连接数据库后，我们需要创建一个游标对象来执行SQL语句：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 使用游标执行SQL语句
# ...

# 关闭游标和连接
cursor.close()
conn.close()

三、SQLite基本操作

3.1 创建表

使用CREATE TABLE语句创建表：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 创建user表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)
''')

# 提交事务
conn.commit()

cursor.close()
conn.close()

3.2 插入数据

使用INSERT INTO语句插入数据：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 插入单条数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", ('张三', 25, 'zhangsan@example.com'))

# 插入多条数据
users = [
    ('李四', 30, 'lisi@example.com'),
    ('王五', 28, 'wangwu@example.com')
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users)

# 提交事务
conn.commit()

# 获取最后插入的ID
print(f"最后插入的ID: {cursor.lastrowid}")

cursor.close()
conn.close()

3.3 查询数据

使用SELECT语句查询数据：

import sqlite3

conn = sqlite3.connect('test.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 > 25")
first_user = cursor.fetchone()
print(f"\n年龄大于25的第一个用户: {first_user}")

# 查询前N条数据
cursor.execute("SELECT * FROM users ORDER BY age DESC")
top_users = cursor.fetchmany(2)
print("\n年龄最大的两个用户:")
for user in top_users:
    print(user)

cursor.close()
conn.close()

3.4 更新数据

使用UPDATE语句更新数据：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 更新数据
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, '张三'))

# 提交事务
conn.commit()

# 查看更新结果
cursor.execute("SELECT * FROM users WHERE name = '张三'")
updated_user = cursor.fetchone()
print(f"更新后的用户信息: {updated_user}")

cursor.close()
conn.close()

3.5 删除数据

使用DELETE语句删除数据：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 删除数据
cursor.execute("DELETE FROM users WHERE age < ?", (28,))

# 提交事务
conn.commit()

# 查看剩余数据
cursor.execute("SELECT * FROM users")
remaining_users = cursor.fetchall()
print("剩余用户:")
for user in remaining_users:
    print(user)

cursor.close()
conn.close()

四、事务处理

4.1 什么是事务

事务是指作为单个逻辑工作单元执行的一系列操作，这些操作要么全部成功，要么全部失败。

4.2 事务的ACID特性

  • **原子性(Atomicity)**：事务中的所有操作要么全部完成，要么全部不完成
  • **一致性(Consistency)**：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态
  • **隔离性(Isolation)**：多个事务并发执行时，一个事务的执行不应影响其他事务
  • **持久性(Durability)**：事务一旦提交，其结果应该永久保存在数据库中

4.3 在SQLite中使用事务

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

try:
    # 开始事务（默认已经开始）
    
    # 执行操作1
    cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", ('赵六', 35, 'zhaoliu@example.com'))
    
    # 执行操作2
    cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, '李四'))
    
    # 提交事务
    conn.commit()
    print("事务执行成功")
    
except Exception as e:
    # 回滚事务
    conn.rollback()
    print(f"事务执行失败: {e}")

finally:
    cursor.close()
    conn.close()

五、高级操作

5.1 使用上下文管理器

使用with语句可以自动管理数据库连接和游标：

import sqlite3

# 使用上下文管理器管理连接
with sqlite3.connect('test.db') as conn:
    # 使用上下文管理器管理游标
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        users = cursor.fetchall()
        print(users)
# 自动关闭游标和连接

5.2 列名访问

默认情况下，查询结果返回的是元组，我们可以使用row_factory属性使查询结果返回字典：

import sqlite3

conn = sqlite3.connect('test.db')

# 设置row_factory为sqlite3.Row，使查询结果返回字典
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()

for user in users:
    # 可以通过列名访问
    print(f"ID: {user['id']}, 姓名: {user['name']}, 年龄: {user['age']}")

cursor.close()
conn.close()

5.3 执行脚本

使用executescript方法可以执行包含多个SQL语句的脚本：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 执行脚本
cursor.executescript('''
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL
);

INSERT INTO products (name, price) VALUES ('苹果', 5.5);
INSERT INTO products (name, price) VALUES ('香蕉', 3.0);
INSERT INTO products (name, price) VALUES ('橙子', 4.0);
''')

conn.commit()

# 查询产品
cursor.execute("SELECT * FROM products")
products = cursor.fetchall()
print("产品列表:")
for product in products:
    print(product)

cursor.close()
conn.close()

六、常见错误和注意事项

6.1 常见错误

  1. 数据库文件路径错误：确保提供的数据库文件路径正确
  2. SQL语法错误：检查SQL语句的语法是否正确
  3. 数据类型不匹配：确保插入的数据类型与表定义一致
  4. 主键冲突：避免插入重复的主键值
  5. 唯一约束冲突：避免插入重复的唯一约束字段值
  6. 外键约束错误：确保外键引用的记录存在

6.2 注意事项

  1. 关闭连接：使用完毕后及时关闭数据库连接
  2. 提交事务：对数据库的修改需要显式提交
  3. 使用参数化查询：避免SQL注入攻击
  4. 备份数据库：定期备份SQLite数据库文件
  5. 性能考虑：对于大量数据操作，考虑使用事务和批量操作
  6. 并发访问：SQLite支持并发读取，但写入操作是串行的

七、总结

本集我们学习了SQLite数据库的基本操作，包括：

  1. SQLite的特点和应用场景
  2. Python中sqlite3模块的使用
  3. 数据库连接和关闭
  4. 表的创建和管理
  5. 数据的增删改查操作
  6. 事务处理
  7. 高级操作和最佳实践

SQLite是一款轻量级的数据库，非常适合初学者学习数据库操作，也适合在实际项目中用于本地数据存储。在下一集中，我们将学习SQL的基础语法，进一步掌握数据库操作技能。

八、练习

  1. 创建一个学生表，包含id、name、gender、age、class等字段
  2. 向表中插入5条学生数据
  3. 查询所有学生的信息
  4. 查询年龄大于18的学生
  5. 更新某个学生的年龄
  6. 删除某个学生的信息
  7. 使用事务同时插入多个学生数据
  8. 使用上下文管理器管理数据库连接
« 上一篇 数据库基础概念 下一篇 » SQL基础语法
↑