SQLite3�ｿｽ�ｿｽ�ｿｽg�ｿｽp�ｿｽ�ｿｽ�ｿｽ�ｿｽ

SQLite3�ｿｽ�ｿｽ�ｿｽg�ｿｽp�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽy�ｿｽﾊ・�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾈデ�ｿｽ[�ｿｽ^�ｿｽx�ｿｽ[�ｿｽXSQLite�ｿｽ�ｿｽPython�ｿｽ�ｿｽ�ｿｽ辷ｵ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾟの��ｿｽ�ｿｽC�ｿｽu�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽB

�ｿｽC�ｿｽ�ｿｽ�ｿｽX�ｿｽg�ｿｽ[�ｿｽ�ｿｽ

Python2.5�ｿｽ�ｿｽ�ｿｽ�ｿｽW�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽC�ｿｽu�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾉ難ｿｽ�ｿｽ�ｿｽﾜゑｿｽ�ｿｽ�ｿｽ�ｿｽB
�ｿｽC�ｿｽ�ｿｽ�ｿｽX�ｿｽg�ｿｽ[�ｿｽ�ｿｽ�ｿｽ�ｿｽﾆは不�ｿｽv�ｿｽﾅゑｿｽ�ｿｽB

�ｿｽg�ｿｽp�ｿｽ�ｿｽ�ｿｽ@

sqlite3�ｿｽ�ｿｽ�ｿｽC�ｿｽ�ｿｽ�ｿｽ|�ｿｽ[�ｿｽg�ｿｽ�ｿｽ�ｿｽ�ｿｽ

#!python2.6
# -*- coding: utf-8 -*-
import sqlite3

�ｿｽf�ｿｽ[�ｿｽ^�ｿｽx�ｿｽ[�ｿｽX�ｿｽ�ｿｽ�ｿｽ�成�ｿｽ�ｿｽ�ｿｽ�ｿｽ

con = sqlite3.connect("data.db")

• �ｿｽt�ｿｽ@�ｿｽC�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾅに托ｿｽ�ｿｽﾝゑｿｽ�ｿｽ�ｿｽﾆゑｿｽ�ｿｽﾍフ�ｿｽ@�ｿｽC�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽJ�ｿｽ�ｿｽ�ｿｽB
• �ｿｽt�ｿｽ@�ｿｽC�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾈゑｿｽ�ｿｽﾆゑｿｽ�ｿｽﾍ新�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽf�ｿｽ[�ｿｽ^�ｿｽx�ｿｽ[�ｿｽX�ｿｽ�ｿｽ�ｿｽ�成�ｿｽ�ｿｽ�ｿｽ�ｿｽB

isolation_level�ｿｽ�ｿｽNone�ｿｽ�ｿｽ�ｿｽw�ｿｽ閧ｷ�ｿｽ�ｿｽﾆ、�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽR�ｿｽ~�ｿｽb�ｿｽg�ｿｽ�ｿｽ�ｿｽ[�ｿｽh�ｿｽﾉなゑｿｽﾜゑｿｽ�ｿｽB

con = sqlite3.connect('temp.db', isolation_level=None)

�ｿｽ�ｿｽ�ｿｽﾊな厄ｿｽ�ｿｽO�ｿｽﾅゑｿｽ�ｿｽ�ｿｽ ":memory:" �ｿｽ�ｿｽ�ｿｽg�ｿｽ�ｿｽ�ｿｽ�ｿｽRAM�ｿｽ�ｿｽﾉデ�ｿｽ[�ｿｽ^�ｿｽx�ｿｽ[�ｿｽX�ｿｽ�ｿｽ�ｿｽ�ｿｽ驍ｱ�ｿｽﾆゑｿｽ�ｿｽﾅゑｿｽ�ｿｽﾜゑｿｽ�ｿｽB

con = sqlite3.connect(":memory:")

�ｿｽf�ｿｽ[�ｿｽ^�ｿｽx�ｿｽ[�ｿｽX�ｿｽ�ｿｽﾂゑｿｽ�ｿｽ�ｿｽ

con.close()

�ｿｽe�ｿｽ[�ｿｽu�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�成�ｿｽ�ｿｽ�ｿｽ�ｿｽ

String�ｿｽ^�ｿｽﾌ「�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽv�ｿｽt�ｿｽB�ｿｽ[�ｿｽ�ｿｽ�ｿｽh�ｿｽA Integer�ｿｽ^�ｿｽﾌ「�ｿｽN�ｿｽ�ｿｽv�ｿｽt�ｿｽB�ｿｽ[�ｿｽ�ｿｽ�ｿｽh�ｿｽA String�ｿｽ^�ｿｽﾌ「�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽv�ｿｽt�ｿｽB�ｿｽ[�ｿｽ�ｿｽ�ｿｽh�ｿｽA�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾂ「�ｿｽﾐ茨ｿｽ�ｿｽv�ｿｽe�ｿｽ[�ｿｽu�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�成�ｿｽ�ｿｽ�ｿｽ�ｿｽB

sql = u"""
create table �ｿｽﾐ茨ｿｽ (
  �ｿｽ�ｿｽ�ｿｽO varchar(10),
  �ｿｽN�ｿｽ�ｿｽ integer,
  �ｿｽ�ｿｽ�ｿｽ�ｿｽ varchar(200)
);
"""
con.execute(sql)

�ｿｽg�ｿｽp�ｿｽﾅゑｿｽ�ｿｽ�ｿｽf�ｿｽ[�ｿｽ^�ｿｽ^

• Datatypes In SQLite Version 3

�ｿｽ�ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽo�ｿｽ^�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽ�ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽo�ｿｽ^�ｿｽ�ｿｽ�ｿｽ�ｿｽ

sql = u"insert into �ｿｽﾐ茨ｿｽ values ('�ｿｽ�ｿｽ�ｿｽ{', 26, '�ｿｽL�ｿｽ�ｿｽ')"
con.execute(sql)

�ｿｽv�ｿｽ�ｿｽ�ｿｽ[�ｿｽX�ｿｽz�ｿｽ�ｿｽ�ｿｽ_�ｿｽﾆバ�ｿｽC�ｿｽ�ｿｽ�ｿｽh�ｿｽl�ｿｽ�ｿｽ�ｿｽg�ｿｽp�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ鼾�

sql = u"insert into �ｿｽﾐ茨ｿｽ values (?, ?, ?)"
con.execute(sql, (u"�ｿｽ�ｿｽ�ｿｽ�ｿｽ", 35, u"�ｿｽc�ｿｽﾆ包ｿｽ"))
con.execute(sql, (u"�ｿｽT�ｿｽ�ｿｽ", 40, u"�ｿｽc�ｿｽﾆ包ｿｽ"))

�ｿｽ鼕��ｿｽo�ｿｽ^�ｿｽ�ｿｽ�ｿｽ�ｿｽ

executemany()�ｿｽ�ｿｽ�ｿｽ\�ｿｽb�ｿｽh�ｿｽ�ｿｽ�ｿｽg�ｿｽp�ｿｽ�ｿｽ�ｿｽ�ｿｽﾆ、�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽSQL�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ鼕��ｿｽ�ｿｽ�ｿｽﾄ趣ｿｽ�ｿｽs�ｿｽﾅゑｿｽ�ｿｽﾜゑｿｽ�ｿｽB

con.executemany(u"insert into �ｿｽﾐ茨ｿｽ values (?, ?, ?)",
                [(u"�ｿｽ�ｿｽ�ｿｽ�ｿｽ", 35, u"�ｿｽc�ｿｽﾆ包ｿｽ"),
                 (u"�ｿｽT�ｿｽ�ｿｽ", 40, u"�ｿｽc�ｿｽﾆ包ｿｽ")])

�ｿｽ�ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽ�ｿｽ謫ｾ�ｿｽ�ｿｽ�ｿｽ�ｿｽ

c = con.cursor()
c.execute(u"select * from �ｿｽﾐ茨ｿｽ")
for row in c: # row�ｿｽ�ｿｽtuple
    print row[0], row[1], row[2]

�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽ�ｿｽ�ｿｽ{ 26 �ｿｽL�ｿｽ�ｿｽ
�ｿｽ�ｿｽ�ｿｽ�ｿｽ 35 �ｿｽc�ｿｽﾆ包ｿｽ
�ｿｽT�ｿｽ�ｿｽ 40 �ｿｽc�ｿｽﾆ包ｿｽ

execute()�ｿｽ�ｿｽ�ｿｽ\�ｿｽb�ｿｽh�ｿｽﾍ、cursor()�ｿｽ�ｿｽ�ｿｽ\�ｿｽb�ｿｽh�ｿｽﾌシ�ｿｽ�ｿｽ�ｿｽ[�ｿｽg�ｿｽJ�ｿｽb�ｿｽg�ｿｽﾅゑｿｽ�ｿｽB

c = con.execute(u"select * from �ｿｽﾐ茨ｿｽ")
for row in c:
    print row[0], row[1], row[2]

�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽ�ｿｽ�ｿｽ{ 26 �ｿｽL�ｿｽ�ｿｽ
�ｿｽ�ｿｽ�ｿｽ�ｿｽ 35 �ｿｽc�ｿｽﾆ包ｿｽ
�ｿｽT�ｿｽ�ｿｽ 40 �ｿｽc�ｿｽﾆ包ｿｽ

�ｿｽﾊ置�ｿｽﾅはなゑｿｽ�ｿｽ�ｿｽ�ｿｽO�ｿｽﾅカ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽﾉア�ｿｽN�ｿｽZ�ｿｽX�ｿｽ�ｿｽ�ｿｽ�ｿｽ

con = sqlite3.connect("data.db")
con.row_factory = sqlite3.Row
for row in con.execute(u"select * from �ｿｽﾐ茨ｿｽ"):
    print row["�ｿｽ�ｿｽ�ｿｽO"], row["�ｿｽN�ｿｽ�ｿｽ"], row["�ｿｽ�ｿｽ�ｿｽ�ｿｽ"]

�ｿｽﾅ擾ｿｽ�ｿｽﾌ��ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽ�ｿｽ謫ｾ�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽJ�ｿｽ[�ｿｽ\�ｿｽ�ｿｽ�ｿｽ�ｿｽfetchone()�ｿｽ�ｿｽ�ｿｽ\�ｿｽb�ｿｽh�ｿｽ�ｿｽ�ｿｽﾄぶと茨ｿｽs�ｿｽ�ｿｽ�ｿｽﾂ値�ｿｽ�ｿｽ�ｿｽ謫ｾ�ｿｽﾅゑｿｽ�ｿｽﾜゑｿｽ�ｿｽB

c = con.execute(u"select * from �ｿｽﾐ茨ｿｽ")
row = c.fetchone()
print row[0], row[1], row[2]
row = c.fetchone()
print row[0], row[1], row[2]

�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽ�ｿｽ�ｿｽ{ 26 �ｿｽL�ｿｽ�ｿｽ
�ｿｽ�ｿｽ�ｿｽ�ｿｽ 35 �ｿｽc�ｿｽﾆ包ｿｽ

�ｿｽc�ｿｽ�ｿｽﾌ��ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽ�ｿｽ謫ｾ�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽJ�ｿｽ[�ｿｽ\�ｿｽ�ｿｽ�ｿｽ�ｿｽfetchall()�ｿｽ�ｿｽ�ｿｽ\�ｿｽb�ｿｽh�ｿｽ�ｿｽ�ｿｽﾄぶと残�ｿｽ�ｿｽﾌゑｿｽ�ｿｽﾗての行�ｿｽ�ｿｽ�ｿｽ謫ｾ�ｿｽﾅゑｿｽ�ｿｽﾜゑｿｽ�ｿｽB

c = con.execute(u"select * from �ｿｽﾐ茨ｿｽ")
row = c.fetchone()
print row[0], row[1], row[2]

print "---"
for row in c.fetchall():
    print row[0], row[1], row[2]

�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽ�ｿｽ�ｿｽ{ 26 �ｿｽL�ｿｽ�ｿｽ
---
�ｿｽ�ｿｽ�ｿｽ�ｿｽ 35 �ｿｽc�ｿｽﾆ包ｿｽ
�ｿｽT�ｿｽ�ｿｽ 40 �ｿｽc�ｿｽﾆ包ｿｽ

�ｿｽ�ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽ�ｿｽX�ｿｽV�ｿｽ�ｿｽ�ｿｽ�ｿｽ

#�ｿｽ�ｿｽ�ｿｽ�ｿｽﾌ包ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽL�ｿｽ�部に更�ｿｽV�ｿｽ�ｿｽ�ｿｽ�ｿｽ
con.execute(u"update �ｿｽﾐ茨ｿｽ set �ｿｽ�ｿｽ�ｿｽ�ｿｽ='�ｿｽL�ｿｽ�ｿｽ' where �ｿｽ�ｿｽ�ｿｽO='�ｿｽ�ｿｽ�ｿｽ�ｿｽ'")

�ｿｽ�ｿｽ�ｿｽR�ｿｽ[�ｿｽh�ｿｽ�ｿｽ�ｿｽ�除�ｿｽ�ｿｽ�ｿｽ�ｿｽ

# �ｿｽT�ｿｽ�ｿｽ�ｿｽ�ｿｽ�除
con.execute(u"delete from �ｿｽﾐ茨ｿｽ where �ｿｽ�ｿｽ�ｿｽO='�ｿｽT�ｿｽ�ｿｽ'")

�ｿｽg�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽU�ｿｽN�ｿｽV�ｿｽ�ｿｽ�ｿｽ�ｿｽ

�ｿｽR�ｿｽ~�ｿｽb�ｿｽg�ｿｽ�ｿｽ�ｿｽ�ｿｽ

con.commit()

�ｿｽ�ｿｽ�ｿｽ[�ｿｽ�ｿｽ�ｿｽo�ｿｽb�ｿｽN

con.rollback()

�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽR�ｿｽ~�ｿｽb�ｿｽg�ｿｽ�ｿｽ�ｿｽ[�ｿｽh

Connection�ｿｽI�ｿｽu�ｿｽW�ｿｽF�ｿｽN�ｿｽg�ｿｽ�ｿｽisolation_level�ｿｽ�ｿｽNone�ｿｽﾉゑｿｽ�ｿｽ�ｿｽﾆ、�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽR�ｿｽ~�ｿｽb�ｿｽg�ｿｽ�ｿｽ�ｿｽ[�ｿｽh�ｿｽﾉなゑｿｽﾜゑｿｽ�ｿｽB

con = sqlite3.connect("data.db")
con.isolation_level = None

�ｿｽﾜゑｿｽ�ｿｽﾍ接托ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ

con = sqlite3.connect('temp.db', isolation_level=None)

�ｿｽﾖ連

• Python �ｿｽ�ｿｽ�ｿｽC�ｿｽu�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽt�ｿｽ@�ｿｽ�ｿｽ�ｿｽ�ｿｽ�ｿｽX 13.13 sqlite3 -- SQLite �ｿｽf�ｿｽ[�ｿｽ^�ｿｽx�ｿｽ[�ｿｽX�ｿｽﾉ対ゑｿｽ�ｿｽ�ｿｽ DB-API 2.0 �ｿｽC�ｿｽ�ｿｽ�ｿｽ^�ｿｽt�ｿｽF�ｿｽ[�ｿｽX