使用嵌入式 Python 创建存储过程

Python 已成为世界上使用最广泛的编程语言（来源：https://www.tiobe.com/tiobe-index/），SQL 作为数据库语言继续引领潮流。 Python 和 SQL 一起工作以提供 SQL 单独无法提供的新功能不是很好吗？毕竟，Python 拥有超过 380,000 个已发布的库（来源：https://pypi.org/），它们具有非常有趣的功能，可以在 Python 中扩展您的 SQL 查询。本文详细介绍了如何使用嵌入式 Python 在 InterSystems IRIS 数据库中创建新的 SQL 存储过程。

用作示例的 Python 库

本文将使用两个非常有用的库：Geopy 和 Chronyk。

Geopy 是一个用于将地理编码（地址和地理坐标的限定）应用于地址数据的库。有了它，就可以从街道名称中获取邮局格式的邮政编码和完整地址。非常有用，因为许多记录都有地址。

Chronyk 用于使用人类语言处理日期和时间。这非常有用，因为在内部，对于 IRIS 和 Python，日期是一个数字，表示自初始日期以来经过的时间量。对于人类来说，日期是 7 月 20 日，或者昨天，或者明天，或者两个小时前。 Chronyk 接受接收这样的日期，然后将其转换为通用日期格式。

InterSystems IRIS 中的 Python 支持

从 2021.1 版开始，可以使用 Python 以双向方式在 Python 和 IRIS (ObjectScript) 之间创建类方法、存储过程、互操作产品和Native调用。我不知道有任何其他数据平台可以如此深入地使用 Python。这个工作的要求是 Python 安装在与 IRIS 相同的物理或虚拟机或容器上。 更多详情请见：https://docs.intersystems.com/iris20221/csp/docbook/DocBook.UI.Page.cls?...。
对于安装 python 运行：

Python 库支持 InterSystems IRIS

为了使 InterSystems IRIS 能够使用 Python 库，必须将其安装在 <installdir>/mgr/python 中。其中 installdir 是安装 IRIS 的文件夹。要安装新包运行：

Pip3 是 Python 最受欢迎的包管理器和安装程序 Pip。

Python语言创建存储过程

在 InterSystems IRIS 中使用 Python 的一种可能性是使用 Python 创建存储过程。有两种可能性：

1. Python使用Create Function or Procedure的SQL语句创建存储过程；
2. 使用 sqlProc 和 language=Python 标记在 ObjectScript 类中创建 ClassMethod。

Python使用Create Procedure的SQL语句创建存储过程

根据 InterSystems 文档，您还可以通过在 CREATE 语句中指定 LANGUAGE PYTHON 参数，使用嵌入式 Python 编写 SQL 函数或存储过程，如下所示（来源：https://docs.intersystems.com/iris20221/csp/ docbook/DocBook.UI.Page.cls?KEY=AEPYTHON#AEPYTHON_runpython_sql):

CREATE FUNCTION tzconvert(dt TIMESTAMP, tzfrom VARCHAR, tzto VARCHAR)
    RETURNS TIMESTAMP
    LANGUAGE PYTHON
{
    from datetime import datetime
    from dateutil import parser, tz
    d = parser.parse(dt)
    if (tzfrom is not None):
        tzf = tz.gettz(tzfrom)
        d = d.replace(tzinfo = tzf)
    return d.astimezone(tz.gettz(tzto)).strftime("%Y-%m-%d %H:%M:%S")
}

当您运行这个新的 SQL 函数时：

SELECT tzconvert(now(), 'US/Eastern', 'UTC')

该函数返回如下内容：

2022-07-20 15:10:05

使用 sqlProc 和 language=Python 标记在 ObjectScript 类中创建 ClassMethod

我承认我最喜欢这种方法：使用 sqlProc 和 language=Python 标记创建一个 ClassMethod。在我看来，它更容易维护，文档更好，明显并且源代码版本管理更好。对于这种方法，我发布了一个示例应用程序：https://openexchange.intersystems.com/package/Python-IRIS-SQL-Procedures...。我将用它来详细演示第二种方法。

示例应用程序安装

要安装示例应用程序，请按照以下步骤操作：

1. clone/git 将 repo 拉入任何本地目录

$ git clone https://github.com/yurimarx/iris-sql-python-sample.git

2. 在此目录中打开一个 Docker 终端并运行：

$ docker-compose build

3. 运行 IRIS 容器：

$ docker-compose up -d

另一种安装可能性是使用 ZPM：

zpm "install iris-sql-python-sample"

使用 Python 的存储过程示例

第一个示例是处理地址地理编码的存储过程，请参阅源代码：

看到使用 [Language = python, SqlProc] 标记声明了一个 ClassMethod（在 dc.pythonsql.Company 类中）。
SqlName 标签允许在 SQL 语句中为新的存储过程设置一个名称。

转到管理门户，系统 > SQL 并运行以下代码：

SELECT 
ID, City, Name, State, Street, Zip, dc_pythonsql.GetFullAddress(Street, City, State) As FullAddress 
FROM dc_pythonsql.Company

现在不完整的地址返回为“完整”地址（完整且合格）。

注意：如果没有返回执行#class(dc.pythonsql.Company).CreateFiveCompanies()。它将创建五家公司用于测试。

该软件包可以与主要的开放和市场地理编码服务一起使用。在此示例中，我们使用开放服务 Nominatim，但也可以使用 Bing、Google、ArcGIS 等。在 https://geopy.readthedocs.io/en/stable/#module-geopy.geocoders 上查看可能性。

第二个例子是人性化格式的日期和时间包，Chronyk。

它允许您发送“明天”、“昨天”、“4 小时后”、“2022 年 7 月 4 日”等句子，并以通用日期格式获取结果。查看存储过程的创建：

在管理门户 > 系统 > SQL 中执行以下调用：

SELECT 
ID, City, Name, State, Street, Zip, dc_pythonsql.GetHumanDate('yesterday') As Datetime      
FROM dc_pythonsql.Company

如果你只想调用存储过程，你可以使用这个 SQL 语句：

select dc_pythonsql.GetHumanDate('yesterday') as Datetime

该库具有多种人性化日期和时间的可能性，请参阅 https://github.com/KoffeinFlummi/Chronyk。

所以，很容易创建 Python 存储过程，尽情享受吧！