SQLite 与 Python – 详细教程

SQLite 是 Python 内置支持的数据库，适用于小型应用、桌面软件和数据存储。本文将提供更详细的示例，包括连接数据库、执行 SQL 语句、处理异常、使用 ORM（SQLAlchemy）等。

---

1. 安装 SQLite 和 Python

SQLite 是 Python 标准库的一部分，默认已安装。你可以运行以下命令检查：

python -c "import sqlite3; print(sqlite3.version)"

如果输出 SQLite 版本号，表示 sqlite3 模块已安装。

---

2. 连接 SQLite 数据库

import sqlite3

# 连接数据库（如果文件不存在，会自动创建）
conn = sqlite3.connect("test.db")

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

print("成功连接 SQLite 数据库")

SQLite 数据库是一个文件，例如 test.db，可以随意移动。

---

3. 创建表

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
""")
conn.commit()
print("表创建成功")

AUTOINCREMENT 让 id 自动递增，NOT NULL 表示字段不能为空。

---

4. 插入数据

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 30))
conn.commit()
print("数据插入成功")

使用 ? 作为占位符，防止 SQL 注入。

---

5. 查询数据

查询所有数据

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()  # 获取所有结果

for row in rows:
    print(f"ID: {row[0]}, 姓名: {row[1]}, 年龄: {row[2]}")

fetchall() 获取所有行，结果是一个列表。

查询特定条件的数据

cursor.execute("SELECT * FROM users WHERE age > ?", (26,))
rows = cursor.fetchall()

for row in rows:
    print(f"符合条件的用户: {row}")

---

6. 更新数据

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (28, "Alice"))
conn.commit()
print("数据更新成功")

UPDATE users SET age = 28 WHERE name = 'Alice'。

---

7. 删除数据

cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))
conn.commit()
print("数据删除成功")

DELETE FROM users WHERE name = 'Bob'。

---

8. 使用事务

try:
    conn.execute("BEGIN")  # 开始事务
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Eve", 22))
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("David", 35))
    conn.commit()  # 提交事务
    print("事务提交成功")
except Exception as e:
    conn.rollback()  # 事务回滚
    print("事务回滚:", e)

事务用于确保多条 SQL 语句要么全部成功，要么全部失败。

---

9. 关闭数据库

cursor.close()
conn.close()
print("数据库连接已关闭")

关闭数据库连接，避免资源占用。

---

10. 使用 with 语句自动管理连接

with sqlite3.connect("test.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    for row in cursor.fetchall():
        print(row)

with 语句可以自动关闭数据库连接。

---

11. 使用 Pandas 读取 SQLite 数据

如果你要分析数据，可以结合 pandas 直接读取 SQLite 数据：

import pandas as pd

conn = sqlite3.connect("test.db")
df = pd.read_sql_query("SELECT * FROM users", conn)
conn.close()

print(df)

适用于数据分析和可视化。

---

12. 使用 SQLAlchemy（ORM 操作 SQLite）

SQLAlchemy 是 Python 强大的 ORM（对象关系映射）库，可以用 Python 代码操作数据库，而不需要直接写 SQL 语句。

安装 SQLAlchemy

pip install sqlalchemy

使用 ORM 操作 SQLite

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# 创建数据库连接
engine = create_engine("sqlite:///test.db", echo=True)
Base = declarative_base()

# 定义表结构
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    age = Column(Integer, nullable=False)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
new_user = User(name="Charlie", age=27)
session.add(new_user)
session.commit()

# 查询数据
users = session.query(User).all()
for user in users:
    print(f"ID: {user.id}, 姓名: {user.name}, 年龄: {user.age}")

# 关闭会话
session.close()

通过 ORM，可以避免写 SQL 语句，提高代码可读性。

---

13. SQLite 高级特性

13.1. 创建索引

cursor.execute("CREATE INDEX IF NOT EXISTS idx_name ON users(name)")
conn.commit()
print("索引创建成功")

13.2. 启用 WAL 模式

cursor.execute("PRAGMA journal_mode=WAL;")
print("WAL 模式启用")

13.3. 只读模式

conn_ro = sqlite3.connect("file:test.db?mode=ro", uri=True)
print("数据库只读模式")

---

总结

操作	SQL 语句	Python 代码
连接数据库	sqlite3.connect("test.db")	sqlite3.connect(...)
创建表	CREATE TABLE ...	cursor.execute(...)
插入数据	INSERT INTO users (name, age) VALUES (?, ?)	cursor.execute(...)
查询数据	SELECT * FROM users	cursor.fetchall()
更新数据	UPDATE users SET age = ? WHERE name = ?	cursor.execute(...)
删除数据	DELETE FROM users WHERE name = ?	cursor.execute(...)
事务	BEGIN TRANSACTION	conn.execute("BEGIN")

---

适用场景

• 本地数据存储（如桌面软件、日志管理）
• 小型 Web 应用（如 Flask/Django）
• 移动应用数据库（如 Android 本地存储）
• 数据分析（结合 Pandas）

如果你的数据量较大，建议使用 MySQL 或 PostgreSQL，但 SQLite 依然适用于小型应用和测试环境。

更多详细内容请关注其他相关文章。