轻量级数据库SQLite 入门

虽然我们都知道，在很多情况下只有 Postgres（或 MySQL、Oracle、SQL Server 等）对应用的某些需求很有效果，不过这不是本文的讨论范围，本文只想强调管理 SQLite 数据库上手的基础操作步骤，然后讲述一下与传统数据库服务器之间的区别。

SQLite 非常容易上手，不需要任何经验，5分钟内就可以学会安装SQLite并创建一个新的数据库。

访问SQLite的官网，找到下载页面，获取windows版本的SQLlite

https://www.sqlite.org/download.html

找到sqlite-tools-win32-x86的下载包

下载下来之后，解压zip包，解压后的文件如下：

双击sqlite3即可打开SQLite的CLI命令行界面

我们通过命令来熟悉SQLite的基本操作，使用.help可以获取SQLite的所有命令帮助。我们按照提示创建一个数据库。执行sqlite> .open testdb 即可创建一个新的数据库文件。创建的数据库文件在sqlite.exe所在的目录：

也可以在命令行使用sqlite.exe testdb打开数据库文件，文件不存在时会创建新文件。

打开数据库文件后，创建一个表test，并插入数据

sqlite> .open testdb
sqlite> CREATE TABLE test (id integer primary key, value text);
sqlite> INSERT INTO test (id, value) VALUES(1,'eenie');
sqlite> INSERT INTO test (id, value) VALUES(2,'meenie');
sqlite> INSERT INTO test (value) VALUES('miny');
sqlite> INSERT INTO test (value)  VALUES('mo');

查看插入的内容

后两条insert没有指定id列的值，这里使用了自动增量。使用 

sqlite> select last_insert_rowid()

可以获取最后插入的自动增量值。

我们再添加一个索引和一个视图

使用 sqlite> .exit 退出数据库

重新打开数据库，查看表、索引 、schema信息

导出SQL数据

导出的sql文本，可以通过notepad打开查看

导入数据

通过上述一番操作，会发现SQLite是非常容易上手的数据库，当然也能够在真实的生产环境中完成一些真正的工作。

便于管理

不知你是否管理过 Postgres 数据库？想要确保数据库服务器正确配置，需要了解不少东西，比如共享缓存、有效缓存大小、work mem、work mem 的维护以及 wal 缓存等等。此外升级的过程也很恐怖，使用者需要先将数据库离线，运行程序来升级，然后祈祷在重新打开时能正常运作。另外，postgres 数据库具体在哪里呢？你能否指着某个地方说：“那就是我的数据库？”

虽然我们都知道，在很多情况下只有 Postgres（或 MySQL、Oracle、SQL Server 等）对应用的某些需求很有效果，不过这不是本文的讨论范围，本文只想强调管理 SQLite 数据库与传统数据库服务器之间的区别。

SQLite 便于管理——只有单个文件（有时候是一个文件+事务日志），这个文件的格式在多个主要版本中都是通用的，也就是说如果我有一个3.0.0版本（2004年）的 SQLite 数据库文件，便可以在最新的 SQLite 3.10.0上使用。如果想要在别处使用这个数据库文件，也只需复制到U盘里，甚至存放到云存储中。如果想要每天晚上进行备份，只需将此数据库文件同步到 S3。如果想要与同事分享我的数据分析，也只需给他们发送一份数据库文件备份即可。这个数据库的一大特性就是只有单文件，且文件格式多年以来非常稳定。

此外，SQLite 配置起来也很简单，其功能有两种管理方式：编译标识以及编译指示语句（运行时配置）。没有什么配置文件，只需使用想要的功能来构建相应的库，然后在建立数据库连接时配置运行时选项即可。

稳定性坚如磐石，且还在不断提高

目前有一些优秀的大牛工程师正在积极地进行 SQLite 的开发，使得 SQLite 新增高质量新功能的速度十分惊人。就在最近，SQLite 还加入了 json1 扩展程序以支持 JSON 数据，想要了解如何在 Python 中使用它，请查看这篇文章。SQLite 还发布了一个全文搜索扩展包的改进版，其中包括使用 BM25 算法对结果进行排序。

除了新增功能之外，SQLite 的开发者也在努力改进 library 的性能，在3.8.11版本的发布说明中，包含这些宣传内容：

新版本 SQLite，运行速度是3.8.0版本的两倍，是3.3.9版本的三倍。

尽管一直在更新和改进，SQLite 却很少有新增的 bug。SQLite 的测试套件公认是业内最好的测试套件之一，而“ SQLite 是如何测试的”相关文档也被频繁推荐到 HackerNews 上。

可扩展性与可控性

笔者最喜爱 SQLite 的地方是它的可扩展性，SQLite 是应用嵌入式的，它与应用运行在同一个地址空间中，并能代表你执行应用代码。在 Python 标准库中，无论是 SQLite 驱动的 pysqlite ，还是可选驱动 apsw 都为自定义 SQL 函数、聚合函数与排序规则提供了相应的 API；apsw 更进一步，为定义虚拟表和虚拟文件系统提供了相应的 API。

在实际案例中，假设表格中有一列用于存储 URL，你还想确定最常见的主机名是哪些——如果使用不同的数据库，就必须编写复杂的正则表达式（字符串操作函数组），或者将数据从应用中抽出来，然后在代码中进行计算。使用 SQLite 的话，就可以在 Python 中定义主机名，并使用它来创建简单的 COUNT 查询：

from urlparse import urlparse
def hostname(url):
return urlparse(url).netloc
conn = sqlite3.connect('my-database.db')
conn.create_function('hostname', 1, hostname)  # name, num_params, func

SELECT hostname(mytable.url), COUNT(mytable.id) AS ct
FROM mytable
GROUP BY hostname(mytable.url)
ORDER BY ct DESC;

还可以创建聚合函数，输入0……n的值，生成单独的输出值。样例可能包括：计算标准差、通过处理值来生成字符串、进行某种类型的分类等。

虚拟表目前仅受 apsw 支持，用户可以在代码中定义表格，并将其当作普通的 SQL 表格查询，即便后台数据是完全动态的。比如，我编写了一个简单的虚拟表格，允许用户将其当作 SQL 表格来查询 Redis。

你也可以编写同名函数，返回0……n行结果，比如正则表达式：处理输出内容，并生成一行行匹配 token。我写了一个库叫做sqlite-vtfunc，用来编写这类函数非常简单。

实际上，SQLite 的各个方面都可以受应用的控制。

快如闪电

SQLite 速度非常快，它运行在同一台机器上，因此在执行查询或读取结果时并不产生网络开销。由于与应用运行在同一个地址空间中，因此并无连接协议、序列或通过 unix socket 通讯的需求。SQLite 也可以在资源匮乏、要求高效率的移动设备上运行，并支持大量的编译标记：允许用户移除没有计划使用的功能。

SQLite 的速度弥补了它的最大缺点之一：写入时数据库文件锁定。通过快速写入数据，只有当有大量的并发写入时，数据库锁定才会成为问题。

WAL模式

SQLite 的3.7.0发布版增加了新的日志记录方法：使用预写日志。单独来看这个消息并不太吸引人，但对于 web 应用开发者来说（或者要应付并发问题的开发者来说），这意味着读取并不会再阻碍写入了，反之亦然。或者换句话说，读取和写入能够并发进行。没有 WAL 模式的话，想要写入数据库则要求写入程序独占数据库的访问权，在写入完成前无法读取。

下面是一个样例，说明了两者的不同。假设我们有两个进程，一个写入、一个读取。写入程序开启了独占事务（表明打算写入）；读取程序开启事务；读取程序尝试发布SELECT语句：

Journal mode = "delete" (the default):
Writer: BEGIN EXCLUSIVE
Reader: BEGIN
Reader: SELECT * FROM foo; Error: database is locked

Journal mode = "wal":
Writer: BEGIN EXCLUSIVE
Reader: BEGIN
Reader: SELECT * FROM foo; Returns table contents

然而值得注意的是：即使不启用 WAL 模式，写入通常在几毫秒中发生。这个时间太短了，用户只会在并发很高或者写入事务用时很长时才会注意到这个问题。

总结

SQLite是一款非常优秀的数据库，非常容易上手使用，可以轻松在生产环境中完成一些真正的工作。