JupyterLab 中的 SQL 接口

Jupyter Notebooks 是任何数据科学工作流程中不可或缺的一部分，甚至像 Netflix 这样的组织也离不开它们。基于浏览器的计算环境，加上可复现实验的文档格式，使其成为全球数百万数据科学家和研究人员的事实标准之选。Jupyter 拥有一个来自全球几乎每个国家的庞大国际社区，社区本身也开发了大量内核。

Jupyter Lab 是 Jupyter 项目的下一代用户界面，提供了经典 Jupyter Notebook 中熟悉的组件，如笔记本、终端、文本编辑器、文件浏览器、丰富的输出等。但与经典笔记本不同，这些功能都在一个灵活而强大的用户界面中提供。Jupyter Lab 的基本理念是把经典笔记本中的所有构件，再加上一些新内容，汇聚到同一处。

JupyterLab 展示其工作区：笔记本、文本文件、终端和笔记本输出都可以彼此交互。

我们先在系统上安装并运行 Jupyter Lab。

安装

可以使用 conda, pip 或 pipenv 安装 JupyterLab。

conda install -c conda-forge jupyterlab
or
pip install jupyterlab
or
pipenv install jupyterlab
pipenv shell
技术支持

更多详情请参阅官方安装文档。

启动 JupyterLab

您可以在控制台中直接输入以下命令来启动 Jupyter：

jupyter lab
技术支持

JupyterLab 会自动在浏览器中打开，界面类似下图。这意味着一切就绪，您可以开始使用了。

主工作区是实际开展工作的地方。它包含笔记本、文档、控制台、终端等。双击或将文件拖到此区域即可开始工作。

JupyterLab 的 SQL 接口

多数情况下，我们处理的数据存储在称为数据库的文件中。数据科学家的一个重要任务，就是能够从数据库中访问数据并进行分析。因此，在 SQL 数据库与 Jupyter Notebook/Lab 之间建立无缝接口，是一个让数据访问与操作更轻松高效的好主意。该接口可以通过两种方式实现：

1. 使用 IPython SQL Magic 扩展

Magic 命令是一组方便的函数，旨在解决标准数据分析中的一些常见问题。您可以通过 %lsmagic 查看所有可用的 magic。

IPython SQL magic 扩展使您可以直接在代码单元中编写 SQL 查询，并将结果直接读入 pandas DataFrame（来源）。它既适用于传统笔记本，也适用于现代 Jupyter Lab。

在笔记本中安装 SQL 模块

 !pip install ipython-sql
技术支持

加载 SQL 模块

 %load_ext sql
技术支持

上述 magic 命令会加载 ipython-sql 扩展。我们可以连接任何由 SQLAlchemy 支持的数据库。这里我们将连接到一个 SQLite 数据库。在代码单元中输入以下命令：

%sql sqlite://
技术支持

如果输出为 ‘Connected: @None'，则表示连接已建立。

创建数据库

最后，我们创建一个名为 EMPLOYEES 的演示表来展示功能。

%%sql
CREATE TABLE EMPLOYEE(firstname varchar(50),lastname varchar(50));
INSERT INTO EMPLOYEE VALUES('Tom','Mitchell');
INSERT INTO EMPLOYEE VALUES('Jack','Ryan');技术支持

现在我们可以在数据库上执行查询。

 %sql SELECT * from EMPLOYEE;
 ----------------------------------
 * sqlite://
 Done. 技术支持

上述查询会输出如下表格。

使用现有数据库

我们也可以使用 magic 函数连接到现有数据库。本教程将使用 SQL_SAFI 数据库（“非洲农户主导灌溉（SAFI）”数据库）。“SAFI 项目是一项研究坦桑尼亚和莫桑比克农民所用耕作与灌溉方法的科研项目。该数据集由与家庭和农业相关的调查数据构成”（来源）。您可以从此处下载。

 #Specifying the path of the database
 %sql sqlite:////Users/Parul/Desktop/SQL_SAFI.sqlite
 -----------------------------------------------------------

 'Connected: @/Users/Parul/Desktop/SQL_SAFI.sqlite' 技术支持

上述语句打开了位于路径 Users/Parul/Desktop 下、名为 SQL_SAFI.sqlite 的数据库。让我们选择 Crops 表并显示其内容。

%sql select * from Crops
技术支持

显示 Crops 表的前几行

 %sql select * from Crops where D_curr_crop = "maize" AND ID <= 3;技术支持

结果也可以按如下方式转换为 pandas DataFrame：

result = %sql select * from Crops where D_curr_crop = "maize" AND ID <= 3;
dataframe = result.DataFrame()技术支持

dataframe.info()技术支持

2. jupyterlab-sql 扩展

在继续之前，先简单介绍一下 Jupyter 扩展。

JupyterLab 扩展

扩展是 Jupyter Lab 最有用的功能之一，能够增强使用体验。Jupyter Lab 本质上就是一个可扩展的环境，笔记本、终端和编辑器都作为扩展存在。

“JupyterLab 扩展是 npm 包（JavaScript 开发中的标准包格式）”（来源）。扩展通过提供许多有用选项（如新主题、查看器、键盘快捷键、高级设置等）来定制和增强 JupyterLab 体验。GitHub 上有许多社区开发的扩展。您可以搜索 GitHub 主题 jupyterlab-extension 来查找扩展。

要安装 JupyterLab 扩展，您需要先安装 Node.js，可以从其官方网站安装，或按以下方式安装。

conda install -c conda-forge nodejs
or
brew install node #for MacOS users技术支持

jupyterlab-sql 扩展是一个很有用的扩展，可为 Jupyter Lab 添加 SQL 用户界面。它有两个主要优点：

可以通过简单的点选来探索 SQL 表。
也可通过自定义查询修改和读取表。

您可以在其官方GitHub 仓库了解更多信息。

安装

要安装 jupyterlab-sql，按顺序运行以下命令：

pip install jupyterlab_sql
jupyter serverextension enable jupyterlab_sql --py --sys-prefix
jupyter lab build
技术支持

随后需要重启所有正在运行的 Jupyter 服务器。

此外需要注意，jupyterlab-sql 仅适用于 Python 3.5 及以上版本。

快速上手

重启 Jupyter Lab 后，启动器中会出现一个新的 SQL 图标。

这意味着一切安装正确，您可以将实例连接到数据库了。

连接

要连接数据库，您需要一个有效的数据库 URL。jupyterlab-sql 已在 SQLite、PostgreSQL 和 MySQL 数据库上进行了广泛测试（来源）。请遵循 SQLAlchemy 的 URL 指南。URL 必须是数据库 URL，例如：

postgres://localhost:5432/postgres
postgres://username:password@localhost:5432/postgres
mysql://localhost/employees
sqlite://
sqlite:///myfile.db技术支持

让我们连接之前使用过的现有 SQL_SAFI 数据库。点击启动器中的 SQL 图标，输入数据库 URL，按下 enter 进行连接。

数据库一连接成功，您就可以查看库中的所有表。

接下来，我们还可以编写自定义 SQL 查询，从表中获取所需数据。

结论

将 SQL 与 JupyterLab 结合使用，可以将数据分析提升到新水平。jupyter-sql 接口让您非常容易地将 SQL 服务器连接到 Jupyter 生态系统，并将数据直接提取到其中，而无需离开 Jupyter 界面。

如果您想进一步学习 SQL，请参加 DataCamp 的 Data-Driven Decision Making in SQL 课程。

查看我们的 Remote Python and R in SQL 教程。

主题

SQL 数据科学

主题

技术