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 �򸫤Ƥ���������