PythonとPandasでSQLiteデータベースを操作する方法を徹底解説

はじめに

実際の企業環境では、データがテキストファイルやExcelファイルとして保存されていることはむしろ少なく、Oracle、SQL Server、PostgreSQL、MySQLといったSQLベースのリレーショナルデータベースが幅広く利用されています。さらに近年では、これら以外の代替データベースも急速に普及しています。

データベースの選定は、アプリケーションが求めるパフォーマンス、データ整合性（インテグリティ）、スケーラビリティなどの要件によって決まるのが一般的です。

本記事では、Pythonに標準で同梱されているsqlite3モジュールと、データ分析ライブラリPandasを組み合わせて、データベースへのデータ書き込みやSQLクエリによる集計を行う方法を解説します。sqlite3はPythonのインストール時にデフォルトで含まれているため、追加のセットアップは不要です。動作確認のため、まずバージョンを出力してみましょう。

準備：必要なライブラリのインポート

SQLからDataFrameへデータを読み込む処理は非常にシンプルで、Pandasにはこのプロセスを簡単にするための関数が用意されています。まずはsqlite3とPandasをインポートし、sqlite3のバージョンを確認します。

import sqlite3
import pandas as pd
print(f"Output \n {sqlite3.version}")

実行結果

2.6.0

ステップ1：データベース接続とサンプルデータの作成

次に、sqlite3.connect()で「example.db」というデータベースファイルへの接続オブジェクトを作成します。続いて、顧客情報（customers）と注文情報（orders）の2つのDataFrameを作成します。

# 接続オブジェクトの作成
conn = sqlite3.connect("example.db")

# 顧客データ
customers = pd.DataFrame({
"customerID" : ["a1", "b1", "c1", "d1"]
, "firstName" : ["Person1", "Person2", "Person3", "Person4"]
, "state" : ["VIC", "NSW", "QLD", "WA"]
})
print(f"Output \n *** Customers info -\n {customers}")

実行結果

*** Customers info -
customerID firstName state
0 a1 Person1 VIC
1 b1 Person2 NSW
2 c1 Person3 QLD
3 d1 Person4 WA

続いて、注文データのDataFrameを作成します。各注文には顧客IDと購入された商品名が記録されています。

# 注文データ
orders = pd.DataFrame({
"customerID" : ["a1", "a1", "a1", "d1", "c1", "c1"]
, "productName" : ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})

print(f"Output \n *** orders info -\n {orders}")

実行結果

*** orders info -
customerID productName
0 a1 road bike
1 a1 mountain bike
2 a1 helmet
3 d1 gloves
4 c1 road bike
5 c1 glasses

ステップ2：DataFrameをデータベースへ書き込む

Pandasのto_sql()メソッドを使えば、DataFrameの内容をそのままSQLテーブルとして保存できます。if_exists="replace"を指定すると、同名のテーブルが既に存在する場合は置き換えられます。また、index=Falseにより、DataFrameのインデックス列はテーブルに書き込まれません。

# データベースへ書き込み
customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)

ステップ3：SQLクエリでデータを取得・集計する

次に、注文データと顧客データを結合（LEFT JOIN）し、顧客ごとの購入数量を集計するSQLクエリを作成します。三重引用符（"""）を使うことで、複数行にわたるSQL文をそのまま記述できます。

# データ取得用のSQLを作成
q = """
select orders.customerID, customers.firstName, count(*) as productQuantity
from orders
left join customers
on orders.customerID = customers.customerID
group by customers.firstName;
"""

作成したクエリは、Pandasのread_sql_query()関数で実行できます。結果は自動的にDataFrameとして返されるため、そのまま分析や可視化に活用できます。

# SQLを実行
pd.read_sql_query(q, con=conn)

コード全体のまとめ

ここまでの手順をすべてまとめた完全なサンプルコードが以下です。コピーしてそのまま実行すれば、同じ結果を再現できます。

import sqlite3
import pandas as pd
print(f"Output \n {sqlite3.version}")

# 接続オブジェクトの作成
conn = sqlite3.connect("example.db")

# 顧客データ
customers = pd.DataFrame({
"customerID" : ["a1", "b1", "c1", "d1"]
, "firstName" : ["Person1", "Person2", "Person3", "Person4"]
, "state" : ["VIC", "NSW", "QLD", "WA"]
})

print(f"*** Customers info -\n {customers}")

# 注文データ
orders = pd.DataFrame({
"customerID" : ["a1", "a1", "a1", "d1", "c1", "c1"]
, "productName" : ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})

print(f"*** orders info -\n {orders}")

# データベースへ書き込み
customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)

# データ取得用のSQLを作成
q = """
select orders.customerID, customers.firstName, count(*) as productQuantity
from orders
left join customers
on orders.customerID = customers.customerID
group by customers.firstName;

"""

# SQLを実行
pd.read_sql_query(q, con=conn)

実行結果

2.6.0
*** Customers info -
customerID firstName state
0 a1 Person1 VIC
1 b1 Person2 NSW
2 c1 Person3 QLD
3 d1 Person4 WA
*** orders info -
customerID productName
0 a1 road bike
1 a1 mountain bike
2 a1 helmet
3 d1 gloves
4 c1 road bike
5 c1 glasses
customerID firstName productQuantity
____________________________________
0      a1         Person1     3
1 c1 Person3 2
2 d1 Person4 1

おわりに

このように、Pythonのsqlite3モジュールとPandasを組み合わせれば、追加インストールなしにローカル環境でリレーショナルデータベースを構築し、テーブル作成からJOINを使った集計まで一貫して行えます。実務ではOracleやMySQLなどの本格的なRDBMSが使われることも多いですが、to_sql()やread_sql_query()といったPandasのインターフェースは共通しているため、ここで学んだ手法はそのまま応用できます。まずはSQLiteで基本をマスターし、必要に応じて他のデータベースへスキルを広げていくのがおすすめです。