Pythonで5分でシステム運用に役立つレシピ

元ネタ: Pythonで5分で便利なことをするレシピ - 西尾泰和のはてなダイアリー

お正月なので(？)、挑戦してみます。

Python に限ったお話ではないですが、スクリプト言語の嬉しさを実感するのは文字列の扱いがメインのときではないでしょうか。緊急でちょっとデータの加工をしなければならない、そんなときに Python が使えるときっと嬉しい気分になると思います。

商品マスタに新商品データを今すぐ追加してほしい

データベースに商品マスタがあります。普通のアプリケーションだったら csv 取り込み機能が付いていて、大量データの取り込みをサポートしているでしょう。あるとき、csv 取り込み機能にバグが発生してデータを取り込めないというクレームがユーザから寄せられました。

システム運用を担当しているあなたは、データを頂けたらこちらで登録しますと電話で回答しますよね。そして、ユーザから新商品の csv データがメールで送られてきました。作業の納期は今日中です。

送られてきた data.csv は、商品コード、商品名、価格の3個だけでした。本当は30個ぐらいあるのでしょうが、ここでは説明のために3個しかないとします。

4909411037291,キリン生茶,115
4901085098929,伊藤園お〜いお茶,105
4901777033238,サントリーウーロン茶,90

データベースには csv データを取り込むローダープログラムも付いていたりするでしょうが、トランザクションやエラーを制御し易い SQL の方が私は好みです。ここでは csv データから insert 文を作成してみましょう。csv データには3つの要素しかありませんが、実際のデータベースの商品マスタには、発注単位(lot)とユーザID(uid)もあることが分かり、ユーザからその2つの要素は固定の値をセットするように伺いました。

商品マスタ

商品コード	商品名	価格	発注単位	ユーザID
jan	name	price	lot	uid

これらを考慮すると、insert 文は以下になります。

insert into mstitem(jan, name, price, lot, uid) values(4909411037291, 'キリン生茶', 115, 12, 99);

こんな insert 文を csv データの件数分作成する必要があります。例えば、csv データが2万件あったら手作業ではできません。そこで Python で大量の insert 文を作成するツールを作ってみましょう。前振りが長くてすみません(> <)

発注単位とユーザ ID は固定値で良いそうなのでグローバルな変数を定義しましょう。ついでに出力ファイルも定義します。

OTHER_DATA = [12, 99]
SQL_FILE = "data.sql"

Python のコーディングスタイルとしては、定数を 意図する ときは変数名を全て大文字にします(本当の定数ではありません)。

次に csv ファイルからデータを読み込み、先ほど定義した SQL_FILE へ書き込むために2つのファイルをオープンします。Python 2.5 以上で with 文でコンテキストマネージャの処理を呼び出せるようになりました。ここでは、ファイルのオープンとクローズの処理が with 文のブロックへ入るときと出るときに自動的に呼ばれます *1 。

    with open(data_file, "rb") as fr, open(SQL_FILE, "wb") as fw:
        for data in csv.reader(fr):
            data.extend(OTHER_DATA)
            fw.write(get_insert_sql(data))

csv ファイルをオープンしたファイルオブジェクト(fr)を csv.reader へ渡すことで、区切り文字(デフォルトは "," )で分割された要素を持つリストを data として受け取れます。csv ファイルには3つの要素しかなかったので残りの要素(OTHER_DATA)を data へ追加します。あとは fr.write() で出力ファイルへ書き込むときに insert 文を作成すれば良いだけですね。

呼び出し側
            fw.write(get_insert_sql(data))
...
def get_insert_sql(data):
    return "insert into mstitem(jan, name, price, lot, uid) " \
           "values({0}, '{1}', {2}, {3}, {4});\n".format(*data)

insert 文の文字列を返す関数 get_insert_sql を定義しています。文字列操作は、元記事で Python 2.6 から導入された str.format を使用する方がお奨めらしいので、それに従います。ちょっと変な引数の渡し方をしていますが、Python で可変長引数を扱う仕組みとして * や ** を付ける魔法の引数があります。標準ドキュメントの 呼び出し(call) の説明を読んでも分かり難いですが、

"values({0}, '{1}', {2}, {3}, {4});\n".format(*data)

と

"values({0}, '{1}', {2}, {3}, {4});\n".format(data[0], data[1], data[2], data[3], data[4])

は同じ振る舞いになります。* や ** 付きの引数は意味なく使うものではありませんが、ここではコーディング量を減らす目的に使っています。書き捨てのスクリプトという前提なのでまぁいいでしょう。*data は1つのリストを持つタプルになり、渡された関数で data を評価するときにそのリストの個々の要素が固定引数のように扱われます。

最終的なスクリプトは以下になります。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import csv
import sys

OTHER_DATA = [12, 99]
SQL_FILE = "data.sql"

def get_insert_sql(data):
    return "insert into mstitem(jan, name, price, lot, uid) " \
           "values({0}, '{1}', {2}, {3}, {4});\n".format(*data)

def main():
    data_file = sys.argv[1]
    with open(data_file, "rb") as fr, open(SQL_FILE, "wb") as fw:
        for data in csv.reader(fr):
            data.extend(OTHER_DATA)
            fw.write(get_insert_sql(data))

if __name__ == "__main__":
    main()

実行結果。

$ python make_insert.py data.csv
data.csv から data.sql を作成する
$ cat data.sql 
insert into mstitem(jan, name, price, lot, uid) values(4909411037291, 'キリン生茶', 115, 12, 99);
insert into mstitem(jan, name, price, lot, uid) values(4901085098929, '伊藤園お〜いお茶', 105, 12, 99);
insert into mstitem(jan, name, price, lot, uid) values(4901777033238, 'サントリーウーロン茶', 90, 12, 99);

data.sql が作成できたので SQL クライアントで商品マスタへデータを追加すれば作業完了です。

はい。5分ですね。

・・・

嘘です、私は試行錯誤したり、ドキュメント読んだりしながらで20〜30分かかりました(> <)

昔、SIer でお仕事していた頃は、こんな SQL 文を Excel の concatenate で大量に作って作業したりしていました。それでも効率はそこそこ良いのですが、マウス操作やコピペのミスをする可能性があるので集中力が必要でした。数十回ぐらいやって1回だけコピペミスをして、データのリカバリ作業で中途半端な処理になってしまい、月次の受払記録を狂わせて、周りに平謝りしたことがありました。

なるべく手作業を減らすことの重要性を知った事件でした。

追記: クエリのエスケープ処理について

@t2y クエリのエスケープがないです。重箱の隅を突いてるのは承知してるけど、5分でできることよりも安全なプログラムは重要です。

2011-01-03 09:11:10 via Echofon to @t2y

@tk0miya からご指摘を受けました。wikipedia:SQLインジェクション *2 の考慮が全く行われていないので危険なプログラムでした *3 。標準モジュールの sqlite3 でエスケープ処理が実装できないかなと調べてみたのですが、クエリのエスケープ処理のみを行う方法を見つけられませんでした *4 。

別途、インストールしないといけないのですが、MySQL-Python で MySQL のエスケープ処理の API を利用します。MySQL の文字列の扱いは MySQL :: MySQL 5.1 リファレンスマニュアル :: 8.1.1 文字列 を参考にしてください。

エスケープ処理を行うために先のスクリプトを修正します。

$ diff -u make_insert.py make_insert_kai.py 
--- make_insert.py	2011-01-03 14:30:18.000000000 +0900
+++ make_insert_kai.py	2011-01-03 14:33:42.000000000 +0900
@@ -3,6 +3,7 @@
 
 import csv
 import sys
+import MySQLdb
 
 OTHER_DATA = [12, 99]
 SQL_FILE = "data.sql"
@@ -15,6 +16,7 @@
     data_file = sys.argv[1]
     with open(data_file, "rb") as fr, open(SQL_FILE, "wb") as fw:
         for data in csv.reader(fr):
+            data = map(MySQLdb.escape_string, data)
             data.extend(OTHER_DATA)
             fw.write((get_insert_sql(data)))

実行結果。

$ cat invalid.csv 
4909411037291,キリン'生茶,115
4901085098929,伊藤園''お〜いお茶,
4901777033238,サントリ\'ーウーロン茶,90
$ python make_insert_kai.py invalid.csv 
$ cat data.sql 
insert into mstitem(jan, name, price, lot, uid) values(4909411037291, 'キリン\'生茶', 115, 12, 99);
insert into mstitem(jan, name, price, lot, uid) values(4901085098929, '伊藤園\'\'お〜いお茶', , 12, 99);
insert into mstitem(jan, name, price, lot, uid) values(4901777033238, 'サントリ\\\'ーウーロン茶', 90, 12, 99);

これで安心(？)してお正月を過ごせますねo(^ ^)o

*1:Python 2.7 から with 文でネストされたコンテキストが直接サポートされています、Python 2.6 では contextlib.nested() を使用します。詳細は ネストされたコンテキスト を参照してください。

*2:そろそろSQLエスケープに関して一言いっとくか: SQLのエスケープ再考 - 徳丸浩の日記(2008-06-01)

*3:ユーザ(ベンダ)から頂くデータは SQL で特殊な意味を持つ記号は使わないという運用が行われていたので、私の意識が低かったです。気をつけないといけませんね。

*4:sqlite3 モジュールで SQL を実行するときは Cursor.execute へ位置パラメータや名前付きパラメータを渡すと、内部的にエスケープ処理を行ってくれます。詳細は クエリで変数を使用する を参照してください。