この記事では、PythonでSQLiteデータベースを操作する高度なクエリと集計手法について解説します。具体的なコード例とその解説、応用例を含めています。
目次
はじめに
SQLiteは、C言語ライブラリとして実装された関係データベース管理システムです。Pythonからも簡単にアクセスでき、軽量ながらもパワフルな操作が可能です。今回は、PythonでSQLiteを使った高度なクエリと集計方法について解説します。
基本的なクエリの振り返り
データベースの接続
PythonでSQLiteに接続する基本的な方法を振り返ってみましょう。
import sqlite3
# データベースに接続
conn = sqlite3.connect("sample.db")
テーブルの作成とデータの挿入
基本的なテーブル作成とデータ挿入についても確認しておきます。
# カーソルオブジェクトを作成
c = conn.cursor()
# テーブル作成
c.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER
);
""")
# データ挿入
c.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
c.execute("INSERT INTO users (name, age) VALUES ('Bob', 40)")
# コミット
conn.commit()
高度なクエリと集計
JOINの使用
複数のテーブルを結合するJOINについて見ていきましょう。
# 新しいテーブル作成
c.execute("""
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
amount INTEGER
);
""")
# データ挿入
c.execute("INSERT INTO orders (user_id, amount) VALUES (1, 1000)")
c.execute("INSERT INTO orders (user_id, amount) VALUES (1, 1500)")
c.execute("INSERT INTO orders (user_id, amount) VALUES (2, 2000)")
# JOINを使って結合
c.execute("""
SELECT users.name, SUM(orders.amount)
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.id
""")
for row in c.fetchall():
print(row)
サブクエリ
サブクエリを使った高度なデータ抽出について説明します。
# サブクエリを使用して、平均年齢以上のユーザーを抽出
c.execute("""
SELECT * FROM users
WHERE age >= (SELECT AVG(age) FROM users)
""")
for row in c.fetchall():
print(row)
応用例
日付データと時系列分析
SQLiteで日付データを使った時系列分析を行う例です。
# テーブル作成
c.execute("""
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
event_date TEXT
);
""")
# データ挿入
c.execute("INSERT INTO events (user_id, event_date) VALUES (1, '2023-01-01')")
c.execute("INSERT INTO events (user_id, event_date) VALUES (1, '2023-01-02')")
c.execute("INSERT INTO events (user_id, event_date) VALUES (2, '2023-01-01')")
# 時系列分析
c.execute("""
SELECT user_id, COUNT(*)
FROM events
WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY user_id
""")
for row in c.fetchall():
print(row)
動的なクエリ生成
動的にクエリを生成する方法についてです。
# パラメータを動的にする
query = "SELECT * FROM users WHERE age >= ?"
c.execute(query, (30,))
for row in c.fetchall():
print(row)
まとめ
PythonでSQLiteを用いた高度なクエリと集計手法について解説しました。JOINやサブクエリ、日付データの時系列分析、動的なクエリ生成など、多角的な観点からデータを操作・分析する方法を学びました。これらの知識を活かして、より高度なデータ操作を行いましょう。
コメント