SQLite 嵌入式数据库¶

SQLite 是一个嵌入式的轻量级关系型数据库，不需要安装服务器，整个数据库就是一个文件，Python 标准库自带支持，特别适合小型项目、本地数据存储和快速原型开发。

核心知识点¶

知识点	说明
数据库类型	嵌入式关系型数据库（无服务器）
最新版本	SQLite 3.47+（持续更新）
核心特点	零配置、单文件存储、跨平台、Python 内置
适用场景	本地应用、原型开发、数据分析、测试、移动端
不适用场景	高并发写入、大规模 Web 应用（用 PostgreSQL）
数据大小	单数据库最大 281 TB
Python 支持	标准库 sqlite3 模块，无需额外安装

安装配置¶

Python 内置（无需安装）¶

import sqlite3  # Python 标准库自带，直接导入即可
print(sqlite3.sqlite_version)  # 查看 SQLite 版本

命令行工具安装¶

# Ubuntu/Debian
sudo apt install sqlite3             # 安装命令行工具

# macOS（自带）
sqlite3 --version                     # 查看版本

# Conda
conda install -c conda-forge sqlite  # 安装

基本使用¶

1. 命令行操作¶

# 创建/打开数据库
sqlite3 bioinfo.db                    # 打开（不存在则创建）

# SQLite 命令（以 . 开头的是 SQLite 特有命令）
.databases                            # 查看已打开的数据库
.tables                               # 列出所有表
.schema samples                       # 查看表结构
.mode column                          # 列对齐显示
.headers on                           # 显示列名
.quit                                 # 退出

2. Python 基本操作¶

import sqlite3  # 导入 sqlite3 模块

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

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS samples (
        id INTEGER PRIMARY KEY AUTOINCREMENT,   -- 自增主键
        sample_id TEXT UNIQUE NOT NULL,         -- 样本 ID
        diagnosis TEXT NOT NULL,                -- 诊断
        bmi REAL,                              -- BMI（浮点数）
        created_at TEXT DEFAULT (datetime('now'))  -- 创建时间
    )
""")

# 插入数据
cursor.execute(
    "INSERT INTO samples (sample_id, diagnosis, bmi) VALUES (?, ?, ?)",
    ("T2D_001", "T2D", 28.5)           # 用 ? 占位符防止 SQL 注入
)

# 批量插入
data = [
    ("T2D_002", "T2D", 30.1),
    ("HC_001", "Healthy", 22.0),
    ("HC_002", "Healthy", 21.5),
]
cursor.executemany(
    "INSERT INTO samples (sample_id, diagnosis, bmi) VALUES (?, ?, ?)",
    data                                # 批量插入列表
)

conn.commit()  # 提交事务（重要！不提交数据会丢失）

# 查询
cursor.execute("SELECT * FROM samples WHERE diagnosis = ?", ("T2D",))
for row in cursor.fetchall():          # 获取所有结果
    print(row)

# 关闭
conn.close()

3. 与 Pandas 结合（生信数据分析利器）¶

import pandas as pd  # 导入 pandas
import sqlite3       # 导入 sqlite3

conn = sqlite3.connect("bioinfo.db")

# 从 CSV 导入到 SQLite
df = pd.read_csv("abundance_table.csv")  # 读取 CSV
df.to_sql("abundance", conn, if_exists="replace", index=False)  # 写入数据库

# 用 SQL 查询，直接返回 DataFrame
result = pd.read_sql_query("""
    SELECT sample_id, diagnosis, bmi
    FROM samples
    WHERE bmi > 25
    ORDER BY bmi DESC
""", conn)  # SQL 查询结果直接变 DataFrame
print(result)

# 复杂查询也没问题
result = pd.read_sql_query("""
    SELECT s.sample_id, s.diagnosis, a.Bacteroides
    FROM samples s
    JOIN abundance a ON s.sample_id = a.sample_id
    WHERE s.diagnosis = 'T2D'
""", conn)

conn.close()

高级用法¶

1. 内存数据库（极速临时分析）¶

import sqlite3

# 内存数据库（关闭连接后数据消失，但速度极快）
conn = sqlite3.connect(":memory:")  # 使用 :memory: 创建内存数据库
cursor = conn.cursor()

# 适合临时数据处理
cursor.execute("CREATE TABLE temp_data (id INTEGER, value REAL)")
cursor.executemany("INSERT INTO temp_data VALUES (?, ?)",
    [(i, i * 0.5) for i in range(100000)])  # 插入 10 万行

cursor.execute("SELECT AVG(value), MAX(value) FROM temp_data")
print(cursor.fetchone())  # 瞬间完成

conn.close()  # 关闭后数据自动消失

2. 使用上下文管理器（自动提交/回滚）¶

import sqlite3

# 推荐用 with 语句，自动处理提交和回滚
with sqlite3.connect("bioinfo.db") as conn:
    conn.execute("INSERT INTO samples (sample_id, diagnosis) VALUES (?, ?)",
                 ("T2D_010", "T2D"))
    # with 块结束时自动 commit
    # 如果出错会自动 rollback

3. 自定义函数¶

import sqlite3
import math

conn = sqlite3.connect("bioinfo.db")

# 注册自定义函数（SQLite 原生不支持的功能）
conn.create_function("log2", 1, lambda x: math.log2(x) if x > 0 else None)

# 现在可以在 SQL 中使用 log2
cursor = conn.cursor()
cursor.execute("SELECT sample_id, log2(bmi) FROM samples")
for row in cursor.fetchall():
    print(row)

conn.close()

4. 全文搜索（FTS5）¶

-- 创建全文搜索表
CREATE VIRTUAL TABLE notes_fts USING fts5(
    title,       -- 标题
    content,     -- 内容
    tags         -- 标签
);

-- 插入数据
INSERT INTO notes_fts VALUES (
    'T2D 肠道菌群研究',
    '2型糖尿病患者的肠道菌群多样性显著降低',
    'T2D microbiome diversity'
);

-- 全文搜索
SELECT * FROM notes_fts WHERE notes_fts MATCH 'microbiome';     -- 搜索关键词
SELECT * FROM notes_fts WHERE notes_fts MATCH 'T2D AND diversity';  -- 组合搜索

5. 数据库备份和导出¶

import sqlite3

# 备份到另一个文件
source = sqlite3.connect("bioinfo.db")
backup = sqlite3.connect("bioinfo_backup.db")
source.backup(backup)  # 一行代码完成备份
backup.close()
source.close()

# 命令行导出
sqlite3 bioinfo.db .dump > backup.sql       # 导出为 SQL
sqlite3 bioinfo.db ".mode csv" ".headers on" "SELECT * FROM samples;" > samples.csv  # 导出 CSV

常见报错与解决¶

报错信息	原因	解决方法
`database is locked`	多个进程同时写入	用 `timeout` 参数或改用 WAL 模式
`UNIQUE constraint failed`	唯一键重复	用 `INSERT OR REPLACE` 或 `INSERT OR IGNORE`
`no such table`	表不存在	检查表名，用 `.tables` 确认
`OperationalError: unable to open database`	文件路径错或权限不够	检查路径和文件权限
数据丢失	忘了 `conn.commit()`	务必在写操作后提交，或用 `with` 语句
`disk I/O error`	磁盘满或文件系统错误	检查磁盘空间

速查表¶

# ===== SQLite 速查表 =====

import sqlite3
import pandas as pd

# === 连接 ===
conn = sqlite3.connect("data.db")     # 文件数据库
conn = sqlite3.connect(":memory:")    # 内存数据库

# === 基本操作 ===
cursor = conn.cursor()
cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
cursor.execute("INSERT INTO t (name) VALUES (?)", ("test",))
cursor.execute("SELECT * FROM t WHERE name = ?", ("test",))
rows = cursor.fetchall()              # 获取所有行
conn.commit()                         # 提交
conn.close()                          # 关闭

# === 与 Pandas 结合 ===
df = pd.read_sql_query("SELECT * FROM t", conn)  # SQL → DataFrame
df.to_sql("t", conn, if_exists="replace")         # DataFrame → SQL

# === WAL 模式（提升并发性能）===
conn.execute("PRAGMA journal_mode=WAL")

# === 命令行 ===
# sqlite3 data.db                    打开数据库
# .tables                            列出表
# .schema table                      表结构
# .mode csv / .mode column           显示模式
# .headers on                        显示列名
# .dump                              导出 SQL
# .quit                              退出

# === SQLite vs PostgreSQL ===
# SQLite：单文件、零配置、适合小项目
# PostgreSQL：服务器模式、高并发、适合生产环境