【実用】WindowsのPythonからSQLiteを使う

Python をインストールすると、もれなくSQLiteが付いてきます。

つまり、Pythonにとって、SQLiteは標準データベースなんですね。

しかし、Pythonの公式サイトは今一分かり難いし、かと言ってネットでググっても断片的な情報しか出てきません。

そこで、この記事では実際のアプリで使う事を前提に、必要となるメソッドと使い方について解説したいと思います。

SQLiteに関する特徴や仕様、実用上の注意点を理解しておきたい方は、こちらの記事をご一読下さい。

最初の準備
データベースの作成
1. close 処理は忘れずに
2. パスの区切り文字はスラッシュ’/’が無難
データベースの操作
データの登録／変更／削除
データの検索／集計
トランザクションモード
トランザクションの有無による速度比較

最初の準備

Pythonをインストールすると標準でSQLiteがインストールされています。

単純にインポートすればOKです。

import sqlite3

1	import sqlite3

データベースの作成

SQLiteのデータベースは単一ファイルで構成されており、データベースの作成は　connect メソッドを呼ぶだけで完了します。

sqlite3.connect(ファイル名)

このメソッドを呼ぶと、既にデータベースファイルが存在していればデータベースに接続し、存在しなければ新しくデータベースを作成してくれます。

ファイル名の代わりに ‘:memory:’ という文字列を指定すると、メモリ上にデータベースが構築されます。

いわゆるインメモリデータベースです。

メモリに余裕があり、高速な処理を行いたい時には有効ですが、close メソッドを呼ぶと内容が消えてしまいますので、あくまでも一時的な使い方に適しています。

話は変わりますが、この connect に関して2点ほど注意点があります。

close 処理は忘れずに

1つ目は、close 処理の問題です。

connect メソッドを呼ぶとコネクションクラスのインスタンスが生成されます。

一般的にはこのまま放置していても問題ありませんが、多少なりともメモリを消費してしまうため、あまり良い事ではありません。

connect メソッドを呼んだら、そのインスタンスを使って closeメソッドを呼び出すことを心がけましょう。

パスの区切り文字はスラッシュ’/’が無難

Windowsの場合、パスの区切りは ‘￥’ マークを使いますので、Pythonでも ‘D:\notepc.db’ の様に’ ￥’ マークを使ってしまいがちです。

既にデータベースが存在していれば問題ありませんが、存在しなければエラーになって作成してくれません。

Python ではスラッシュ ‘/’ も区切り文字として使えますので、こちらを使用すると問題なく新規作成してくれます。

データベースの操作

SQLiteのデータベースを操作する場合、「DBに接続」「カーソルを取得」「SQLの実行」「カーソルを閉じる」「接続を閉じる」という手順が必要です。

この章では、テーブル作成やテーブル名変更などを行うSQL、いわゆるDDL （Data Definition Language）と、テーブルへのデータの挿入／変更／削除を行う場合の２パターンについて、それぞれ解説します。

DDL（Data Definition Language）を実行する場合

DDLはトランザクションが効かないので、コミットやロールバックを行う必要はなく、省略することが可能です。

従って次のように書くことが出来ます。

カーソルを取得せず、connに実装されているexecute メソッドを使っても同じことが出来ます。

実は、conn.execute を実行するとカーソルのインスタンスが返され、メモリに残ったままになります。

一般的な使い方では問題ありませんが、前述の様に明示的にカーソルを取得して使い終わったらclose するという書き方の方が良いでしょう。

データの挿入／変更／削除を実行する場合

こちらはトランザクションを掛けることが前提となるため、コミットが必要になります。

ちなみに、Pythonの場合、特に指定しない限りconnectメソッド実行と同時にトランザクションが開始されています。

もちろん、DDLを実行した後にコミットをしても問題ないので、DDLとデータの挿入／変更／削除を連続して行わせても構いません。

ちなみに、エラーが発生したり、処理を中断した時に元に戻したい時は、conn.rollback() メソッドを使います。

データベースの作成

データベースを作成するには、sqlite3.connectメソッドを使います。

　　conn = sqlite3.connect(データベースファイル名)

テーブルの作成と削除

テーブルの作成や削除はDDLであるため、直接コネクションのインスタンスからSQLを実行できます。

ここでは、コネクションを使う方法とカーソルを使う方法の2通りについてサンプルを提示しておきます。

# data.db に pc という名前のテーブルを作成するサンプル 

conn = sqlite3.connect("D:\data.db")
cur = conn.cursor()
cur.execute('create table pc(id integer,maker text,name text,score integer,size real,memory integer,weight real,primary key(id))')
conn.rollback()
cur.close()
conn.close()

# data.db に pc という名前のテーブルを作成するサンプル

conn = sqlite3.connect("D:\data.db")

cur = conn.cursor()

cur.execute('create table pc(id integer,maker text,name text,score integer,size real,memory integer,weight real,primary key(id))')

conn.rollback()

cur.close()

conn.close()

また、テーブルの削除についても同様に次のように記述できます。

# data.db に作られた テーブル名 pc を削除するサンプル

conn = sqlite3.connect("D:\data.db")
cur = conn.cursor()
cur.execute('drop table pc')
conn.rollback()
cur.close()
conn.close()

# data.db に作られたテーブル名 pc を削除するサンプル

conn = sqlite3.connect("D:\data.db")

cur = conn.cursor()

cur.execute('drop table pc')

conn.rollback()

cur.close()

conn.close()

もちろん、テーブル名を変更する ALTER TABLE 、Viewを作成する Create Viewについても同様に２通りの手順が使用できます。

データの登録／変更／削除

テーブルに対してデータの登録／変更／削除を行う場合はトランザクションを利用します。

といっても、connect メソッドと同時にトランザクションが開始されているため、必要なSQLを実行し終えた段階で commit 又は rollback メソッドを呼び出すだけです。

言い換えると、commitを実行しないとデータの変更は行われません。

Insert

以下はインサートのサンプルです。


# pc という名前のテーブルにデータを追加するサンプル

conn = sqlite3.connect("D:/data.db")
cur = conn.cursor()
cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10000,'Dell','Inspiron 13 7000',6484,13.3,8,0.955)");
cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10001,'Lenovo','ThinkPad X380 Yoga',6196,13.3,8,1.44)");
cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10002,'マイクロソフト','Surface Laptop D9P-00039',3359,13.5,4,1.25)");
cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10003,'富士通','FMV LIFEBOOK SH75/C3',6196,13.3,4,1.36)");
cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10004,'Dynabook','dynabook GZ83/M',10326,13.3,16,0.859)");
conn.commit()
cur.close()
conn.close()

# pc という名前のテーブルにデータを追加するサンプル

conn = sqlite3.connect("D:/data.db")

cur = conn.cursor()

cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10000,'Dell','Inspiron 13 7000',6484,13.3,8,0.955)");

cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10001,'Lenovo','ThinkPad X380 Yoga',6196,13.3,8,1.44)");

cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10002,'マイクロソフト','Surface Laptop D9P-00039',3359,13.5,4,1.25)");

cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10003,'富士通','FMV LIFEBOOK SH75/C3',6196,13.3,4,1.36)");

cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values(10004,'Dynabook','dynabook GZ83/M',10326,13.3,16,0.859)");

conn.commit()

cur.close()

conn.close()

delete

下記は削除のサンプルです。

# pc という名前のテーブルから idが10000のデータを削除するサンプル

conn = sqlite3.connect("D:/data.db")
cur = conn.cursor()
cur.execute('delete from pc where id=10000')
conn.commit()
cur.close()
conn.close()

# pc という名前のテーブルから idが10000のデータを削除するサンプル

conn = sqlite3.connect("D:/data.db")

cur = conn.cursor()

cur.execute('delete from pc where id=10000')

conn.commit()

cur.close()

conn.close()

update

下記は更新のサンプルです。

# pc という名前のテーブルから maker が Dell のデータについて、score を 9999 に変更するサンプル

conn = sqlite3.connect("D:/data.db")
cur = conn.cursor()
cur.execute("update pc set score=999 where maker='Dell'")
conn.commit()
cur.close()
conn.close()

# pc という名前のテーブルから maker が Dell のデータについて、score を 9999 に変更するサンプル

conn = sqlite3.connect("D:/data.db")

cur = conn.cursor()

cur.execute("update pc set score=999 where maker='Dell'")

conn.commit()

cur.close()

conn.close()

データの検索／集計

データを取得する場合は、コミットの代わりにフェッチ(fetch～)を使います。

fetchall は条件に合致したデータを全て読み込むメソッドですが、一度にメモリに入りきらない可能性もあるため、1行又は複数行づつ取り出すメソッドが用意されています。

fetchall／fetchone／fetchmany

データの取り出し（フェッチ）メソッドは3種類用意されています。

機能	メソッド	備考
全件取得	fetchall()	リスト形式で全件を返す。
1件づつ取得	fatchone()	データがあれば1行をタプル形式で返す。データが無くなれば None を返す。
指定件数づつ取得	fetchmeny(件数)

fetchallはタプルを要素としたリスト形式で結果を返します。

#fetchallのサンプル
conn = sqlite3.connect("d:/data.db")
cur = conn.cursor()
cur.execute('select * from pc')
data = cur.fetchall()
cur.close()
conn.close()
print(data)

#fetchallのサンプル

conn = sqlite3.connect("d:/data.db")

cur = conn.cursor()

cur.execute('select * from pc')

data = cur.fetchall()

cur.close()

conn.close()

print(data)

fetchone は1行だけタプル形式で返します。

戻り値が None になるまでループすることで全件を取り出せます。

#fetchone のサンプル
conn = sqlite3.connect("d:/data.db")
cur = conn.cursor()
cur.execute('select * from pc')
while(True):
    row = cur.fetchone()
    print(row)
    if row == None:
        break


cur.close()
conn.close()

#fetchone のサンプル

conn = sqlite3.connect("d:/data.db")

cur = conn.cursor()

cur.execute('select * from pc')

while(True):

row = cur.fetchone()

print(row)

if row == None:

break

cur.close()

conn.close()

fetchmeny は fetchall と同様にタプルを要素としたリスト形式で指定件数づつ返します。

取り出すデータが無くなれば空のリスト [] を返すので、次のようなループで全件を取り出す事が出来ます。



#fetchmanyのサンプル
conn = sqlite3.connect("d:/data.db")
cur = conn.cursor()
cur.execute('select * from pc')
while(True):
    row = cur.fetchmany(2)
    print(row)
    if row == []:
        break

cur.close()
conn.close()

#fetchmanyのサンプル

conn = sqlite3.connect("d:/data.db")

cur = conn.cursor()

cur.execute('select * from pc')

while(True):

row = cur.fetchmany(2)

print(row)

if row == []:

break

cur.close()

conn.close()

カーソルから直接取り出す（イテレータ）

カーソルはイテレータなので、forループで全件を取り出すことが可能です。

fetchone よりもこちらの方がスマートに記述出来ます。

conn = sqlite3.connect("d:/data.db")
cur = conn.cursor()
cur.execute('select * from pc')
for row in cur:
    print(row)

cur.close()
conn.close()

conn = sqlite3.connect("d:/data.db")

cur = conn.cursor()

cur.execute('select * from pc')

for row in cur:

print(row)

cur.close()

conn.close()

どちらかというと、ループで1件づつ処理したい場合はカーソルを使い、先頭の1行だけ取り出したい場合は fetchone を使うのが良さそうです。

集計処理

集計処理についても基本的に結果がリスト形式で返されます。

# メモリの平均と重量の合計を集計

conn = sqlite3.connect("d:/data.db")
cur = conn.cursor()
cur.execute('select avg(memory),sum(weight) from pc')
data = cur.fetchall()
cur.close()
conn.close()

# メモリの平均と重量の合計を集計

conn = sqlite3.connect("d:/data.db")

cur = conn.cursor()

cur.execute('select avg(memory),sum(weight) from pc')

data = cur.fetchall()

cur.close()

conn.close()

select count(*) from ～　の様に値が１つしか返さない場合であっても、戻り値のフォーマットは同じです。

トランザクションモード

connect メソッドの isolation_level 引数に下記表の設定値を渡すことで、トランザクションにおけるロックの種類を指定することが出来ます。

　　sqlite3.connect(ファイル名,isolation_level=設定値)

分離レベル (isolation_level)	設定値	内容
deferred	‘DEFERRED’	読み込み処理時にSHARED ROCKを、書き込み処理時にRESERVED ROCKを取得
immediate	‘IMMEDIATE’	開始時にRESERVED ROCKを取得
exclusive	‘EXCLUSIVE’	開始時にEXCLUSIVE ROCKを取得
auto commit	None	トランザクションをしない。言い換えると、SQLを１つ実行する度に commit する。

ロックに関する詳細については、SQLite公式サイトに記載されています（但し英語です）。

下記は公式サイトの内容を簡単に要約したものです。

UNLOCKED	データベースにはロックが保持されておらず、データベースの読み取りも書き込みも不可。初期値はこれ。
SHARED	データベースは読み取り可能だが、、書き込みはできません。任意の数のプロセスが同時にSHAREDロックを保持できるため、多数の同時リーダーが存在する可能性があります。ただし、1つ以上のSHAREDロックがアクティブな間は、他のスレッドまたはプロセスがデータベースファイルに書き込むことはできません。
RESERVED	データベースファイルへの書き込みを予定しているが、現在はファイルからの読み取りのみを行っている状態。複数のSHAREDロックを1つのRESERVEDロックと共存させることはできるが、一度にアクティブにできるRESERVEDロックは1つだけ。
PENDING	ロックを保持しているプロセスができるだけ早くデータベースに書き込みを行い、現在のすべてのSHAREDロックがクリアされるのを待ってEXCLUSIVEロックを取得できることを意味します。
EXCLUSIVE	データベースファイルに書き込むには、EXCLUSIVEロックが必要。ファイルには1つのEXCLUSIVEロックのみが許可されており、他のいかなる種類のロックもEXCLUSIVEロックと共存することはできない。

トランザクションの有無による速度比較

PythonのSQLite では、sqlite3.connect の引数になにも指定しない場合、トランザクションが自動的に実行されます。

isolation_level という引数を None にすることで、トランザクションを無効にすることが可能です。　

そこで、トランザクションの有り無しでどれくらい差がでるのかを調べてみました。

ついでに、興味本位でコネクションのexecute とカーソルの execute についても速度の差があるか調べてみました。

結果は、コネクションのexecute を使った場合で242倍、カーソルのexecuteを使った場合で188倍もの差がありました。

また、コネクションのexecuteを使った方が若干早かったです。

＜トランザクション有り＞

計測条件	1000件インサート時間（10回の平均値）
コネクションのexecute トランザクション有り	0.153秒
カーソルのexecute トランザクション有り	0.198秒

＜トランザクション無し＞

計測条件	1000件インサート時間（10回の平均値）
コネクションのexecute トランザクション有り	37.17秒
カーソルのexecute トランザクション無し	37.25秒

下記が実験に使ったソースコードです。

#コネクションの execute を使った場合の速度計測

result = []
for cnt in range(10):
    conn = sqlite3.connect("D:/data.db")
    conn.execute("drop table pc")
    conn.execute('create table pc(id integer,maker text,name text,score integer,size real,memory integer,weight real,primary key(id))')
    conn.close()
    
    conn = sqlite3.connect("D:/data.db")　
    ### conn = sqlite3.connect("D:/data.db",isolation_level=None)
    t1 = time.time()
    for id in range(1000):
        conn.execute("insert into pc(id,maker,name,score,size,memory,weight) values({0},'Dell','Inspiron 13 7000',6484,13.3,8,0.955)".format(id));
    
    conn.commit()
    conn.close()
    
    t2 = time.time()
    result.append(t2-t1)

print(result)
print(sum(result)/len(result))

#コネクションの execute を使った場合の速度計測

result = []

for cnt in range(10):

conn = sqlite3.connect("D:/data.db")

conn.execute("drop table pc")

conn.execute('create table pc(id integer,maker text,name text,score integer,size real,memory integer,weight real,primary key(id))')

conn.close()

conn = sqlite3.connect("D:/data.db")　

### conn = sqlite3.connect("D:/data.db",isolation_level=None)

t1 = time.time()

for id in range(1000):

conn.execute("insert into pc(id,maker,name,score,size,memory,weight) values({0},'Dell','Inspiron 13 7000',6484,13.3,8,0.955)".format(id));

conn.commit()

conn.close()

t2 = time.time()

result.append(t2-t1)

print(result)

print(sum(result)/len(result))

#カーソルの execute を使った場合の速度計測

result = []
for cnt in range(10):
    conn = sqlite3.connect("D:/data.db")
    conn.execute("drop table pc")
    conn.execute('create table pc(id integer,maker text,name text,score integer,size real,memory integer,weight real,primary key(id))')
    conn.close()
    
    conn = sqlite3.connect("D:/data.db")

    ### conn = sqlite3.connect("D:/data.db",isolation_level=None)
    cur = conn.cursor()
    t1 = time.time()
    for id in range(1000):
        cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values({0},'Dell','Inspiron 13 7000',6484,13.3,8,0.955)".format(id));
    
    conn.commit()
    cur.close()
    conn.close()
    
    t2 = time.time()
    result.append(t2-t1)

print(result)
print(sum(result)/len(result))

#カーソルの execute を使った場合の速度計測

result = []

for cnt in range(10):

conn = sqlite3.connect("D:/data.db")

conn.execute("drop table pc")

conn.execute('create table pc(id integer,maker text,name text,score integer,size real,memory integer,weight real,primary key(id))')

conn.close()

conn = sqlite3.connect("D:/data.db")

### conn = sqlite3.connect("D:/data.db",isolation_level=None)

cur = conn.cursor()

t1 = time.time()

for id in range(1000):

cur.execute("insert into pc(id,maker,name,score,size,memory,weight) values({0},'Dell','Inspiron 13 7000',6484,13.3,8,0.955)".format(id));

conn.commit()

cur.close()

conn.close()

t2 = time.time()

result.append(t2-t1)

print(result)

print(sum(result)/len(result))