使用嵌入式 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 运行:
RUN apt-get -y update \
&& apt-get -y install apt-utils \
&& apt-get install -y build-essential unzip pkg-config wget \
&& apt-get install -y python3-pip
Python 库支持 InterSystems IRIS
为了使 InterSystems IRIS 能够使用 Python 库,必须将其安装在 <installdir>/mgr/python 中。其中 installdir 是安装 IRIS 的文件夹。要安装新包运行:
RUN pip3 install --upgrade pip setuptools wheel
RUN pip3 install --target /usr/irissys/mgr/python geopy chronyk
Pip3 是 Python 最受欢迎的包管理器和安装程序 Pip。
Python语言创建存储过程
在 InterSystems IRIS 中使用 Python 的一种可能性是使用 Python 创建存储过程。有两种可能性:
- Python使用Create Function or Procedure的SQL语句创建存储过程;
- 使用 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...。我将用它来详细演示第二种方法。
示例应用程序安装
要安装示例应用程序,请按照以下步骤操作:
- clone/git 将 repo 拉入任何本地目录
$ git clone https://github.com/yurimarx/iris-sql-python-sample.git
- 在此目录中打开一个 Docker 终端并运行:
$ docker-compose build
- 运行 IRIS 容器:
$ docker-compose up -d
另一种安装可能性是使用 ZPM:
zpm "install iris-sql-python-sample"
使用 Python 的存储过程示例
第一个示例是处理地址地理编码的存储过程,请参阅源代码:
As %String [ Language = python, SqlName = GetFullAddress, SqlProc ]
{
import geopy.geocoders
from geopy.geocoders import Nominatim
geopy.geocoders.options.default_timeout = 7
geolocator = Nominatim(user_agent="intersystems_iris")
location = geolocator.geocode(Street + ", " + City + ", " + State, country_codes="US")
return location.address
}
看到使用 [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 日”等句子,并以通用日期格式获取结果。查看存储过程的创建:
{
from chronyk import Chronyk
t = Chronyk(Sentence)
return t.ctime()
}
在管理门户 > 系统 > 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 存储过程,尽情享受吧!