【Python】SQLiteの基本的な使い方

軽量で優れたオープンソースのリレーショナルデータベースであるSQLiteをPythonで使用する方法について解説します。

Contents

1 SQLite
2 PythonでのSQLiteの基本的な使い方
3 まとめ

SQLite

SQLiteは、軽量で優れた優れたオープンソースのリレーショナルデータベースエンジンとして広く利用されています。SQLiteの公式ホームページは、こちらを参照してください。

SQLiteデータベースは、Python標準ライブラリのsqlite3モジュールで扱うことができるようになっています。

よく世の中の各種システムで使用されているOracle、SQLServer、MySQL、PostgreSQL等の有名なデータベースマネジメントシステム（DBMS）をご存じの方は多いと思いますが、これらの本格的なデータベースでは専用にデータ領域用ファイルを作成して動作します。

一方で、SQLiteは通常のファイルにデータベースを格納します（よく使われる拡張子は.db）。このデータベースファイルはOSの違いなどを超えてコピーして使うことができることから移植性が非常に高くなっています。

また、SQLをサポートしているため単純なリレーショナルデータベースのアプリケーションを作る際には、SQLiteは有力な選択肢の一つとなります。

本記事では、PythonでSQLiteデータベースを使う方法について説明します。

PythonでのSQLiteの基本的な使い方

SQLiteの基本操作

sqlite3を使用してSQLiteデータベースを扱うプログラムの基本的な流れは以下となります。

sqlite3をインポートする
connect関数でデータベースファイルに接続する
cursorメソッドでデータベース操作のためのカーソルを準備する
カーソルのexecuteメソッドでSQLを実行する
操作によってcommitで変更を確定する
使用後にカーソルとコネクションをクローズする

以降で、テーブル作成、データ登録、検索、更新、削除といった基本操作について例を使って説明していきます。

データベースの準備と終了

以降のサンプルプログラムで共通である、データベースの準備と終了の処理についてまず説明します。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

...(データベース処理)...

# カーソルとコネクションをクローズする
curs.close()
conn.close()

SQLiteを使う場合には、以下のようにsqlite3をインポートし、connect関数でSQLiteデータベースファイルに接続します。この時にファイルが存在すれば開かれますし、ファイルが存在しない場合はファイルが作成されます。次に、データベースの操作をするためのカーソルをcursorメソッドで生成します。

これでデータベースを操作するための準備ができ、以下で紹介する各種データベース処理を実行できます。最後に、データベースの処理が終わったら使用したカーソルと接続をcloseメソッドによりクローズします。

では、以降で具体的なデータベース操作を見ていきましょう。

テーブルを作成する（CREATE TABLE）

テーブルを作成するには、以下のように作成したカーソルのexecuteメソッドにCREATE TABLEのSQLを指定することで実行します。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

print("===== CREATE TABLE =====")
# テーブルを作成する
curs.execute(
    "CREATE TABLE IF NOT EXISTS person ("
    "id INTEGER PRIMARY KEY AUTOINCREMENT"
    ",name VARCHAR"
    ",age INTEGER)"
)
# コミットする
conn.commit()

# カーソルとコネクションをクローズする
curs.close()
conn.close()

上記処理を実行すると「test.db」というデータベースファイルが作成されます。

この例では「id」「name」「age」という属性があるpersonテーブルを作成しています。idについては、INTEGERのプライマリーキーとして設定し、自動増加するようにAUTOINCREMENTを指定しています。

また、テーブルが存在しない場合に作成されるように「IF NOT EXISTS」をつけています。この部分を除くと既にテーブルがある場合はエラーとなります。

データを追加する（INSERT）

作成したテーブルにデータを追加する場合には、executeメソッドでINSERT文を発行します。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

print("===== INSERT =====")
# プレースホルダ―を使ってデータを指定して登録する
insert_sql = "INSERT INTO person(name, age) VALUES(?, ?)"
curs.execute(insert_sql, ("TARO", 30))
curs.execute(insert_sql, ("JIRO", 20))
curs.execute(insert_sql, ("SABURO", 10))

# コミットする
conn.commit()

# カーソルとコネクションをクローズする
curs.close()
conn.close()

データを登録する場合には、以下のようにINSERT用のSQLを用意します。

# プレースホルダ―を使ってデータを指定して登録する
insert_sql = "INSERT INTO person(name, age) VALUES(?, ?)"

ここで値を設定するべき部分で「?」となっている部分をプレースホルダ―と言います。

curs.execute(insert_sql, ("TARO", 30))
curs.execute(insert_sql, ("JIRO", 20))
curs.execute(insert_sql, ("SABURO", 10))

プレースホルダーに値を渡しつつSQLを実行するには、上記のようにexecuteメソッドにプレースホルダーを含むSQL文と入力値をタプルで渡します。設定値が一つの場合も(XXX,)のようにタプルで渡すことができます。なお、INSERTでデータ登録後はcommitを忘れないようにしましょう。

システム開発では、よく使用するプレースホルダーを含むSQLを別ファイルで用意しておき、システムの各種処理では、ファイルを読み込んでSQL文字列を取得し、プログラム側は値を設定するだけにするようなことがよく行われます。

データを検索する（SELECT）

追加したデータを検索する場合には、executeメソッドでSELECT文を発行します。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

print("===== SELECT =====")
# データを検索する
select_all_sql = "SELECT * FROM person"
curs.execute(select_all_sql)
rows = curs.fetchall()
print(rows, "\n")

# 条件を指定して検索する
select_sql = "SELECT * FROM person WHERE age > ?"
curs.execute(select_sql, (20,))
rows = curs.fetchall()
print(rows)

# カーソルとコネクションをクローズする
curs.close()
conn.close()

【実行結果】
===== SELECT =====
[(1, 'TARO', 30), (2, 'JIRO', 20), (3, 'SABURO', 10)] 

[(1, 'TARO', 30)]

データを検索する場合には、以下のようにSELECT用のSQLを用意します。

# データを検索する
select_all_sql = "SELECT * FROM person"

上記はすべてのデータを取得する例ですが、もちろん以下のように条件をプレースホルダーで指定して検索することもできます。

# 条件を指定して検索する
select_sql = "SELECT * FROM person WHERE age > ?"
curs.execute(select_sql, (20,))

SELECT結果を全て取得する場合はfetchallメソッドでタプルのリストとして取得できます。また、件数を指定する場合には、fetchmanyメソッド、1件を取得する場合はfetchoneメソッドが使用できます。

rows = curs.fetchall()
print(rows)

データを更新する（UPDATE）

データを更新する場合には、executeメソッドでUPDATE文を発行します。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

print("===== UPDATE =====")
# データを更新する
update_sql = "UPDATE person SET name=? WHERE id=?"
curs.execute(update_sql, ("SHIRO", 1))

# コミットする
conn.commit()

# カーソルとコネクションをクローズする
curs.close()
conn.close()

データを更新する場合には、以下のようにUPDATE用のSQLを用意します。

# データを更新する
update_sql = "UPDATE person SET name=? WHERE id=?"
curs.execute(update_sql, ("SHIRO", 1))

プレースホルダ―で条件を指定している部分は同じです。UPDATEでデータを更新した場合、commitを忘れないようにしましょう。

データ更新後、上記で紹介したSELECTで全てのデータを検索すると以下のようにデータが更新されていることが確認できます。

[(1, 'SHIRO', 30), (2, 'JIRO', 20), (3, 'SABURO', 10)]

データを削除する（DELETE）

データを削除する場合には、executeメソッドでDELETE文を発行します。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

print("===== DELETE =====")
# データを削除する
delete_sql = "DELETE FROM person WHERE id = ?"
curs.execute(delete_sql, (2,))

# コミットする
conn.commit()

# カーソルとコネクションをクローズする
curs.close()
conn.close()

データを削除する場合には、以下のようにDELETE用のSQLを用意します。

# データを削除する
delete_sql = "DELETE FROM person WHERE id = ?"
curs.execute(delete_sql, (2,))

プレースホルダ―で条件を指定している部分は同じです。DELETEでデータを削除した場合、commitを忘れないようにしましょう。

データ削除後、上記で紹介したSELECTで全てのデータを検索すると以下のようにデータが削除されていることが確認できます。

[(1, 'SHIRO', 30), (3, 'SABURO', 10)]

プレースホルダーを使うようにしよう

上記で紹介してきた例ではプレースホルダーを使った値の指定を行ってきました。executeメソッドはSQL文のみをそのまま指定して実行することができるので、画面からの入力値を使って自分でSQL文字列を組み立てて実行することも可能です。

しかし、有名な攻撃手法であるSQLインジェクションの対策の一つとしてプレースホルダーを使うようにするのが適切です。以下の例で見てみましょう。

import sqlite3

# データベースに接続する
conn = sqlite3.connect("test.db")
# カーソルを作成する
curs = conn.cursor()

print("===== SELECT =====")
# 直接SQLを組み立てる
input_name = "SABURO"
select_sql = f"SELECT * FROM person WHERE name = '{input_name}'"
print(select_sql)
curs.execute(select_sql)
rows = curs.fetchall()
print(rows, "\n")

# 悪意ある入力の場合 (SQLインジェクション)
input_name = "SABURO' or 'A'='A"
select_sql = f"SELECT * FROM person WHERE name = '{input_name}'"
print(select_sql)
curs.execute(select_sql)
rows = curs.fetchall()
print(rows, "\n")

# プレースホルダーを使って検索する
input_name = "SABURO' or 'A'='A"
select_sql = "SELECT * FROM person WHERE name = ?"
curs.execute(select_sql, (input_name,))
rows = curs.fetchall()
print(rows)

# カーソルとコネクションをクローズする
curs.close()
conn.close()

【実行結果】
SELECT * FROM person WHERE name = 'SABURO'
[(3, 'SABURO', 10)] 

SELECT * FROM person WHERE name = 'SABURO' or 'A'='A'
[(1, 'TARO', 30), (2, 'JIRO', 20), (3, 'SABURO', 10)] 

[]

上記の例は、これまでに紹介したCREATE TABLE, INSERTの後に、実行した場合の例となります。

# 直接SQLを組み立てる
input_name = "SABURO"
select_sql = f"SELECT * FROM person WHERE name = '{input_name}'"
print(select_sql)
curs.execute(select_sql)
rows = curs.fetchall()
print(rows, "\n")

input_nameは画面などから入力された入力値だと思ってください。上記部分では、f-stringを使って入力値を埋め込んだSQL文を作成し、executeメソッドにSQL文を指定しています。この方法でも確かに目的となるデータの抽出はできます。

# 悪意ある入力の場合 (SQLインジェクション)
input_name = "SABURO' or 'A'='A"
select_sql = f"SELECT * FROM person WHERE name = '{input_name}'"
print(select_sql)
curs.execute(select_sql)
rows = curs.fetchall()
print(rows, "\n")

次に上記部分では、悪意のある入力があった例です。input_nameという画面入力欄に「SABURO’ or ‘A’=’A」という入力がされていると思ってください。この時に組み立てられるSQL文は「SELECT * FROM person WHERE name = ‘SABURO’ or ‘A’=’A’」のようになってしまい、実行結果を見てもらうとすべてのデータが取得できてしまっています。これは情報漏洩等の観点から大きな問題です。また、他にもデータを削除するようなコードが埋めこまれるなど色々なパターンの攻撃が考えられます。

# プレースホルダーを使って検索する
input_name = "SABURO' or 'A'='A"
select_sql = "SELECT * FROM person WHERE name = ?"
curs.execute(select_sql, (input_name,))
rows = curs.fetchall()
print(rows)

上記はプレースホルダーを使って検索している部分です。入力として悪意のある入力があったとしても、プレースホルダーを使った場合は検索結果は空（[]）となって不正にデータ取得はできていないことが分かります。

SQLインジェクション対策としては、他にも画面入力の際にエスケープ処理(ルールに従ってプログラムで使われる特殊文字や記号を別の文字列に置き換える)といった対策等がありますが、プレースホルダーも対策の一つになります。

実行時に値を設定してSQLを組み立てる際にはプレースホルダ―を使うようにしましょう。

メモリを使用したデータベース操作

SQLiteの便利な使い方として、メモリー上にデータベースを作る方法があります。

メモリー上でデータベースを作成し操作したい場合には、以下例のように「:memory:」と指定します。

import sqlite3

conn = sqlite3.connect(":memory:")
curs = conn.cursor()

print("===== CREATE TABLE =====")
# テーブルを作成する
curs.execute(
    "CREATE TABLE person ("
    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "name VARCHAR,"
    "age INTEGER)"
)
# 作成したテーブルをコミットする
conn.commit()

print("===== INSERT =====")
# プレースホルダ―を使ってデータを指定して登録する
insert_sql = "INSERT INTO person(name, age) VALUES(?, ?)"
curs.execute(insert_sql, ("TARO", 30))
curs.execute(insert_sql, ("JIRO", 20))
curs.execute(insert_sql, ("SABURO", 10))
# コミットする
conn.commit()

print("===== SELECT =====")
# データを検索する
curs.execute("SELECT * FROM person")
rows = curs.fetchall()
print(rows)

print("===== UPDATE =====")
# データを更新する
update_sql = "UPDATE person SET name=? WHERE id=?"
curs.execute(update_sql, ("SHIRO", 1))
# コミットする
conn.commit()
# データを検索する
curs.execute("SELECT * FROM person")
rows = curs.fetchall()
print(rows)

print("===== DELETE =====")
# データを削除する
curs.execute("DELETE from person WHERE id = 2")
# コミットする
conn.commit()
# データを検索する
curs.execute("SELECT * FROM person")
rows = curs.fetchall()
print(rows)

# カーソルとコネクションをクローズする
curs.close()
conn.close()

【実行結果】
===== CREATE TABLE =====
===== INSERT =====
===== SELECT =====
[(1, 'TARO', 30), (2, 'JIRO', 20), (3, 'SABURO', 10)]
===== UPDATE =====
[(1, 'SHIRO', 30), (2, 'JIRO', 20), (3, 'SABURO', 10)]
===== DELETE =====
[(1, 'SHIRO', 30), (3, 'SABURO', 10)]

上記例は、これまで紹介してきた各種処理を順に実行しているプログラムになります。このプログラムはメモリ上で動作するため、プログラム実行後にデータは消えてしまいますが、何回実行しても結果は同じになります。

実際にtest.db等で作ってしまうと実行のたびに状態が変わるため、必ずしも実行結果が同じになるとは限らず、確認のためには実行前に一度ファイルを消してといった面倒な作業が必要になります。

プログラム開発時には「:memory:」でメモリ上に展開しながら動作を確認し、問題ないことが確認出来たら、実際のデータベースファイルに書くというようにすると効率よく開発を進めることができます。