Python库和命令行工具:sqlite-utils,用于构建SQLite数据库

发表时间: 2019-03-16 08:29

sqlite-utils是我在过去六个月中一直在构建的Python库和命令行工具的合集,旨在尽可能快速简便地创建新的SQLite数据库

它是我的Datasette项目构建的工具生态系统的一部分。

我花了整个周末为它添加各种令人兴奋的命令行选项,现在已经准备完毕。

一个用于快速创建数据库的python库

Datasette背后的一个核心思想是,SQLite是发布各种有趣的结构化数据的理想格式。DataSette接收任意SQLite数据库,并添加可浏览的Web界面、JSON API以及将表和查询导出为CSV的功能。

这个程序的另一半是创建SQLite数据库的工具。 csvs-to-sqlite是我第一次尝试搭建命令行工具,而sqlite-utils采用更加灵活和全面的方法。

我从去年开始研究sqlite-utils,作为我的项目Analyze US Election Russian Facebook Ads的一部分。 最初的目标是构建一个可以提高在Jupyter notebook中构建新的SQLite数据库效率的工具库。

该库背后的核心思想是,您可以为其提供一个Python字典列表(相当于JSON对象),它将自动创建具有正确模式的SQLite表,然后将这些项插入到新表中。

为了进一步说明,让我们使用这个由NASA发布的陨石坠落数据JSON文件创建一个数据库(通过由Justin Dorfman策划的awesome-json-datasets发现)。

以下是将数据快速存入数据库的代码实现:

这两行代码在磁盘上创建了一个新的sqlite数据库,名为meetites.db,在该文件中创建了一个名为meetites的表,根据输入的数据检测必要的列,插入所有行并将id列设置为主键。

要查看生成的数据库,请运行命令datasette meteorites.db并浏览地址:http://127.0.0.1:8001/。

使用这个工具库还可以做更多的事情。 您可以批量创建表,插入和更新数据,配置外键关系,配置SQLite全文搜索等等。 我您通过查阅文档以获取所有详细信息。

sqlite-utils命令行工具

这是过去几天完成的一个很有趣的新工具。

首先从PyPI安装该工具库,使用该命令 : pip3 install sqlite-utils

让我们先下载一份我在Analyzing US Election Russian Facebook Ads项目中创建的数据库russian-ads.db的副本(4MB):

我们可以使用tables命令查看数据库中的表及其计数列表:

默认情况下,sqlite-utils命令将输出格式化的JSON数据。 您可以使用--csv选项获取CSV:

或者,如果您想要一个漂亮的ASCII艺术表,请使用--table(或快捷方式-t):

表格视图是建立在tabulate之上的,它提供了几十种表格变化。运行sqlite utils tables--help命令以获取完整的列表,运行try--table-fmt=rst获取可以直接粘贴到reStructuredText文本中的输出(用于编写文档)。

到目前为止,我们只看了一个表格列表。 让我们运行一个SQL查询:

同样,这里可以使用--csv输出为CSV,或者使用--table输出为表格。

默认的JSON输出是包装在数组中的对象。 使用--arrays来获取数组。 更有趣的是: --nl会以带换行符分隔的JSON格式输出,如下所示:

对于传输到其他工具来说,这是一种非常有趣的格式。

在命令行上通过JSON创建数据库

sqlite-utils insert命令可用于通过将JSON或CSV直接传输到工具中来创建新表。 它和我之前演示的Python函数中的.insert_all是相同的。

以下是如何直接从命令行创建陨石相关数据库:

这将使用名为meteorites.db的SQLite数据库文件(如果它尚不存在则创建一个),创建或使用名为meteorites的表并从中的标准输入(通过管道)读取数据。 您可以使用文件名替换 "-" 来从磁盘上的文件中读取数据。

insert命令可接受多种格式,默认情况下它会接受JSON数组对象,但是可以使用--nl接受换行分隔的JSON格式的数据,使用--csv接受csv格式的数据。

这意味着您可以组合这些工具!让我们通过从旧数据库中导出数据来创建一个全新的数据库,使用换行分隔的JSON作为中间格式:

这将创建一个名为veterans.db的新文件,其中包含一个广告表,其中只包含在正文中某处提到退伍军人的广告。

由于我们使用的是JSON,所以我们可以在组合中引入其他命令行工具。

jq是一个简洁的小工具,可以使用它自己的小众语言从JSON文件中提取数据。

诺贝尔奖API提供了一个JSON文件,其列出了所有诺贝尔奖获得者,但它们作为一个数组包含在一个最外层的“获奖者”键中。sqlite utils需要一个一维数组,因此我们可以使用jq来获取:

现在我们成功获得一个名为nobel.db的文件,其中包含所有的诺贝尔奖获得者。

最近Datasette增强了导出换行分隔的JSON的能力,因此我们还可以使用此功能直接从数据集使用数据。让我们来看看鲍勃罗斯所作的《绘画的乐趣》中有海滩的每一集:

更多功能

与Python API一样,sqlite-utils命令行工具还有许多其他选项和大量的文档。

我非常喜欢围绕数据集建立一个工具生态系统。sqlite utils是这里的重点:它是我正在构建的其他工具的基础,例如db-to-sqlite(它可以将任何sqlAlchemy支持的数据库直接导出到磁盘上的sqlite文件)。

我发现自己越来越倾向于使用sqlite作为进行各种临时分析的首选,我也很高兴尝试使用sqlite-utils的这些新命令行能力来进行真实的数据搜索任务。

发布于2019年2月25日 上午3:29 在Twitter上关注@simonw

英文原文:https://simonwillison.net/2019/Feb/25/sqlite-utils/
译者:黄叶飞舞