pypyodbcを使って手っ取り早くAS/400からデータを取得してみた

永らく(3か月くらい) IBM client solution の「SQL scriptの実行」を使ってSQLの発行・データ取得を行っていましたが、データ件数が5000件を超えたあたりから、全件取得するために延々スクロールさせるのが面倒になってきたので、PythonとODBCを利用して一括取得の実験をしてみました。

実験コードですので、エラーチェックや出力結果のフォーマットは度外視しています。

実験環境

PC環境

• Windows10
• python 3.8
• pypyodbc 1.3.4
• IBM i Access Client Solutions 1.1.0 (ODBCドライバ)

接続先環境

• AS400 V7R3

準備

Pythonの取得

python.org から最新安定板を取得してインストールします。
インストール後、"python.exe"のあるフォルダにパスを通しておきます。

pypyodbcの取得

なぜ、一般的なpyodbcではなく、pypyodbcにしたかといえば、pipでのインストールに失敗したからです。IBM推奨の ibm_db も試してみましたが、両者共にMicrosoft Visual C++14 を要求された為、今回はパスです。

python添付の"pip"を使用してインストールします。

pip install pypyodbc

IBM i Access Client Solutions の取得

数か月前に設定しましたので、内容はうろ覚えです。
https://www.ibm.com/support/pages/ibm-i-access-client-solutions にアクセスします。
"Downloads for IBM i Access Client Solutions"と書かれたリンクを辿ります。途中でIBM ID の認証を求められますので、IDのある方は入力、無ければ新規登録(無償)します。
"ACS Windows App Pkg English (64bit)" をクリックしてダウンロードします。

インストーラ形式になっていますので、インストーラの手順に従ってインストールします。

テストコード

odbctest.py

import pypyodbc

# 接続情報取得
config = {}
with open("connection_config.txt", 'r', encoding="utf-8") as conf:
    for line in conf.read().splitlines():
        key_, val_ = line.replace(" ", "").split("=")
        config[ key_ ] = val_

# DB接続
connection = pypyodbc.connect(
    driver='{iSeries Access ODBC Driver}',
    system = config["system"],
    uid = config["uid"],
    pwd = config["pwd"]                    )
cur = connection.cursor()

# SQL実行
statement = open("statement.sql", 'r', encoding="utf-8").read()
cur.execute( statement )
for row in cur:
    print( row )

ソースは大きく3パートに分かれています。

「接続情報取得」ではデータベースへの接続情報を外部ファイルから取り込んで、辞書に保管しています。外部ファイル "connection_config.txt"は以下のようになっています。

connection_config.txt

system = xxx.xxx.xxx.xxx
uid    = USER
pwd    = PASWORD

「DB接続」では読み込んだ接続情報を使用してデータベースへの接続を試み、カーソルを取得します。

「SQL実行」では、外部ファイルに書き込んだSQLを読み込んで、カーソルの execute()に渡してSQLを発行します。
その下の for ループで1行ごとにprint()関数に投げ込んで標準出力に書き込みます。

実行結果

statement.sql

select * from QSYS2.LIBLIST

statement.sqlに上記のSQLを記述して実行してみましょう。

>python testodbc.py
(1, 'QSYS', 'QSYS', 'SYSTEM', 0, '\x0eäýäþämäwäáäÝäbäJäÝäÞäì\x0f')
(2, 'QSYS2', 'QSYS2', 'SYSTEM', 0, 'CPI\x0eá¶àªäýäþämäwäáäÝäbäJäÝäÞäì\x0f')
(3, 'QHLPSYS', 'QHLPSYS', 'SYSTEM', 0, None)
(4, 'QUSRSYS', 'QUSRSYS', 'SYSTEM', 0, 'Sﾓﾍﾎｵﾃ Lｹｲﾈｱﾈﾓ ｶﾅﾈ Uﾍｵﾈﾍ')
(5, 'QIWS', 'QIWS', 'PRODUCT', 0, None)
(6, 'QGPL', 'QGPL', 'USER', 0, 'GENERAL PURPOSE LIBRARY')
(7, 'QTEMP', 'QTEMP', 'USER', 0, None)

文字化けは仕様です...。
いくつかのIBM提供オブジェクトのラベルはうまく変換してくれないようですが、普通に作ったテーブルでしたら、SQLを使用してようが、AS/400固有の機能を使用していようが、日本語も問題なく読める文字に変換してくれます。

ファイルにリダイレクトすれば、何万件の結果があろうと数秒から数十秒で終わるはずです。
サーバの性能にもよりますが、試しに、15カラム × 10000件、計算量やや多めのSQLを実行してみると、5秒程度で書込み完了しました（出力ファイルサイズ 1MB）。

これで、全結果セットの取得の為に、延々ページダウンキーを押し続ける苦行から解放されます。