PythonでSQLiteデータベースを操作する方法｜sqlite3モジュール徹底解説

---

SQLiteとは

SQLiteは、サーバーを必要とせず、設定も一切不要なオープンソースのデータベースです。データベース全体が単一のディスクファイルとして保存され、OSのファイルシステム上の任意の場所に配置できます。SQLiteのコマンドは標準SQLに近い構文を持つため、学習コストも低く抑えられます。

SQLiteは、Webブラウザなど多くのアプリケーションの内部データ保存に広く採用されており、組み込みデバイス向けのデータストレージとしても非常に有用です。

Pythonの標準ライブラリには、SQLiteデータベース接続のサポートが最初から組み込まれています。その中核となるのがsqlite3モジュールで、Gerhard Häring氏によって開発されたDB-API 2.0準拠のモジュールです。追加インストールなしで、すぐに使い始められる点が大きな魅力です。

DB-APIはPEP 249に基づいて定義されており、さまざまなデータベース製品へアクセスするPythonモジュール間で共通のインターフェースを保証しています。これにより、コードの移植性が高まります。

データベース操作の基本的な流れは以下の通りです。

1. データベースを表すコネクションオブジェクトを取得する
2. コネクションからカーソルオブジェクトを取得する
3. カーソルを使ってSQLを実行し、結果を処理する

カーソルオブジェクトはイテレータに似た概念で、クエリ結果の各行を順番に処理するために使われます。行の集合の中の1行を指す「ポインタ」と捉えると理解しやすいでしょう。

connect()関数でデータベースに接続する

sqlite3モジュールのconnect()関数は、ディスク上の既存データベースを表すコネクションオブジェクトを返します。ファイルが存在しない場合は、新しくデータベースを作成します。

import sqlite3
conn = sqlite3.connect('mydb.sqlite3')

また、SQLiteはメモリ上にデータベースを作成することもできます。特別な名前「:memory:」を指定すると、ディスクにファイルを作らず、RAM内だけで動作する一時的なデータベースが生成されます。テストや一時的な計算用途に便利です。

conn = sqlite3.connect(':memory:')

コネクションオブジェクトの主なメソッド

Connectionクラスには、以下のようなメソッドが定義されています。

cursor()

カーソルオブジェクトを返します。SQL文の実行は、このカーソルを通じて行います。

cur = conn.cursor()

commit()

トランザクションを確定し、変更内容を永続的にディスクに書き込みます。

rollback()

トランザクションをロールバックし、最後にcommit()を呼び出した時点までデータベースの状態を復元します。SQL処理中に例外が発生した場合のデータ整合性を保つために重要なメソッドです。

try:
    conn.commit()
except:
    conn.rollback()

execute()

内部でカーソルを暗黙的に作成し、そのexecute()メソッドを呼び出します。

executemany()

内部でカーソルを暗黙的に作成し、そのexecutemany()メソッドを呼び出します。複数行の一括処理に適しています。

create_function()

ユーザー定義関数を作成します。登録した関数は、後からSQL文の中で呼び出せるようになります。

create_aggregate()

集計関数（aggregate function）を作成します。

iterdump()

データベースの内容をSQLテキスト形式でダンプします。バックアップやマイグレーションに活用できます。

import sqlite3
con = sqlite3.connect('mydb.sqlite3')
f = open('dump.sql', 'w')
for line in con.iterdump():
    f.write('%s\n' % line)

backup()

アクセス中のデータベースであっても、バックアップを作成できます。サービスを停止せずにバックアップを取れる点が強力です。

source = sqlite3.connect('mydb.sqlite3')
dest = sqlite3.connect(':memory:')
source.backup(dest)

カーソルオブジェクトでSQLを実行する

コネクションから取得したカーソルを使うと、execute()メソッドを通じて各種SQL操作を実行できます。例えば、次のステートメントは現在のデータベースにテーブルを作成します。

cur = conn.cursor()
cur.execute("""CREATE TABLE guests (
    ID INTEGER PRIMARY KEY,
    name TEXT(20) NOT NULL,
    address TEXT(40),
    city TEXT(20)
);""")

レコードを挿入する場合は以下のように記述します。

cur.execute("""INSERT INTO GUESTS VALUES(1,'RAJU','ABIDS','HYDERABAD');""")

大量のレコードをまとめて追加したい場合は、executemany()メソッドを使うと効率的です。

addresses = [(2, 'KISHAN', 'TILAK ROAD', 'PUNE'),
             (3, 'LATA', 'GAANDHI NAGAR', 'AURANGABAD')]
sql = 'INSERT INTO GUESTS VALUES (:ID, :NAME, :ADD, :CITY)'
cur.executemany(sql, addresses)

検索結果の取得：fetchone()とfetchall()

SELECTクエリを実行すると、結果セット（result set）が形成されます。execute()メソッドにはSELECT文を表す文字列を渡します。DB-APIでは、カーソルが保持する結果セットから行を取り出すためのメソッドが2つ用意されています。

fetchone() ─ 結果セットから次に利用可能なレコードを1件だけ取得します。戻り値は、取得したレコードの各カラム値を格納したタプルです。

fetchall() ─ 残りのすべてのレコードをタプルのリストとして取得します。各タプルが1行に対応し、テーブルの各カラム値を含みます。

fetchone()を使ってguestsテーブルの全レコードを表示するコード例です。

sql = "SELECT * from guests;"
cur = db.cursor()
cur.execute(sql)
while True:
    record = cur.fetchone()
    if record == None:
        break
    print(record)

fetchall()の使用例は以下の通りです。

sql = "SELECT * from guests;"
cur = db.cursor()
cur.execute(sql)
rows = cur.fetchall()
for record in rows:
    print(record)

SQLiteとPythonの型マッピング

SQLiteのデータ型は、デフォルトで以下の表のようにPythonの対応するデータ型へ自動的にマッピングされます。

Python型	SQLite型
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

この自動変換により、PythonのオブジェクトをそのままSQLに渡したり、クエリ結果をPythonオブジェクトとして受け取ったりできるため、コードがシンプルになります。

DB-APIで定義されている例外

DB-APIは、SQLiteデータベースに対するSQL操作に関連して、以下の例外を定義しています。

DatabaseError	データベースに関するエラー全般に対して送出される例外です。
IntegrityError	外部キー制約違反など、データベースのリレーショナル整合性が損なわれる場合に送出される例外です。DatabaseErrorのサブクラスです。
ProgrammingError	テーブルが見つからない・既に存在する、SQL文の構文エラー、パラメータ数の不一致など、プログラミング上の誤りに対して送出される例外です。
OperationalError	データベースの動作に関連し、必ずしもプログラマの制御下にあるとは限らないエラー（接続断、タイムアウトなど）に対して送出される例外です。
NotSupportedError	そのデータベースがサポートしていないAPIやメソッドが使用された場合に送出される例外です。