Python は世界で最も使用されているプログラミング言語になり（出典: https://www.tiobe.com/tiobe-index/）、SQL はデータベース言語としての道をリードし続けています。 Python と SQL が連携して、SQL だけでは不可能であった新しい機能を提供できれば、素晴らしいと思いませんか？ 結局のところ、Python には 380,000 を超える公開ライブラリがあり（出典: https://pypi.org/）、Python 内で SQL クエリを拡張できる興味深い機能が提供されています。 この記事では、Embedded Python を使用して、InterSystems IRIS データベースに新しい SQL ストアドプロシージャを作成する方法を詳しく説明します。
サンプルとして使用する Python ライブラリ
この記事では、IRIS で SQL を扱う人にとって非常に便利な GeoPy と Chronyk という 2 つのライブラリを使用します。
Geopy は、ジオコーディング（住所と地理座標の修飾）を住所データに適用するために使用するライブラリです。 これを使用すると、通りの名前から郵便番号と完全な住所を郵便局の形式で取得することができます。 多くのレコードには住所が含まれるため、非常に便利です。
Chronyk は、人間の言語で日付と時刻を処理するために使用されます。 これは、IRIS と Python の両方において、日付は内部的に最初の日付から経過した時間を表す数字でたるため、非常に便利です。 人間の場合、日付は 7 月 20 日、または昨日や明日、または 2 時間前と表現しますが、 Chronyk では、このような日付を受け取って、ユニバーサル日付形式に変換します。
InterSystems IRIS への Python サポート
バージョン 2021.1 より、Python を使用してクラスメソッド、ストアドプロシージャ、相互運用プロダクション、Python と IRIS（ObjectScript）間の双方向のネイティブ呼び出しを作成できるようになりました。 Python とこれほど深く連携するデータプラットフォームを他に知りません。 これが機能するためには、要件として、Python が IRIS と同じ同じ物理マシンか仮想マシンまたはコンテナにインストールされている必要があります。 詳細は、https://docs.intersystems.com/iris20221/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_epython をご覧ください。 Python をインストールするには、以下を実行します。
# install libraries required for python and pip
RUNapt-get-yupdate\
&&apt-get-yinstallapt-utils\
&&apt-getinstall-ybuild-essentialunzippkg-configwget\
&&apt-getinstall-ypython3-pip
InterSystems IRIS への Python ライブラリサポート
InterSystems IRIS が Python ライブラリを使用できるようにするには、Python ライブラリが <installdir>/mgr/python にインストールされている必要があります。 installdir は IRIS がインストールされているフォルダです。 新しいパッケージをインストールするには、以下を実行します。
# use pip3 (the python zpm) to install geopy and chronyk packages
RUNpip3install--upgradepipsetuptoolswheel
RUNpip3install--target/usr/irissys/mgr/pythongeopychronyk
Pip3 は Python の最も一般的なパッケージマネージャーおよびインストーラー Pip です。
Python 言語でストアドプロシージャを作成する
InterSystems IRIS で Python を使用する可能性の 1 つは、Python を使用してストアドプロシージャを作成することです。 以下の 2 つの可能性があります。
- Create 関数またはプロシージャの SQL 文を使用したストアドプロシージャ Python の作成。
- sqlProc タグと language=Python タグを使用した ObjectScript クラス内での ClassMethod の作成。
Create プロシージャの SQL 文を使用したストアドプロシージャ Python の作成
InterSystems ドキュメントによると、以下に示すとおり、CREATE ステートメントに LANGUAGE PYTHON 引数を指定することで、Embedded 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-Sample）。 これを使用して、この 2 番目のアプローチを詳しく説明します。
サンプルアプリケーションのインストール
サンプルアプリケーションをインストールするには、以下の手順に従います。
- リポジトリを任意のローカルディレクトリに Clone/git pull します。
$ git clone https://github.com/yurimarx/iris-sql-python-sample.git
-
このディレクトリで Docker ターミナルを開き、以下を実行します。
$ docker-compose build
-
IRIS コンテナを実行します。
$ docker-compose up -d
もう 1 つのインストール方法は、ZPM を使用する方法です。
zpm "install iris-sql-python-sample"
Python を使用したストアドプロシージャのサンプル
最初の例は、住所のジオコーディングを処理するストアドプロシージャです。ソースコードを参照してください。
ClassMethodGetFullAddress(StreetAs%String,CityAs%String,StateAs%String)
<div>
<span style="color: #85a6ff;">As</span><span style="color: #d4d4d4;"> </span><span style="color: #4ec9b0;">%String</span><span style="color: #d4d4d4;"> </span><span style="color: #ffffff;">[</span><span style="color: #d4d4d4;"> </span><span style="color: #85a6ff;">Language</span><span style="color: #d4d4d4;"> </span><span style="color: #ffffff;">=</span><span style="color: #d4d4d4;"> </span><span style="color: #d4b57c;">python</span><span style="color: #ffffff;">,</span><span style="color: #d4d4d4;"> </span><span style="color: #85a6ff;">SqlName</span><span style="color: #d4d4d4;"> </span><span style="color: #ffffff;">=</span><span style="color: #d4d4d4;"> </span><span style="color: #dcdcaa;">GetFullAddress</span><span style="color: #ffffff;">,</span><span style="color: #d4d4d4;"> </span><span style="color: #85a6ff;">SqlProc</span><span style="color: #d4d4d4;"> </span><span style="color: #ffffff;">]</span>
</div>
<div>
<span style="color: #ffffff;">{</span>
</div>
<div>
<span style="color: #85a6ff;"> </span><span style="color: #85a6ff;">import</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">geopy</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">geocoders</span>
</div>
<div>
<span style="color: #85a6ff;"> </span><span style="color: #85a6ff;">from</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">geopy</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">geocoders</span><span style="color: #85a6ff;"> </span><span style="color: #85a6ff;">import</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">Nominatim</span>
</div>
<div>
<span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">geopy</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">geocoders</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">options</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">default</span><span style="color: #ade2ff;">_</span><span style="color: #ade2ff;">timeout</span><span style="color: #ffffff;"> =</span><span style="color: #d4b57c;"> </span><span style="color: #d4b57c;">7</span>
</div>
<div>
<span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">geolocator</span><span style="color: #ffffff;"> =</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">Nominatim</span><span style="color: #ffffff;">(</span><span style="color: #ade2ff;">user</span><span style="color: #ade2ff;">_</span><span style="color: #ade2ff;">agent</span><span style="color: #ffffff;">=</span><span style="color: #d4b57c;">"intersystems_iris"</span><span style="color: #ffffff;">)</span>
</div>
<div>
<span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">location</span><span style="color: #ffffff;"> =</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">geolocator</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">geocode</span><span style="color: #ffffff;">(</span><span style="color: #ade2ff;">Street</span><span style="color: #ffffff;"> +</span><span style="color: #d4b57c;"> </span><span style="color: #d4b57c;">", "</span><span style="color: #ffffff;"> +</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">City</span><span style="color: #ffffff;"> +</span><span style="color: #d4b57c;"> </span><span style="color: #d4b57c;">", "</span><span style="color: #ffffff;"> +</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">State</span><span style="color: #ffffff;">,</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">country</span><span style="color: #ade2ff;">_</span><span style="color: #ade2ff;">codes</span><span style="color: #ffffff;">=</span><span style="color: #d4b57c;">"US"</span><span style="color: #ffffff;">)</span>
</div>
<div>
<span style="color: #85a6ff;"> </span><span style="color: #85a6ff;">return</span><span style="color: #ade2ff;"> </span><span style="color: #ade2ff;">location</span><span style="color: #ffffff;">.</span><span style="color: #ade2ff;">address</span>
</div>
<div>
<span style="color: #ffffff;">}</span>
</div>
</div>
ClassMethod が [ Language = python, SqlProc] タグで（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() を実行してください。 テストに使用する 5 つの会社が作成されます。
このパッケージは、主なオープンのジオコーディングサービスと有料のサービスと連携できます。 この例では、Nominatim というオープンサービスを使用していますか、Bing、Google、ArcGIS などを使用することも可能です。 利用可能なサービスについて、https://geopy.readthedocs.io/en/stable/#module-geopy.geocoders をご覧ください。
2 つ目の例は、人間が読み取れる形式による Chronyk という日付と時刻のパッケージです。
「明日」、「昨日」、「今から 4 時間後」、「2022 年 7 月 4 日」などの文を送信し、ユニバーサル日付形式の結果を取得できます。 ストアドプロシージャの作成を参照してください。
ClassMethodGetHumanDate(SentenceAs%String)As%String[Language=python,SqlName=GetHumanDate,SqlProc]
{
fromchronykimportChronyk
t =Chronyk(Sentence)
returnt.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 ストアドプロシージャは簡単に作成できます。ぜひ試してみてください！
.png)
.png)
ObjectScriptエクステンションマークをクリックし、対象のコードを選択→エクスポート の手順でローカルにエクスポートする必要があります。
と 
で取得可能なログの取得方法をご紹介します。