HOME Python 書き込む

sqlite3


1. 初めに

(書きかけ)

SQLite は RDMS (関係データベース管理システム) の一種です。 通常の RDMS と異なり面倒なセットアップが不要で、簡単に利用することができます。 通常のファイル並に簡単に扱えるので、 今まではテキストファイルなどに保存していたデータは sqlite に保存するようにすると便利です。

2. Python から SQLite を使う

もちろん、Python からも SQLite を利用できます。 Python 2.5 から sqlite3 が build-in package として配布されているので、 すぐに使うことができます。

次の例のように、データファイルに connect するだけで、使うことができます。 データファイルは、

また、データの保存はデータベースオブジェクトの commit() メソッドを使います。

[shopping1.py]

001:   #! /usr/local/bin/python
002:   # coding:shift_jis
003:   
004:   import sqlite3
005:   
006:   db=sqlite3.connect('shopping.db')  # データベースに接続
007:   db.execute('create table shopping(name, unit_price, number)')   # テーブルを作成
008:   db.executemany('insert into shopping(name, unit_price, number) values (?,?,?)', \
009:     [(u'ビール', 270, 5), \
010:      (u'芋焼酎', 1000, 3), \
011:      (u'かっぱえびせん', 120, 3), \
012:      (u'焼き鳥', 80, 20), \
013:      (u'ピーナッツ', 200, 3),  \
014:     ])    # テーブルにデータを挿入。executemany を使うと複数のSQL 文を実行することができる。
015:   
016:   db.commit()   # データの保存
017:   db.close()    # データベースの切断
SQL 文の実行は execute() メソッドを使います。このメソッドは、 カーソルを返すので、必要に応じて fetchall(), fetchmany(), fetchone() を使って、値を取り出します。また、for ブロックに渡すと、値が1行ずつ取り出されます。

[shopping2.py]

001:   #! /usr/local/bin/python
002:   # coding:shift_jis
003:   import sqlite3
004:   
005:   db=sqlite3.connect('shopping.db')
006:   
007:   print u'           品目\t      単価\t個数\t小計'
008:   for name, up, num, p in db.execute('select name, unit_price, number, unit_price*number from shopping'):
009:       print '%s\t%5d\t%3d\t%5d' % (u' '*(8-len(name))+name, up, num, p)        # 値を1行ずつ取り出してプリントする
010:   
011:   print '-'*45
012:   print ' ' * 40, db.execute('select sum(unit_price * number) from shopping').fetchone()[0] # fetchone を使って最初の値を取り出す
013:   db.close()

実行結果

$python shopping1.py  (テーブルを作っているので、2 回実行するとエラーになります)
$python shopping2.py
           品目       単価      個数    小計
     ビール          270     5      1350
     芋焼酎         1000     3      3000
 かっぱえびせん          120     3       360
     焼き鳥           80    20      1600
   ピーナッツ          200     3       600
---------------------------------------------
                                         6910

3. ID や 日付の取り扱い

以下はこづかい帳の例です。品目ごとに固有の ID をつけ、使った日付を YYYY-MM-DD の形式のテキストで保存します。 (SQLite のデータ型は基本的に数値とテキストしかありません)。ID は、buy_index というテーブルに保存します。

[spend1.py]

001:   #! /usr/local/bin/python
002:   # coding:shift_jis
003:   
004:   import sqlite3
005:   
006:   
007:   def insert_items(db, ls0):
008:       for name,unit_price, number,date in ls0:
009:           idx=db.execute('select id from buy_index').fetchone()[0]+1  # buy_index からインデックスを取り出し、それに1を加えます
010:           db.execute('insert into buy(id, name, unit_price, number, date) values(?,?,?,?,?)', (idx, name, unit_price, number, date))
011:           db.execute('update buy_index set id=? where 1=1', (idx,))  # buy_index を更新します
012:           
013:   if __name__=='__main__':
014:       db=sqlite3.connect('spend.db')
015:       db.execute('create table buy(id, name, unit_price, number, date)')
016:       db.execute('create table buy_index(id)')
017:       db.execute('insert into buy_index(id) values (0)')
018:       insert_items(db, [ \
019:         ('beer', 300, 3, '2007-12-27'), \
020:         ('chips', 150, 2, '2007-12-30'), \
021:         ('pizza', 1000, 2, '2007-12-31'), \
022:         ('CD-R', 40, 100, '2008-01-05'), \
023:         ('A4 paper', 5, 500, '2008-01-07'), \
024:         ('printer ink', 1000, 5, '2008-01-08'), \
025:         ])
026:       db.commit()
027:       db.close()
月ごとの出費を出力するには以下のようにします。 購入物品のリストは日付の昇順に並べます。
001:   #! /usr/local/bin/python
002:   # coding:shift_jis
003:   
004:   import sqlite3, datetime
005:   
006:   def get_ymd(s0):
007:       return tuple([int(s) for s in s0.split('-')])
008:   
009:   def days(day1, day2):
010:       u'''日付文字列を datetime.date に変換しそれを比較します'''
011:       d1 = datetime.date(*get_ymd(day1))
012:       d2 = datetime.date(*get_ymd(day2))
013:       return  0 if d1==d2   \
014:         else  1 if d1>d2    \
015:         else -1
016:   
017:   def monthly_spending(db, year, month):
018:       s='%d-%02d-%%' % (year, month)
019:       print s
020:       total=0
021:       print 'id, date, name, unit_price, number, price'
022:   
023:       # ある月の購入物品のリストを出力します。
024:       # days を使って日付を比較し昇順に並べます。
025:       for id, date, name, up, num, p in db.execute(           \
026:         'select id, date, name, unit_price, number, unit_price*number from buy where date like ? order by date collate days', (s,)):
027:           total+=p
028:           print id, date, name, up, num, p
029:       print '-'*40
030:       print ' '*35, total
031:   
032:   if __name__=='__main__':
033:       db=sqlite3.connect('spend.db')
034:       db.create_collation('days', days)   # 比較用の関数 days を定義します。
035:       monthly_spending(db, 2007, 12)
036:       monthly_spending(db, 2008, 1)
037:       db.close()
結果は以下のようになります。
$python spend1.py      (テーブルを作っているので、2 回実行するとエラーになります)
$python spend2.py
2007-12-%
id, date, name, unit_price, number, price
1 2007-12-27 beer 300 3 900
2 2007-12-30 chips 150 2 300
3 2007-12-31 pizza 1000 2 2000
----------------------------------------
                                    3200
2008-01-%
id, date, name, unit_price, number, price
4 2008-01-05 CD-R 40 100 4000
5 2008-01-07 A4 paper 5 500 2500
6 2008-01-08 printer ink 1000 5 5000
----------------------------------------
                                    11500

4. 終わりに

エディタで直接中身が見えないという点は不便ですが、そのほかの点では SQLite を使ってデータを保存したほうが 簡単だし何かと便利です。
詳しくは Python 2.5 Library Reference 13.13 sqlite3 -- DB-API 2.0 interface for SQLite databases を見てください。