« Youtubeの動画で返信機能 | トップページ | バーチャル旅行 »

2009年5月24日 (日)

[Python] Excelからsqlite3にデータを追加してみる

最近、小さいバッチ処理にはpythonをよく使います。
コードが簡潔に書けるので気に入ってます。

rubyも好きなんですが、ほんの小さい処理を作るのはpythonのほうが直感的に書けるので。日本語処理がややこしいときや、最初からちょっと大きくなるなとわかっているときはruby、一回限りのバッチはプロトタイピングにはpython、ややこしい処理でCPANにしかモジュールがないな~という場合はperlというように使い分けています。

さて、気づいたらpython関係をブログに書いてなかったのでちょっと実用的なやつ(たぶん)を一つ。

Excelのデータを読み込んで、sqlite3に追加します。
データの整形やWEBアプリ開発の下準備によくやる手なんじゃないでしょうか。

例では次のような表のエクセルデータを使用することにします。

Testxls

これを test.xlsという名前で保存しておきます。

次に、sqlite3のデータベースを用意します。

create table basket (
id       integer primary key,
category text,
name     text,
price    integer
);

を、create_table.txtという名前で保存します。

次に、

$ sqlite3 data.db < create_table.txt

をコマンドプロンプトで実行してdata.dbデータベースファイルを作成します。

プログラミングの準備。pythonでExcelのデータを操作するためのモジュールpyExceleratorをダウンロードします。

展開して

python setup.py install

で簡単にセットアップができます。

エクセルデータを読み込む部分は以下のようになります。
lights on zopeさんのスクリプトを参考にさせていただきました。

sheets = pyExcelerator.parse_xls('test.xls')
record = []
for sheet_name, values in sheets:
    if sheet_name == 'Sheet1':
        buf = []
        buf_row = 0
        for row,col in sorted(values.keys()):
             if buf_row == row:
                 buf.append(values[row,col])
             else:
                 record.append(tuple(buf))
                 buf = []
                 buf.append(values[row,col])
             buf_row = row
        record.append(tuple(buf))

もっとよい書き方があると思いますが、とりあえず動くの最優先ということで^^;
実行するとリストrecordが以下のようになります。
こんな感じになっています。

[(u'\u91ce\u83dc', u'\u306a\u3059', 100.0), (u'\u679c\u7269', u'\u308a\u3093\u3054', 98.0), (u'\u91ce\u83dc', u'\u30ad\u30e3\u30d9\u30c4', 120.0), (u'\u679c\u7269', u'\u30df\u30ab\u30f3', 80.0), (u'\u679c\u7269', u'\u3076\u3069\u3046', 300.0)]

Excelの各行がタプルで入ってます。
こうしておくと後で便利なので。

次に、sqlite3でデータを挿入する部分。

conn = sqlite3.connect('data.db')
conn.execute('delete from basket')
conn.executemany('insert into basket values(null,?,?,?)',record)
conn.commit()
conn.close()

executemany()で複数のレコードを一気に挿入します。
リストrecordを上記のようにしたのは、emecutemany()の引数にあせたためです。
ちなみに、以前 insert into basket values(?,?,?)の?の部分をnullにする値が何かわからなくてハマったことがありました。答えはNoneでした。

conn.executemany('insert into basket values(?,?,?)',[(None,1,'A'),(2,None,'B')])

とすると、None値のところはnullが挿入されるんですね。

さて、上記をくっつけた完成コードは以下のようになります。
コードは、UTF-8で保存してください。

# -*- coding: utf-8 -*-
import pyExcelerator
import sqlite3

sheets = pyExcelerator.parse_xls('test.xls')
record = []
for sheet_name, values in sheets:
    if sheet_name == 'Sheet1':
        buf = []
        buf_row = 0
        for row,col in sorted(values.keys()):
             if buf_row == row:
                 buf.append(values[row,col])
             else:
                 record.append(tuple(buf))
                 buf = []
                 buf.append(values[row,col])
             buf_row = row
        record.append(tuple(buf))

conn = sqlite3.connect('data.db')
conn.execute('delete from basket')
conn.executemany('insert into basket values(null,?,?,?)',record)
conn.commit()
conn.close()

SQLite Studioでdata.dbのbasketテーブルの中身を確認します。

Sqlitestudio

ちゃんとデータが入ってますね。

初めてのPython 第3版 Book 初めてのPython 第3版

著者:Mark Lutz
販売元:オライリージャパン
Amazon.co.jpで詳細を確認する


Pythonクィックリファレンス Book Pythonクィックリファレンス

著者:アレックス マーテリ
販売元:オライリージャパン
Amazon.co.jpで詳細を確認する

|

« Youtubeの動画で返信機能 | トップページ | バーチャル旅行 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.f.cocolog-nifty.com/t/trackback/125677/29770529

この記事へのトラックバック一覧です: [Python] Excelからsqlite3にデータを追加してみる:

« Youtubeの動画で返信機能 | トップページ | バーチャル旅行 »