Pythonでsqlite3を扱う

Last Update:2012/01/03

sqlite3はPython2.5から標準ライブラリに含まれるsqlite3モジュールで利用できるようになった。 サーバを準備する必要がなく、ファイル単位で管理できるので扱いやすい。

SQLite3の基本

SQLite3のメリット

  • Python2.5移行標準
  • ファイル単体でデータベースの管理ができる(コピペでバックアップできる)
  • データベース用のサーバや設定が不要

SQLite3のデメリット

  • 型が緩い(日付型も無いしTEXT型にINT型を入れてもエラーにならない)
  • アクセスのためのパスワードなどは無い
  • 排他制御がファイルロックなので多数がアクセスするような用途には向かない
  • データが多くなるとMySQLなどに比べ速度で劣る(らしい)

格納できる型は

  • INTEGER:整数 8バイトまでの1,2,4,6バイト
  • REAL: 実数 8バイトで格納
  • TEXT:文字列(utf-8)
  • BLOB: Binary Large OBject 入力をそのまま格納
  • NULL: NULL値

テーブルのカラムに指定できる型はNUMERIC、INTEGER、REAL、TEXT、NONEの5つ。

型が違っても格納できてしまうので気をつけること。

データベースの作成

connectは指定したデータベースが存在しない場合は新規作成し、存在する場合には既存のデータベースを開く。

1
2
3
4
5
6
7
8
9
import sqlite3
connection = sqlite3.connect('test.db')
cursor = connection.cursor()
cursor.execute('''
    CREATE TABLE hoge (id INTEGER PRIMARY KEY AUTOINCREMENT,
    foo TEXT(100) NOT NULL,
    bar REAL NULL);
''')
cursor.close()

データの挿入、更新

マルチバイト文字(日本語)を挿入、更新するときは必ずユニコードにして挿入する。utf-8のまま渡すとダメ。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
import sqlite3
connection = sqlite3.connect('test.db')
cursor = connection.cursor()
#プレースホルダ
sql = "INSERT INTO hoge(foo,bar) VALUES(?,?);"
fact = [u"にほんご",3,14]
cursor.execute(sql,fact)

sql = "UPDATE hoge SET foo=?,bar=? WHERE id=?;"
fact = [u"えいご",1.0,1]
cursor.execute(sql,fact)
connection.commit()
cursor.close()

データの取り出し

挿入とは逆にマルチバイト文字を取り出すときには.encode(“utf-8”)を使うこと。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import sqlite3
connection = sqlite3.connect('test.db')
cursor = connection.cursor()
sql = "SELECT * FROM hoge;"
cc = cursor.execute(sql)

#全部取り出す list型
da = cc.fetchall()

#一つ取り出す list型
#do = cc.fetchone()

for i in da:
    print("%d"%i[0]) #id
    print("%s"%i[1].encode("utf-8")) #foo
    print("%f"%i[2]) #bar
cursor.close()

添字をカラム名にして取り出したい時はcursor.row_factory = sqlite3.Rowを入れること。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
import sqlite3
connection = sqlite3.connect('test.db')
cursor = connection.cursor()
cursor.row_factory = sqlite3.Row #この一文を入れる
sql = "SELECT * FROM hoge;"
cc = cursor.execute(sql)
da = cc.fetchall()

for i in da:
    print("%d"%i["id"])
    print("%s"%i["foo"].encode("utf-8"))
    print("%f"%i["bar"])

cursor.close()

削除

単純なDELETE文。

1
2
3
4
5
6
7
import sqlite3
connection = sqlite3.connect('test.db')
cursor = connection.cursor()
sql = "DELETE from hoge where id=?;"
cursor.execute(sql,[1,])
connection.commit()
cursor.close()

排他制御/ロールバック/コミット

デフォルトの状態では、connection.commit()を行うまでデータベースの更新が行われない。 INSERTや削除に失敗した時にはロールバックを行うようにする。

1
2
3
4
if not cursor.execute("DELETE from hoge where id=1"):
    connection.rollback()
else:
    connection.commit()