开发者社区> oneapm_official> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

使用 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 使用 tclawk 进行源码融合,因此在开始前,需要安装下列工具:

  • 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 支持大量的编译配置选项,除了 json1fts5,还有很多其他有效的选择。

编译遵循典型的 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 文件使其指向刚才创建的 includelib 目录。

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);')
<pysqlite2.dbapi2.Cursor object at 0x7ff7d0a2dc60>
>>> 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);')
<apsw.Cursor at 0x7fcf6b17fa80>
>>> 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_treejson_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。

原文地址:http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
(Python)扩展速记符
(Python)扩展速记符
0 0
【Python零基础入门篇 · 13】:字符串判断扩展、变量作用域、global和nonlocal的使用
【Python零基础入门篇 · 13】:字符串判断扩展、变量作用域、global和nonlocal的使用
0 0
ASCII编码(Python扩展)
了解ASCII.和他的作用。
0 0
Python:Flask技术栈及Extensions扩展整理
Python:Flask技术栈及Extensions扩展整理
0 0
Python:Flask缓存扩展Flask-Caching
Python:Flask缓存扩展Flask-Caching
0 0
Python编程:Flask扩展Flask-Mail发送邮件
Python编程:Flask扩展Flask-Mail发送邮件
0 0
Python编程:Flask扩展Flask-Migrate迁移数据库
Python编程:Flask扩展Flask-Migrate迁移数据库
0 0
Python编程:Flask扩展Flask-WTF和Flask-SQLAlchemy应用实例
Python编程:Flask扩展Flask-WTF和Flask-SQLAlchemy应用实例
0 0
Python编程:Flask数据库扩展Flask-SQLAlchemy
Python编程:Flask数据库扩展Flask-SQLAlchemy
0 0
Python编程:Flask表单扩展Flask-WTF
Python编程:Flask表单扩展Flask-WTF
0 0
+关注
oneapm_official
分享技术干货,解决性能问题,塑造品牌力量!
文章
问答
文章排行榜
最热
最新
相关电子书
更多
给运维工程师的Python实战课
立即下载
Python 脚本速查手册
立即下载
ACE 区域技术发展峰会:Flink Python Table API入门及实践
立即下载