一文搞定 SQLite：命令行、图形界面与 Python

SQLite 是一种轻量级关系型数据库。本文从数据库文件和表的基本概念开始，介绍如何用命令行或 DB Browser for SQLite 创建数据库、导入和导出 CSV、管理数据，再用 Python 的 sqlite3 模块执行查询。最后通过 Twitter 数据分析案例，看看 SQLite 如何用于实际工作。

如果您熟悉关系型数据库，可能听过 MySQL、SQL Server、PostgreSQL、IBM DB2 或 Oracle 11g。SQLite 也是一款广泛使用的关系型数据库管理系统（RDBMS），它的特点包括：

• 无需服务器：不必启动、停止或配置服务器进程，也不需要数据库管理员分配实例或管理用户访问权限。
• 简单的数据库文件：一个 SQLite 数据库通常就是一个普通的磁盘文件，可以放在任意目录中，也能通过复制文件共享。
• 轻量且配置简单：SQLite 的体积极小（不超过 500 KB），通常无需复杂配置。
• 事务与并发支持：SQLite 具备事务与并发特性。
• 灵活的类型系统：SQLite 使用显式类型（Manifest Typing）。与大多数依赖静态类型的 SQL 数据库不同，它允许在列中存放与声明类型不同的数据；但也有例外，例如整数主键列只能存储整数。

SQLite 也有局限，例如不支持 RIGHT OUTER JOIN 或 FULL OUTER JOIN。无服务器的特性在某些场景下也可能带来安全性和数据滥用方面的考虑。总体来说，它在合适的场景中非常实用。

SQLite 可以通过多种方式使用：命令行适合直接执行点命令和 SQL；DB Browser for SQLite 提供图形界面；Python 的 sqlite3 模块则便于在程序中读写数据库。下面先用图形界面建立数据库，再介绍命令行操作，最后转到 Python。

02用 DB Browser 创建数据库和数据表

安装与创建数据库

安装和设置 SQLite 只需几分钟。除了命令行工具，也可以使用图形界面工具。本节使用 DB Browser for SQLite，请根据操作系统从下载页面获取。

在 Windows 7 平台上，DB Browser for SQLite 的界面如下：

打开工具后，点击 New Database，输入数据库名称即可创建一个新的 SQLite 数据库。系统随后会提示您创建数据表；这里可以先跳过，稍后再建。

创建 consumers 表

创建表之前，先确定它的模式（schema）。这里创建一个名为 consumers 的示例表，包含以下字段：

• consumer_id：整数，作为主键
• consumer_full_name：字符串，不可为 NULL
• consumer_email：字符串，不可为 NULL
• consumer_grade：字符，不可为 NULL

在 DB Browser 中点击 Create Table，填写表名和字段信息。界面也会显示相应的 SQL。完成后点击 OK，数据库中就会出现 consumers 表。

如果将 consumer_id 设为自增字段，SQLite 可能会创建 sqlite_sequence 表来维护序列。您也可以在 Execute SQL 区域直接运行 SQL 查询。

03导入数据并使用命令行

SQLite 的命令行工具既能管理数据库，也能运行 SQL。Windows 用户可以先访问 SQLite 下载页面，下载 sqlite-tools-win32-x86-3270200.zip 并解压。解压后会看到如下三个文件：

双击 sqlite3 应用程序即可启动命令行窗口：

SQLite 命令行默认使用临时的内存数据库。若要创建磁盘上的数据库文件，可以使用 .open 点命令并指定名称。示例中创建名为 Tweet_Data.db 的文件；添加 .db 扩展名有助于识别数据库文件。

导入 CSV 文件

命令行可以将 CSV 文件导入为表。先用 .mode csv 设置 CSV 模式，再用 .import 指定文件路径和表名；CSV 与程序位于同一文件夹时可以使用文件名，也可以输入完整路径。需要显示列名时，使用 .headers on。

导入后，可以用 .tables 查看数据库中的表，用 .schema 加表名查看表结构，再执行查询确认数据已导入。例如：

SELECT * FROM Table_Name;

如果使用 DB Browser 导入 CSV，菜单路径是 File → Import → Table from CSV file。选择文件后，在导入对话框中指定表名，并勾选 Column names in the first line，让 SQLite 将首行识别为列名。确认后，新表会出现在数据库中；可以执行 SELECT 查询检查导入结果。

创建表、插入和更新记录

除了导入文件，也可以用 CREATE TABLE 创建表，再用 INSERT INTO 插入行。下面的命令行示例从零创建一张表并插入两行数据：

在 SQLite 命令行中，可以按 Enter 换行继续输入同一个查询；等语句末尾出现分号后再按 Enter，查询才会执行。

在 DB Browser 中，也可以在创建好的 consumers 表里执行 SQL 或通过 Python 插入数据，后文会展示 Python 操作方式。

导出 CSV

要将查询结果导出到 CSV，先启用列名并设置 CSV 模式，然后用 .output 指定目标文件，执行要导出的查询，最后用 .exit 退出 SQLite：

导出的新 CSV 文件包含 Sample_Tweets_2 数据：

更新或删除表

可以使用 UPDATE ... SET 更新表中的行。下图将示例表 Sample_Tweets_2 中的第二条推文更新：

若要删除整张表，可以执行 DROP TABLE。下面从数据库文件中删除示例表 Sample_Tweets_2：

04在 Python 中使用 SQLite

SQLite 自带 Python 标准库模块 sqlite3，可用于连接数据库、执行 SQL 和读取结果。该模块包含在 Python 3 中，也包含在 Anaconda 发行版中。以下示例使用前面创建的 tutorial.db 和 countries 表。

先调用 connect() 并传入数据库路径。该方法会返回一个 Connection 对象；数据库文件通常使用 .db 扩展名。然后通过连接创建 Cursor，调用游标的 execute() 方法运行 SQL：

import sqlite3

conn = sqlite3.connect('tutorial.db')

cur = conn.cursor()
cur.execute('SELECT * from countries')

执行 SELECT 后，可以把游标当作迭代器，也可以调用 fetchone() 取一行或 fetchall() 取剩余结果。下面先取出一行，再打印其余行的示例输出：

print(cur.fetchone())
print(cur.fetchall())

输出示例：

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')
[('NLD', 'Netherlands', 'Europe', 'Western Europe', '1581', 'Nederland', 'Constitutional Monarchy', 'Amsterdam'), ('ALB', 'Albania', 'Europe', 'Southern Europe', '1912', 'Shqiperia', 'Republic', 'Tirane'), ('DZA', 'Algeria', 'Africa', 'Northern Africa', '1962', 'Al-Jaza\x92ir/Algerie', 'Republic', 'Algiers'), ('ASM', 'American Samoa', 'Oceania', 'Polynesia', None, 'Amerika Samoa', 'US Territory', 'Pago Pago'), ('AND', 'Andorra', 'Europe', 'Southern Europe', '1278', 'Andorra', 'Parliamentary Coprincipality', 'Andorra la Vella'), ...]

您也可以遍历查询结果，让每行单独显示：

for row in cur.execute('SELECT * FROM countries'):
print(row)

示例输出：

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')
('NLD', 'Netherlands', 'Europe', 'Western Europe', '1581', 'Nederland', 'Constitutional Monarchy', 'Amsterdam')
('ALB', 'Albania', 'Europe', 'Southern Europe', '1912', 'Shqiperia', 'Republic', 'Tirane')

使用参数查询

例如，要查询 code = 'AFG' 的国家详情，可以使用问号占位符并传入参数：

code = ('AFG',)
cur.execute('SELECT * FROM countries WHERE code = ?', code)
print(cur.fetchone())

结果为：

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')

也可以用字符串格式化构造查询，示例如下；不过，前面的参数化写法更安全：

code = 'AFG'
cur.execute("SELECT * FROM countries WHERE code = '%s'" % code)
print(cur.fetchone())

输出同样是：

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')

插入多条记录并提交

记录既可以逐条插入，也可以一次插入多条。下面先向空的 consumers 表插入一条记录，再用 executemany() 插入两条记录：

# One by one
cur.execute("INSERT INTO consumers VALUES (1,'John Doe','[email protected]','A')")
for row in cur.execute('SELECT * FROM consumers'):
print(row)

输出：

(1, 'John Doe', '[email protected]', 'A')

准备多条记录并批量插入：

# Prepare a list of records to be inserted
purchases = [(2,'John Paul','[email protected]','B'),
             (3,'Chris Paul','[email protected]','A'),
            ]

# Use executemany() to insert multiple records at a time
cur.executemany('INSERT INTO consumers VALUES (?,?,?,?)', purchases)
for row in cur.execute('SELECT * FROM consumers'):
print(row)

结果为：

(1, 'John Doe', '[email protected]', 'A')
(2, 'John Paul', '[email protected]', 'B')
(3, 'Chris Paul', '[email protected]', 'A')

您可以在 DB Browser 中交叉检查这些数据行。调用连接对象的 commit() 方法，才能提交并保存事务：

conn.commit()

提交后，DB Browser 中应能看到新增的数据：

操作完成后，应关闭数据库连接；关闭前先提交需要保留的更改：

# Closing the DB connection
conn.close()

05用 SQLite 分析 Twitter 数据

掌握了数据库、表和查询的基本操作后，可以看一个实际案例。在一个社交媒体分析项目中，SQLite 被用于存储数百万 Twitter 用户的 $edge \space list^{1}$，操作简单而高效。

[^1]: 边列表（edge list）或邻接表是网络分析中表示图的一种方式。列表描述图中节点（或顶点）之间的邻居关系。

首先，在 Sample_Tweets 表中查找所有唯一的 Twitter 用户屏幕名。收集推文的唯一用户很重要，因为项目需要获取这些用户的关注者，以创建边列表。

数据库中共有 26,860 位唯一用户。项目关注网站本地化和翻译，这个样本规模相当可观。接下来，用稍复杂一些的查询找出发推最多的前 20 位用户（不含转推）：

结果中发推最多的是一个机器人。名单最后一位 UweMuegge，根据项目计算的多种网络统计指标，确实颇具影响力，但并不是最重要的那位。

最后，查询 UweMuegge 发布过的一些推文：

从示例结果看，他的推文主要涉及翻译招聘信息和翻译会议，这也有助于解释为什么业内人士会在 Twitter 上关注他。

命令行截图也展示了它的一个限制：文本字段较长时不容易阅读。遇到这种情况，可以改用 DB Browser for SQLite 查看数据。

在这个项目中，关注者边列表存放在使用 RSQLite 创建的 SQLite 数据库中，每位用户的关注者网络会逐一追加到表里。持续保存数据很重要：如果停电或 Windows 自动更新造成强制关机，受 Twitter API 速率限制影响，重新收集关注者网络可能需要约 4 周。将结果持续写入 SQLite 后，即使中途关机，也能从数据库中最后一位用户的下一位继续收集，避免从头开始。

06总结

本文从 SQLite 的特点和限制出发，介绍了三种常见的使用方式：用 DB Browser 图形化创建数据库和表，用命令行导入、导出并管理 CSV 和表，再通过 Python sqlite3 连接数据库、查询、插入和提交数据。Twitter 案例则展示了 SQLite 如何保存大规模分析过程中的中间结果，帮助任务在中断后继续进行。

SQLite 也可以与 R 和 Python 搭配使用。例如，可以通过 Python 的 sqlite3 模块操作数据库，也可以使用 R 与 RSQLite。对于数据科学工作，SQLite 是一种简单实用的工具，值得继续探索。