Symfoware

Symfowareについての考察blog

DB2 10.5にPythonからXML列へデータを登録する(ibm_db使用)

Ubuntuにibm_dbをインストールして、PythonからDB2に接続してみました。
DB2 10.5 Express-CにUbuntu + Pythonから接続する(ibm_db使用)

今回は、XML列にデータを登録してみます。



テーブルの準備



登録のテスト用に、こんなテーブルを作成しました。


CREATE TABLE test (doc XML)






XMLを文字列として登録



SQL文にXMLな文字列を含めて登録してみます。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. sql = u"""INSERT INTO test (doc) VALUES
  7. ('<root>
  8.     <test>テストデータです</test>
  9. </root>')"""
  10. cur.execute(sql)
  11. con.commit()





あっさり登録出来ました。

ちなみに、不正なXMLを登録しようとすると、こんなエラーが発生します。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. # XMLの閉じタグが不正
  7. sql = u"""INSERT INTO test (doc) VALUES
  8. ('<root>
  9.     <test>テストデータです</test>
  10. </root_dummy>')"""
  11. cur.execute(sql)
  12. con.commit()




発生するエラーは以下のとおり。


$ python sample.py
Traceback (most recent call last):
File "sample.py", line 17, in <module>
    cur.execute(sql)
File "/usr/local/lib/python2.7/dist-packages/ibm_db-2.0.5-py2.7-linux-x86_64.egg/ibm_db_dbi.py",
 line 1335, in execute
    self._execute_helper(parameters)
File "/usr/local/lib/python2.7/dist-packages/ibm_db-2.0.5-py2.7-linux-x86_64.egg/ibm_db_dbi.py",
    line 1260, in _execute_helper
    raise self.messages[len(self.messages) - 1]
ibm_db_dbi.OperationalError: ibm_db_dbi::OperationalError
Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL10007N Message "0" could not be retrieved.
Reason code: "3". SQLCODE=-16124







プレースフォルダを使用してXML文字列を登録



SQLに直接XMLを含めるのではなく、プレースフォルダを指定して登録してみます。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. # XML
  7. xml = u"""<root>
  8.     <test>テストデータです</test>
  9. </root>"""
  10. cur.execute(u"INSERT INTO test (doc) VALUES (?)", [xml])
  11. con.commit()




エラーになるかな?と思ったのですが登録出来ました。






XML DOMを使用して登録



何かやり方があるのかもしれませんが、DOMオブジェクトを直接渡す方法を
見つけられませんでした。

toxml()でXML文字列を取得し、登録してみました。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.dom.minidom
  4. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  5. # カーソル取得
  6. cur = con.cursor()
  7. # ----- 登録用のDOMオブジェクト生成 -----
  8. # DOM 実装を取得
  9. impl = xml.dom.minidom.getDOMImplementation()
  10. # 「root」というルートノードをもつドキュメントを作成
  11. doc = impl.createDocument(None, 'root', None)
  12. # 最上位のエレメントを取得
  13. top_element = doc.documentElement
  14. # 「test」というエレメントを作成
  15. test = doc.createElement('test')
  16. # 「test」のテキストノード作成
  17. test_text = doc.createTextNode(u'テスト')
  18. # 作成したテキストノードを設定
  19. test.appendChild(test_text)
  20. # ルートノードに、作成したnode1を設定
  21. top_element.appendChild(test)
  22. cur.execute(u"INSERT INTO test (doc) VALUES (?)", [doc.toxml()])
  23. con.commit()








XMLデータの検索



これが地味に苦労しました。
どうも登録したXML列を取得すると


<?xml version="1.0" encoding="UTF-16" ?><root><test>テスト</test></root>



という文字列が帰ってきます。

xml.etree.ElementTree.fromstringを使用してXMLオブジェクトに変換しようと思ったのですが、

まず直接unicodeオブジェクトを渡すパターン。


  1. xml_str = row[0]
  2. elem = xml.etree.ElementTree.fromstring(xml_str)



エラー


UnicodeEncodeError: 'ascii' codec can't encode character u'\ufeff' in position 0: ordinal not in range(128)




utf-16に変換。


  1. xml_str = row[0]
  2. elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16'))



エラー


xml.etree.ElementTree.ParseError: not well-formed (invalid token): line 1, column 1




じゃあ、utf-16beに変換。


  1. xml_str = row[0]
  2. elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))




これで通りました。
ちなみに、「utf-16le」を指定しても正しく処理されます。何なんだ?


試行錯誤の末、登録したXMLデータのtestノードのテキストを取得する
サンプルはこんな感じになりました。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  5. # カーソル取得
  6. cur = con.cursor()
  7. cur.execute(u"SELECT doc FROM test")
  8. row = cur.fetchone()
  9. # XMLオブジェクトに変換
  10. xml_str = row[0]
  11. elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  12. print(elem.find('test').text)








Javaで登録したXMLデータの検索



DB2 10.5のXML列にJavaからデータの検索を行うサンプル
ここでXML列にJavaから郵便番号情報を登録してみました。

このデータをPython + ibm_dbで検索してみます。

xqueryを使用して、郵便番号が「0600000」のデータを検索。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""xquery
  16. for $input in db2-fn:xmlcolumn("POST_XML.ADDRESS")
  17. where ($input/doc[郵便番号='0600000'])
  18. return $input"""
  19. cur.execute(sql)
  20. for row in cur:
  21.     dic = xml_to_dict(row[0])
  22.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)






XMLCASTを使用してWHERE句を記述するパターン。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""SELECT address FROM post_xml
  16. WHERE
  17. XMLCAST(XMLQUERY('$ADDRESS/doc/郵便番号') AS CHAR(7)) = '0600000'"""
  18. cur.execute(sql)
  19. for row in cur:
  20.     dic = xml_to_dict(row[0])
  21.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)





どちらも実行結果はこんな感じになります。


$ python sample.py
北海道札幌市中央区以下に掲載がない場合







住所の一部を指定して検索



町域名に「銀座」という文字列が含まれるデータを検索してみます。

まずはxquery使用版。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""xquery
  16. for $input in db2-fn:xmlcolumn("POST_XML.ADDRESS")
  17. where fn:contains($input/doc/町域名, '銀座')
  18. return $input"""
  19. cur.execute(sql)
  20. for row in cur:
  21.     dic = xml_to_dict(row[0])
  22.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)






XMLCASTを使用した場合。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""SELECT address FROM post_xml
  16. WHERE
  17. XMLCAST(XMLQUERY('$ADDRESS/doc/町域名') AS VARCHAR(100)) LIKE '%銀座%'"""
  18. cur.execute(sql)
  19. for row in cur:
  20.     dic = xml_to_dict(row[0])
  21.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)





実行結果はこちら。


$ python sample.py
北海道夕張郡長沼町銀座
栃木県鹿沼市銀座
埼玉県熊谷市銀座
埼玉県本庄市銀座
東京都中央区銀座
富山県富山市新庄銀座
長野県岡谷市銀座
長野県岡谷市東銀座
長野県飯田市銀座
静岡県静岡市清水区銀座
静岡県熱海市銀座町
静岡県伊東市銀座元町
愛知県半田市銀座本町
愛知県刈谷市銀座
滋賀県彦根市銀座町
京都府京都市伏見区銀座町
山口県周南市銀座
山口県周南市みなみ銀座
徳島県徳島市銀座
福岡県北九州市戸畑区銀座





特に計測してなくて体感でしかないのですが、
PostgreSQLのXML列より高速な気がします。
関連記事

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/16(日) 12:27:23|
  2. DB2
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<DB2 10.5にDebian + PHP5から接続する | ホーム | DB2 10.5にPythonから画像データを登録する(ibm_db使用)>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1343-5f7161d6
この記事にトラックバックする(FC2ブログユーザー)