はじめに
pythonでは標準ライブラリとしてSQLiteが組み込まれているため、外部モジュールを追加インストールすることなく、すぐにデータベース操作が可能です。データベースの接続から、データの作成、読み取り、更新、削除（CRUD操作）まで、シンプルで実用的なコード例を交えて解説していきます。
基本的な操作
1.データベースへの接続
最初に、SQLiteデータベースに接続する必要があります。Pythonではsqlite3モジュールを使って接続します。データベースはファイル形式で保存されますが、メモリ内で一時的に作成することもできます。
import sqlite3
# データベースファイルに接続（なければ自動的に作成）
conn = sqlite3.connect('example.db')
このコードは、example.dbというデータベースファイルに接続します。このファイルが存在しない場合、新しく作成されます。
2. カーソルの作成
データベース接続が確立されたら、次にカーソルを作成します。カーソルは、SQL文をデータベースに送信して実行するためのオブジェクトです。
# カーソルを作成
cursor = conn.cursor()
3. テーブルの作成
次に、データを格納するためのテーブルを作成します。CREATE TABLE SQL文を使ってテーブルを作成します。以下は、usersというテーブルを作成する例です。
# テーブル作成
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER
)
''')
・IF NOT EXISTS: 既にテーブルが存在している場合、テーブルを再作成しないための条件。
・PRIMARY KEY: 主キーを設定し、一意のIDを自動で割り当てます。
・AUTOINCREMENT: id列に自動で連番の値が割り当てられます。
4. データの挿入
テーブルを作成したら、データを挿入することができます。INSERT INTO SQL文を使って、データを追加します。
# データを挿入
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25))
# データの挿入を確定するためにコミット
conn.commit()
・VALUES (?, ?)はプレースホルダを表し、SQLインジェクションを防ぐために使用されます。
・commit()を使って、データの挿入を確定します。これを行わないと、挿入したデータは保存されません。
5. データの取得
テーブルからデータを取得するには、SELECT文を使います。fetchall()でクエリ結果をすべて取得できます。
# データを取得
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
# データを出力
for row in rows:
print(row)
・SELECT * FROM usersは、usersテーブル内のすべての列を取得するクエリです。
6. データの更新
既存のデータを変更するには、UPDATE文を使います。以下は、nameが"Bob"のユーザーのageを更新する例です。
# データの更新
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (28, 'Bob'))
# コミットして変更を反映
conn.commit()
7. データの削除
データを削除する場合は、DELETE文を使います。例えば、nameが"Alice"のユーザーを削除するには次のようにします。
# データの削除
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))
# コミットして削除を反映
conn.commit()
8. トランザクションの管理
複数の操作を一括で行い、途中でエラーが発生した場合にはロールバックすることで、すべての操作を無効にすることができます。
try:
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Charlie', 35))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('David', 40))
# 問題なければコミット
conn.commit()
except sqlite3.Error as e:
print(f"An error occurred: {e}")
# エラーが発生したらロールバック
conn.rollback()
9. 接続のクローズ
データベース接続を使用し終えたら、必ず接続を閉じるようにします。これにより、リソースの無駄遣いを防ぎます。
# データベース接続を閉じる
conn.close()
また、withステートメントを使って接続を自動的に閉じることもできます。
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
withステートメントを使うと、ブロックを抜けるときに自動的にconn.close()が呼び出され、接続がクリーンに終了します。
応用編
1. インデックス
インデックスは、データベースの検索や読み込みのパフォーマンスを向上させるために使います。インデックスは特定の列に対して作成され、検索やフィルタリング時にその列のデータへのアクセスを効率化します。
# インデックスの作成
cursor.execute("CREATE INDEX idx_name ON users (name)")
上記の例では、usersテーブルのname列にインデックスidx_nameを作成しています。このインデックスにより、name列を使った検索（SELECT文など）が高速になります。
インデックスの利用例
インデックスを作成すると、次のようなSELECT文の実行速度が向上します。
# インデックスを使用して検索
cursor.execute("SELECT * FROM users WHERE name = ?", ('Alice',))
rows = cursor.fetchall()
インデックスは、検索処理が多い場合や大規模データセットを扱う場合に非常に効果的です。ただし、挿入や更新の際にはインデックスが更新されるため、過剰にインデックスを作成すると逆にパフォーマンスが低下することもあります。
2. トリガー
トリガーは、データベース内の特定のイベント（データの挿入、更新、削除など）が発生した際に、自動的に実行されるSQLスクリプトです。これにより、データの一貫性を保ったり、複雑な処理を自動化することができます。
トリガーの作成
例えば、usersテーブルにデータが挿入されたときに、自動的にログを記録するトリガーを作成します。
# ログテーブルの作成
cursor.execute('''
CREATE TABLE IF NOT EXISTS log (
action TEXT,
timestamp TEXT
)
''')
# トリガーの作成
cursor.execute('''
CREATE TRIGGER log_insert AFTER INSERT ON users
BEGIN
INSERT INTO log (action, timestamp) VALUES ('INSERT', datetime('now'));
END;
''')
このトリガーは、usersテーブルにデータが挿入されるたびに、logテーブルに「INSERT」というアクションと現在の日時を自動的に記録します。
トリガーの利用例
次に、usersテーブルにデータを挿入すると、自動的にログが記録されます。
# データの挿入
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Eve', 28))
conn.commit()
# ログの確認
cursor.execute("SELECT * FROM log")
logs = cursor.fetchall()
print(logs)
このように、トリガーを使って、データの変更をトラッキングする仕組みを構築できます。
3. ビュー
ビューは、テーブルのデータに対して仮想的なテーブルを定義するSQLの機能です。実際にはデータを保持せず、SELECT文を簡略化したり、複雑なクエリの結果を保存することに使われます。
例えば、usersテーブルから30歳以上のユーザーだけを抽出するビューを作成します。
# ビューの作成
cursor.execute('''
CREATE VIEW IF NOT EXISTS senior_users AS
SELECT * FROM users WHERE age >= 30
''')
ビューの利用例
# ビューからデータを取得
cursor.execute("SELECT * FROM senior_users")
rows = cursor.fetchall()
for row in rows:
print(row)
ビューは複雑なクエリを繰り返し使う場合に非常に便利です。データを保持しないため、クエリの実行時に常に最新のデータを参照します。
4. JOIN操作
SQLiteでは、複数のテーブルを結合してデータを取得するJOIN操作が可能です。JOINを使うことで、関連するデータを複数のテーブルから一度に取得できます。
INNER JOINの例
例えば、usersテーブルとordersテーブルがあり、それぞれのユーザーが行った注文を結合して表示する場合です。
# ordersテーブルの作成
cursor.execute('''
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
product TEXT,
FOREIGN KEY (user_id) REFERENCES users (id)
)
''')
# サンプルデータの挿入
cursor.execute("INSERT INTO orders (user_id, product) VALUES (?, ?)", (1, 'Laptop'))
cursor.execute("INSERT INTO orders (user_id, product) VALUES (?, ?)", (2, 'Smartphone'))
conn.commit()
# INNER JOINを使用して、ユーザーと注文を結合して表示
cursor.execute('''
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id
''')
rows = cursor.fetchall()
for row in rows:
print(row)
この例では、usersテーブルとordersテーブルをINNER JOINで結合し、ユーザーの名前とその注文を一緒に表示しています。
LEFT JOINの例
LEFT JOINを使うと、結合するテーブルにデータが存在しない場合でも、左側のテーブルのデータはすべて取得されます。例えば、注文がないユーザーも表示したい場合は、次のようにします。
# LEFT JOINを使用して、すべてのユーザーと注文を結合
cursor.execute('''
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id
''')
rows = cursor.fetchall()
for row in rows:
print(row)
このクエリは、注文がないユーザーも結果に含めて表示します。