第三十二章：SQLite数据库

完成本章学习后，你将能够：

使用sqlite3模块
执行SQL语句
处理事务
防止SQL注入

import sqlite3
 
# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
 
# 创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
''')
 
# 插入数据
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)",
               ("Alice", "alice@example.com"))
conn.commit()
 
# 查询
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)
 
conn.close()

import sqlite3
 
# 使用上下文管理器
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())
    # 自动提交/回滚

import sqlite3
 
conn = sqlite3.connect('example.db')
 
# 安全的参数化查询（防止SQL注入）
name = "Alice'; DROP TABLE users; --"
conn.execute("SELECT * FROM users WHERE name = ?", (name,))
 
# 批量插入
users = [("Bob", "bob@example.com"), ("Charlie", "charlie@example.com")]
conn.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)
conn.commit()