从零开始学习 Python 3 - 数据库交互 2：解锁更多 SQLite 操作，玩转数据库！

• 公众号：人生只不过是一场投资
• 温馨提示：每个笔记文档都会绑定一个笔记资源；手机端可能看不到，网页端请自行下载。

还记得上节课我们学习了如何用 Python 操作 SQLite 数据库吗？
这次，我们将继续深入探索，解锁更多实用操作，让你像数据库大师一样游刃有余！

前言

上一节课我们学习了 Python 操作 SQLite 数据库的基础知识，包括连接数据库、创建数据表、插入、查询、更新和删除数据等操作。

这次，我们将学习一些更高级的操作，例如：

• 使用 SQL 语句参数化，防止 SQL 注入攻击。
• 使用事务 (Transaction) 保证数据一致性。
• 使用 Python 的数据库 API 简化数据库操作。

一、 SQL 语句参数化：安全第一！

想象一下，如果用户的输入直接拼接到 SQL 语句中，会发生什么？

#  不安全的做法：
user_input = "'; DROP TABLE students; --"  #  恶意代码
cursor.execute(f"SELECT * FROM students WHERE name = '{user_input}'")  

😱 灾难现场！ 恶意用户可以利用 SQL 注入攻击，删除你的数据表！

为了避免这种情况，我们需要使用 SQL 语句参数化：

#  安全的做法：
user_input = "'; DROP TABLE students; --"  #  即使是恶意代码，也无法造成破坏
cursor.execute("SELECT * FROM students WHERE name = ?", (user_input,)) 

使用 ? 作为占位符，将用户输入作为参数传递给 execute() 方法，数据库会将其视为数据，而不是 SQL 代码的一部分，从而防止 SQL 注入攻击。

二、 事务 (Transaction)：保证数据一致性

假设你需要将 100 元从账户 A 转账到账户 B，这个操作需要执行两个 SQL 语句：

1. 从账户 A 扣除 100 元。
2. 向账户 B 增加 100 元。

如果在执行这两个语句的过程中发生错误，例如网络中断，就会导致数据不一致，要么是账户 A 钱少了，要么是账户 B 钱多了。

为了避免这种情况，我们需要使用 事务：

import sqlite3

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

try:
    #  开启事务
    cursor.execute("BEGIN TRANSACTION")

    #  执行 SQL 语句
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")

    #  提交事务
    conn.commit()
    print("转账成功！")

except Exception as e:
    #  发生错误，回滚事务
    conn.rollback()
    print("转账失败！", e)

finally:
    conn.close()

事务的特性 (ACID):

• 原子性 (Atomicity): 事务中的所有操作要么全部成功，要么全部失败。
• 一致性 (Consistency): 数据库从一个一致性状态变换到另一个一致性状态。
• 隔离性 (Isolation): 多个事务并发执行时，不会相互影响。
• 持久性 (Durability): 事务完成后，对数据库的修改是永久性的。

三、 Python 的数据库 API：更便捷的操作

Python 提供了数据库 API (Python Database API Specification v2.0)，可以让我们使用统一的接口操作不同的数据库。

import sqlite3

#  连接数据库
conn = sqlite3.connect('mydatabase.db')

#  创建游标
cursor = conn.cursor()

#  执行 SQL 语句
cursor.execute("SELECT * FROM students")

#  获取查询结果
#  fetchone(): 获取下一行结果
#  fetchall(): 获取所有结果
#  fetchmany(size): 获取指定数量的结果
rows = cursor.fetchall()

#  遍历结果集
for row in rows:
    print(row)

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

总结

• 使用 SQL 语句参数化，防止 SQL 注入攻击，确保数据安全。
• 使用事务保证数据一致性，避免数据错误。
• 使用 Python 的数据库 API 可以更方便地操作数据库。

掌握了这些技巧，你就离数据库大师更近了一步！ 继续加油吧！