pythonsqlite导出json_使用 Python 的 SQLite JSON1 和 FTS5 扩展

早在九月份，编程界出现一个名为 json1.c 的文件，此前这个文件一直在 SQLite 的库里面。还有，笔者也曾总结通过使用新的 json1 扩展来编译 pysqlite 的技巧。但现在随着 SQLite 3.9.0 的发布，用户已经不用再费那么大劲了。

SQLite 3.9.0 版本做了很大的升级，不仅增加了万众期待的 json1 扩展，还增加了具有全文检索的新版本 fts5 扩展模块。 fts5 扩展模块提高了复杂查询的性能，并且提供了开箱即用的 BM25 排序算法。该算法在其他相关领域排序方面也有着重大意义。使用者可通过查看发布说明以了解全部新增功能。

本文主要介绍如何添加 json1 和 fts5 扩展编译 SQLite。这里将使用新版 SQLite 库编译 python 驱动程序，也利用 python 新功能。由于个人很喜欢 pysqlite 和 apsw，所以下文步骤中将会包括建立两者的指令。最后，将在 peewee ORM 通过 json1 和 fts5 扩展进行查询。

使用入门

首先从获取新版 SQLite 源码入手，一种方法是通过使用 SQLite 源代码管理系统 fossil 来完成，另一种是下载一个压缩图像。 SQLite 使用 tcl 和 awk 进行源码融合，因此在开始前，需要安装下列工具：

tcl

awk (可用于大多数 unix系统)

fossil (可选)

该过程涉及几个步骤，这里尽量将步骤细化。首先需要为新库分配一个全新的目录，笔者把它放在 ~/bin/jqlite 中，使用者可根据个人喜好自行选择。

export JQLITE="$HOME/bin/jqlite"

mkdir -p $JQLITE

cd $JQLITE

通过 fossil 获取源码，运行以下命令：

fossil clone http://www.sqlite.org/cgi/src sqlite.fossil

fossil open sqlite.fossil

获取快照文件，运行以下命令：

curl 'https://www.sqlite.org/src/tarball/sqlite.tar.gz?ci=trunk' | tar xz

mv sqlite/* .

如果你更喜欢使用官方正式版，可在 SQLite 下载页 下载 autoconf 的压缩包，并将内容解压到 $JQLITE 目录中。

利用 json1 和 fts5 编译 SQLite

代码下载完成后，把它和 SQLite 源代码树放在同一目录下。SQLite 支持大量的编译配置选项，除了 json1 和 fts5，还有很多其他有效的选择。

编译遵循典型的 configure -> make -> make install 顺序 ：

export CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 \

-DSQLITE_ENABLE_DBSTAT_VTAB=1 \

-DSQLITE_ENABLE_FTS3=1 \

-DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \

-DSQLITE_ENABLE_FTS5=1 \

-DSQLITE_ENABLE_JSON1=1 \

-DSQLITE_ENABLE_RTREE=1 \

-DSQLITE_ENABLE_UNLOCK_NOTIFY \

-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \

-DSQLITE_SECURE_DELETE \

-DSQLITE_SOUNDEX \

-DSQLITE_TEMP_STORE=3 \

-fPIC"

LIBS="-lm" ./configure --prefix=$JQLITE --enable-static --enable-shared

make

make install

在 SQLite3 Source Checkout 中，应该有一个 lib/libsqlite3.a 文件。如果文件不存在，检查控制器的输出，查看错误日志。我在 arch 和 ubuntu 上都已执行成功，但 fapple 和 windoze 我不确定能否成功。

创建 pysqlite

大多数 python 开发者对 pysqlite 一定不陌生，在 Python 标准库中 pysqlite 或多或少的和 sqlite3 模块相似。要建立和 libsqlite3 相对应的 pysqlite，唯一需要做的是修改 setup.cfg 文件使其指向刚才创建的 include 和 lib 目录。

git clone https://github.com/ghaering/pysqlite

cd pysqlite/

cp ../sqlite3.c .

echo -e "library_dirs=$JQLITE/lib" >> setup.cfg

echo -e "include_dirs=$JQLITE/include" >> setup.cfg

LIBS="-lm" python setup.py build_static

测试安装，进入 build/lib.linux-xfoobar/ 目录，启动 Python 解释器，运行以下命令：

>>> from pysqlite2 import dbapi2 as sqlite

>>> conn = sqlite.connect(':memory:')

>>> conn.execute('CREATE VIRTUAL TABLE testing USING fts5(data);')

>>> conn.execute('SELECT json(?)', (1337,)).fetchone()

(u'1337',)

接下来就看你心情了，你可以运行 python setup.py 安装文件，也可以把新建的 pysqlite2（可在 build/lib.linux.../ 目录下查看）链接到 $PYTHONPATH。如果想同时使用 virtualenv 和 $PYTHONPATH ，可以先激活 virtualenv，然后返回 pysqlite 目录下运行 setup.py 来安装文件。

创建 apsw

创建 apsw 的步骤几乎和建立 pysqlite 相同。

cd $JQLITE

git clone https://github.com/rogerbinns/apsw

cd apsw

cp ../sqlite3{ext.h,.h,.c} .

echo -e "library_dirs=$SQLITE_SRC/lib" >> setup.cfg

echo -e "include_dirs=$SQLITE_SRC/include" >> setup.cfg

LIBS="-lm" python setup.py build

为了测试新的 apsw 库，更改目录到 build/libXXX。启动 Python 解释器，运行下列命令：

>>> import apsw

>>> conn = apsw.Connection(':memory:')

>>> cursor = conn.cursor()

>>> cursor.execute('CREATE VIRTUAL TABLE testing USING fts5(data);')

>>> cursor.execute('SELECT json(?)', (1337,)).fetchone()

(u'1337',)

可通过运行 Python setup.py 安装文件来安装新 apsw 全系统，或者链接 apsw.so 库（可在 build/lib.linux.../ 查看）到 $PYTHONPATH。如果开发者想同时使用 virtualenv 和 apsw ，可以先激活 virtualenv，然后返回 apsw 目录下运行 setup.py 安装文件。

使用 JSON 扩展

json1 扩展中具有一些简洁特性，尤其是 json_tree 和 json_each 函数/虚拟表（详情）。为了展示这些新功能，本文特意利用 peewee（小型 Python ORM）编写了一些 JSON 数据并进行查询。

原打算从 GitHub 的 API 上获取测试数据，但为了展示最少冗长这个特性，特意选择编写一个小的 JSON 文件（详情）。其结构如下：

[{

"title": "My List of Python and SQLite Resources",

"url": "http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/",

"metadata": {"tags": ["python", "sqlite"]}

},

{

"title": "Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python"

"url": "http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-engine-as-a-stand-alone-nosql-database-with-python/",

"metadata": {"tags": ["nosql", "python", "sqlite", "cython"]}

},

...]

如果更愿意以 IPython 格式查看代码，参考此处。

填充数据库

获取 JSON 数据文件和进行解码：

>>> import json, urllib2

>>> fh = urllib2.urlopen('http://media.charlesleifer.com/downloads/misc/blogs.json')

>>> data = json.loads(fh.read())

>>> data[0]

{u'metadata': {u'tags': [u'python', u'sqlite']},

u'title': u'My List of Python and SQLite Resources',

u'url': u'http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/'}

现在，需要告知 peewee 怎样去访问我们数据库，通过存入 SQLite 数据库的方式使用自定义的 pysqlite 接口。这里使用的是刚刚编译完成的 pysqlite2，虽然它和 tojqlite 有所混淆，但这并不冲突。在定义数据库类后，将创建一个内存数据库。（注：在接下来的2.6.5版本中，如果其使用比 sqlite3 更新版本编译，peewee 将自动使用 pysqlite2）。

>>> from pysqlite2 import dbapi2 as jqlite

>>> from peewee import *

>>> from playhouse.sqlite_ext import *

>>> class JQLiteDatabase(SqliteExtDatabase):

... def _connect(self, database, **kwargs):

... conn = jqlite.connect(database, **kwargs)

... conn.isolation_level = None

... self._add_conn_hooks(conn)

... return conn

...

>>> db = JQLiteDatabase(':memory:')

利用 JSON 数据填充数据库十分简单。首先使用单一 TEXT 字段创建一个通用表。此时，SQLite 不会显示 JSON 数据单独的列/数据类型，所以需要使用 TextField：

>>> class Entry(Model):

... data = TextField()

... class Meta:

... database = db

...

>>> Entry.create_table()

>>> with db.atomic():

... for entry_json in data:

... Entry.create(data=json.dumps(entry_json))

...

JSON 的功能

首先介绍下 json_extract()。它通过点/括号的路径来描述要找的元素（postgres 使用的是[]）。数据库的每个 Entry 中包含单一数据列，每个数据列中又包含一个 JSON 对象。每个 JSON 对象包括一个标题，一个 URL 和顶层的元数据键，下面是提取作品标题的代码：

>>> title = fn.json_extract(Entry.data, '$.title')

>>> query = (Entry

... .select(title.alias('title'))

... .order_by(title)

... .limit(5))

...

>>> [row for row in query.dicts()]

[{'title': u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More'},

{'title': u'Alternative Redis-Like Databases with Python'},

{'title': u'Building the SQLite FTS5 Search Extension'},

{'title': u'Connor Thomas Leifer'},

{'title': u'Extending SQLite with Python'}]

对应下面 SQL 创建的查询：

SELECT json_extract("t1"."data", '$.title') AS title

FROM "entry" AS t1

ORDER BY json_extract("t1"."data", '$.title')

LIMIT 5

在接下来的例子中，将提取包含特定标签的条目。利用 json_each() 函数搜索标签列表。该函数类似于表（实际指的是虚表），返回筛选后的指定 JSON 路径，下面是如何检索标题为「Sqlite」条目的代码。

>>> from peewee import Entity

>>> tags_src = fn.json_each(Entry.data, '$.metadata.tags').alias('tags')

>>> tags_ref = Entity('tags')

>>> query = (Entry

... .select(title.alias('title'))

... .from_(Entry, tags_src)

... .where(tags_ref.value == 'sqlite')

... .order_by(title))

...

>>> [row for row, in query.tuples()]

[u'Building the SQLite FTS5 Search Extension',

u'Extending SQLite with Python',

u'Meet Scout, a Search Server Powered by SQLite',

u'My List of Python and SQLite Resources',

u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension',

u"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python",

u'Web-based SQLite Database Browser, powered by Flask and Peewee']

上述查询的 SQL 有助阐明整个过程：

SELECT json_extract("t1"."data", '$.title') AS title

FROM

"entry" AS t1,

json_each("t1"."data", '$.metadata.tags') AS tags

WHERE ("tags"."value" = 'sqlite')

ORDER BY json_extract("t1"."data", '$.title')

随着查询变得更加复杂，可通过使用 Peewee 对象对查询进行封装，使之变得更加有用，同时也使得代码能够重用。

下面是 json_each() 的另一个例子。这次将筛选每个条目中的标题，并建立相关标签的字符串，字符串中用逗号分隔。这里将再次使用上文定义的 tags_src 和 tags_ref。

>>> query = (Entry

... .select(

... title.alias('title'),

... fn.group_concat(tags_ref.value, ', ').alias('tags'))

... .from_(Entry, tags_src)

... .group_by(title)

... .limit(5))

...

>>> [row for row in query.tuples()]

[(u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More',

u'peewee, sql, python'),

(u'Alternative Redis-Like Databases with Python',

u'python, walrus, redis, nosql'),

(u'Building the SQLite FTS5 Search Extension',

u'sqlite, search, python, peewee'),

(u'Connor Thomas Leifer', u'thoughts'),

(u'Extending SQLite with Python', u'peewee, python, sqlite')]

为了清晰起见，这里是对应的 SQL 查询语句：

SELECT

json_extract("t1"."data", '$.title') AS title,

group_concat("tags"."value", ', ') AS tags

FROM

"entry" AS t1,

json_each("t1"."data", '$.metadata.tags') AS tags

GROUP BY json_extract("t1"."data", '$.title')

LIMIT 5

最后介绍的功能是 json_tree()。如同 json_each()，json_tree() 同样是一个多值函数，同样与表类似。但不同但时 json_each() 仅返回特定路径的 children，而 json_tree() 将递归遍历全部对象，返回全部的 children。

如果标签键嵌套在条目的任意位置，下面是如何匹配给定标签条目的代码：

>>> tree = fn.json_tree(Entry.data, '$').alias('tree')

>>> parent = fn.json_tree(Entry.data, '$').alias('parent')

>>> tree_ref = Entity('tree')

>>> parent_ref = Entity('parent')

>>> query = (Entry

... .select(title.alias('title'))

... .from_(Entry, tree, parent)

... .where(

... (tree_ref.parent == parent_ref.id) &

... (parent_ref.key == 'tags') &

... (tree_ref.value == 'sqlite'))

... .order_by(title))

...

>>> [title for title, in query.tuples()]

[u'Building the SQLite FTS5 Search Extension',

u'Extending SQLite with Python',

u'Meet Scout, a Search Server Powered by SQLite',

u'My List of Python and SQLite Resources',

u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension',

u"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python",

u'Web-based SQLite Database Browser, powered by Flask and Peewee']

在上述代码中选取了 Entry 自身，以及代表该 Entry 子节点的二叉树。因为每个树节点包含对父节点的引用，我们可以十分简单搜索命名为「标签」的父节点，该父节点包含值为「sqlite」的子节点。

下面是 SQL 实现语句：

SELECT json_extract("t1"."data", '$.title') AS title

FROM

"entry" AS t1,

json_tree("t1"."data", '$') AS tree,

json_tree("t1"."data", '$') AS parent

WHERE (

("tree"."parent" = "parent"."id") AND

("parent"."key" = 'tags') AND

("tree"."value" = 'sqlite'))

ORDER BY json_extract("t1"."data", '$.title')

这仅是 json1 扩展功能的一个方面，在接下来的几周将会尝试使用其更多的功能。请在此处给我留言，或者如果对该扩展存在特定的问题，可通过邮件向 sqlite-users 咨询。

FTS5 与 Python

本小节中的代码均是之前 JSON 示例中的代码，这里将使用 Entry 数据文件的标题并且用它们填充搜索索引。peewee 2.6.5版本将包含 FTS5Model 功能，目前该功能可在 Github 主分支上可用。

重新回到之前的 JSON 例子中去，新建另一张表，作为 Entry 数据的查询索引。

fts5 扩展要求所有的列不包含任何类型或约束。用于表示一列的唯一附加信息是无索引，意味着只能存储数据并不能进行数据查询。

对 entry 模型定义一个查询索引，以实现通过查询标题来确定相关的 URL。为此，需要将 url 字段定义为无索引。

class EntryIndex(FTS5Model):

title = SearchField()

url = SearchField(unindexed=True)

class Meta:

database = db

options = {'tokenize': 'porter', 'prefix': '2,3'}

EntryIndex.create_table()

对于 fts5 扩展，该可选字典提供了附加元数据进行标记字段，以及通过前缀的长度存储快速前缀查询。利用 SQL 创建表的语句如下：

CREATE VIRTUAL TABLE "entryindex" USING fts5 (

"title" ,

"url" UNINDEXED,

prefix=2,3,

tokenize=porter)

为了填充索引，将使用一对 JSON 函数从 Entry 模型中复制数据：

title = fn.json_extract(Entry.data, '$.title').alias('title')

url = fn.json_extract(Entry.data, '$.url').alias('url')

query = Entry.select(title, url).dicts()

with db.atomic():

for entry in query:

EntryIndex.create(**entry)

索引填充后，进行一些查询：

>>> query = EntryIndex.search('sqlite').limit(3)

>>> for result in query:

... print result.title

Extending SQLite with Python

Building the SQLite FTS5 Search Extension

My List of Python and SQLite Resources

实现上述查询的 SQL 语句为：

SELECT "t1"."title", "t1"."url"

FROM "entryindex" AS t1

WHERE ("entryindex" MATCH 'sqlite')

ORDER BY rank

同样可对查询后的结果进行检索：

>>> query = EntryIndex.search('sqlite AND python', with_score=True)

>>> for result in query:

... print round(result.score, 3), result.title

-1.259 Extending SQLite with Python

-1.059 My List of Python and SQLite Resources

-0.838 Querying Tree Structures in SQLite using Python and the Transitive Closure Extension

这些结果是非常准确，用于上述查询的 SQL 语句如下：

SELECT "t1"."title", "t1"."url", rank AS score

FROM "entryindex" AS t1

WHERE ("entryindex" MATCH 'sqlite AND python')

ORDER BY rank

本文中只是简要介绍了 fts5 扩展的简单功能，如果使用者查询该文档，将会发现其更多强大的功能。以下是一些例子：

多列索引，在排序时分配不同的权重

前缀查询、引述语、相邻的行的关键词

上述查询类型与布尔型运算符结合

unicode61默认编码转化器、porter分解器禁止使用

用于定义排序功能和断词的新的 C API。

词汇表，用于查询词的数量和检查索引

感谢阅读

在 SQLite 添加 JSON 扩展对该项目和用户来说都是一件好事。Postgresql 和 MySQL 都已支持 JSON 数据类型，很高兴能 SQLite 跟随他们的脚步。但并不是任何条件下均需要是 JSON 数据格式，例如某些情况下需要用到专用的嵌入式文件存储库 UnQLite。

json1.c 文件同样值得注意。Dr. Hipp 提到：json1.c 现在只是第一步，未来还有更多的发展空间。因此，无论当前版本存在任何问题，我始终坚信将来发布的版本中性能和 APIS 两个方面都会有很大的改善。还有一点，我相信他会考虑使用更高效的二进制格式。

很高兴看到 SQLite 在全文查询扩展模块上不断地自我完善和提高。为用户提供一个内置算法和一个用户可自行添加所需内容的 API。

OneAPM 能够帮你查看 Python 应用程序的方方面面，不仅能够监控终端的用户体验，还能监控服务器监性能，同时还支持追踪数据库、第三方 API 和 Web 服务器的各种问题。想阅读更多技术文章，请访问 OneAPM 官方技术博客。