DevNull 知识库

Appearance

31｜SQL 基础与 SQLite ​

接口需要持久化存储数据——进程重启后数据不能丢失。数据库是专门做这个事的软件。这篇从最基础的关系型数据库概念开始，用 SQLite 作为学习环境：它内置于 Python，不需要安装服务端，所有数据存在一个文件中。

一、关系型数据库基础 ​

关系型数据库用表组织数据。一张表由行（记录）和列（字段）组成：

idhostnameipstatus
1web-01192.168.1.10running
2web-02192.168.1.11stopped
  • 表（table）：数据的容器，如 servers
  • 行（row）：一条记录，如 web-01 的所有信息
  • 列（column）：一个字段，如 hostname、status
  • 主键（primary key）：唯一标识一行，通常用自增整数 id

二、SQLite 连接 ​

Python 标准库自带 sqlite3 模块：

python
import sqlite3

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

# 创建游标，用它执行 SQL
cursor = conn.cursor()
对象作用
Connection数据库连接，管理事务
Cursor执行 SQL、获取结果

三、建表 ​

python
cursor.execute("""
    CREATE TABLE IF NOT EXISTS servers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        hostname TEXT NOT NULL,
        ip TEXT NOT NULL,
        status TEXT DEFAULT 'running',
        created_at TEXT DEFAULT (datetime('now'))
    )
""")

conn.commit()
类型说明
INTEGER整数
TEXT文本
REAL浮点数
BLOB二进制数据
约束说明
PRIMARY KEY主键，唯一标识一行
AUTOINCREMENT自动递增
NOT NULL不允许为空
DEFAULT默认值

IF NOT EXISTS 防止表已存在时报错。建表后必须 commit() 提交事务，否则表不会真正创建。

四、SQLite 数据类型特点 ​

SQLite 是动态类型的——字段声明为 TEXT，但插入整数也不会报错（会存成 TEXT）。这和 PostgreSQL、MySQL 的严格类型不同。学习阶段用 SQLite 方便，但生产环境要注意类型不严格的隐患。

五、常用操作 ​

查看表结构 ​

sql
.schema servers

删除表 ​

python
cursor.execute("DROP TABLE IF EXISTS servers")
conn.commit()

添加列 ​

python
cursor.execute("ALTER TABLE servers ADD COLUMN env TEXT DEFAULT 'dev'")
conn.commit()

SQLite 的 ALTER TABLE 功能有限，不能删除列或修改列类型。复杂变更需要创建新表、迁移数据、删除旧表、重命名新表。

六、连接管理 ​

python
import sqlite3
from contextlib import contextmanager

@contextmanager
def get_db():
    conn = sqlite3.connect("ops.db")
    conn.row_factory = sqlite3.Row   # 让查询结果支持列名访问
    try:
        yield conn
    finally:
        conn.close()

with get_db() as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM servers")
    rows = cursor.fetchall()

sqlite3.Row 让结果可以通过列名访问：row["hostname"] 而不是 row[1]。

七、常见错误 ​

忘记 commit ​

python
# 错误：没有 commit，表/数据不会持久化
cursor.execute("CREATE TABLE test (id INTEGER)")
# conn.commit()  # 忘记这行

# 正确
cursor.execute("CREATE TABLE test (id INTEGER)")
conn.commit()

连接未关闭 ​

python
# 错误：反复连接不关闭，文件句柄耗尽
for i in range(10000):
    conn = sqlite3.connect("ops.db")   # 不 close

# 正确：用 with 语句或确保 close
with sqlite3.connect("ops.db") as conn:
    ...

并发写入冲突 ​

SQLite 同一时间只允许一个写入操作。多线程/多进程同时写入会抛出 OperationalError: database is locked。FastAPI 的并发请求如果都直接写 SQLite，容易触发这个错误。解决方案：用连接池限制并发，或生产环境换 PostgreSQL/MySQL。