CSVファイルをSQLで操作するには？
CSVファイルをPythonで操作中にSQLで操作できれば…ということがあります。
PythonでCSVファイルをSQLで操作することのメリットを説明します。
データの絞り込みが容易
SQLを使用することで特定の条件を満たすデータ行のみを選択的に抽出することができます。例えば、特定の日付範囲におけるデータやある特定の値以上のデータといった具体的な条件を設定しそれにマッチするデータのみを簡単に見つけ出すことが可能です。これにより、目的のデータに素早くアクセスすることができます。
大量データの扱いやすさ
CSVファイルには時として非常に大量のデータが含まれることがあります。SQLを活用することでこれら大量のデータを効率的に処理し管理することができます。SQLの強力なデータ処理機能により、データの読み込み、更新、削除といった操作を迅速かつ容易に行えます。
複雑なデータ処理の実現
SQLにはデータ処理を行うための集計処理が備わっています。特定の条件に基づいてデータを集計するといった高度なデータ分析を実現できます。
コードの簡潔化
SQLを用いることで、複数行にわたる複雑なプログラムコードを数行のSQLクエリに置き換えることができます。これによりプログラムの可読性が向上しメンテナンスが容易になります。
再利用性の向上
一度作成したSQLクエリは、異なるデータセットに対しても再利用することが可能です。これにより似たようなデータ操作が必要な際に効率的に作業を進めることができます
これらのメリットにより、Pythonと組み合わせてCSVファイルをSQLで操作することは、データの効率的な管理と分析において非常に強力な手段となります。
CSVファイルをSQLで操作するサンプルコード
次にCSVファイルをSQLで操作するサンプルコードを紹介します。
事前準備として、以下のファイルをダウンロードして解凍してください。
https://kamedassou.com/wp-content/uploads/test_prefdata.zip
※一時的にリンクを消していますので必要な方は上記URLをコピーしてダウンロードして下さい。
ダウンロード後は以下の図のようにCドライブにtestcsvというフォルダを作成し、その中にファイルをコピーしてください。
次にCSVファイルをSQLで操作するサンプルコードを紹介します。
# データフレームの作成、データの読み込み、加工、書き出しなどに使用
# pip install pandas
import pandas as pd
# データフレーム上でSQLクエリを実行できるようにするライブラリ
# pip install pandasql
from pandasql import sqldf
# SQLiteデータベースアクセス
import sqlite3
def select_sql_csv(csv_filename, sql_query):
"""
CSVファイルに対してSQL文を実行します。
Args:
csv_filename (str): CSVファイルのパス名
sql_query (str): 実行するSQLクエリ
Returns:
pandas.DataFrame: クエリ結果のデータフレーム。
"""
# CSVファイルの読み込み
temp_table = pd.read_csv(csv_filename)
# pandasqlを使用してSQLクエリを実行
result_df = sqldf(sql_query, locals())
return result_df
def exec_sql_csv(csv_filename, sql_statement):
"""
CSVファイルを読み込み、一時的にSQLite3データベースにインポートし、
指定されたSQL文(INSERT, UPDATE, DELETE)を実行後、
変更内容を元のCSVファイルに書き戻し、一時的に作成したデータベースを削除する関数です。
Args:
csv_filename (str): CSVファイルのパス名
sql_statement (str): 実行するSQL文。INSERT, UPDATE, DELETEのいずれか
Returns:
None:
"""
# メモリ上で一時的な接続を作成
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# CSVファイルをDataFrameとして読み込み
df = pd.read_csv(csv_filename)
# DataFrameを一時データベースにテーブルとしてインポート
df.to_sql('temp_table', conn, if_exists='replace', index=False)
# SQL文の実行
cursor.execute(sql_statement)
conn.commit()
# 変更を反映したデータを読み込み
updated_df = pd.read_sql_query('SELECT * FROM temp_table', conn)
# 更新されたDataFrameをCSVファイルに書き戻し
updated_df.to_csv(csv_filename, index=False)
# データベース接続のクローズ
conn.close()
######################################
# 関数の使用例 テーブル名指定は必ず temp_tableとなります。
if __name__ == "__main__":
# CSVファイルへのパスを指定
csv_file = "c:\\testcsv\\test_prefdata.csv"
# SELECT
print("############################################################################################################")
strsql = ""
strsql += " SELECT 都道府県,姓,名,性別,年齢 FROM temp_table WHERE 都道府県 like '%府' and 姓 = '佐藤'"
strsql += " ORDER BY 都道府県,姓 "
print(strsql)
result_df = select_sql_csv(csv_file, strsql)
print(result_df)
# SELECT GROUP BY
print("############################################################################################################")
strsql = ""
strsql += " SELECT 都道府県,SUM(年齢) as 年齢計,AVG(年齢) as 平均年齢 ,COUNT(年齢) as 件数 FROM temp_table WHERE 都道府県 like '%府'"
strsql += " GROUP BY 都道府県"
print(strsql)
result_df = select_sql_csv(csv_file, strsql)
print(result_df)
# DELETE
print("############################################################################################################")
strsql = "DELETE FROM temp_table WHERE 都道府県 like 'テスト都道府県%'"
exec_sql_csv(csv_file, strsql)
print(strsql)
# INSERT
print("############################################################################################################")
strsql = "INSERT INTO temp_table(都道府県,姓,名,性別,年齢) VALUES('テスト都道府県1','テスト姓','テスト名','テスト性別',10)"
exec_sql_csv(csv_file, strsql)
print(strsql)
strsql = "INSERT INTO temp_table(都道府県,姓,名,性別,年齢) VALUES('テスト都道府県2','テスト姓','テスト名','テスト性別',10)"
exec_sql_csv(csv_file, strsql)
print(strsql)
strsql = "INSERT INTO temp_table(都道府県,姓,名,性別,年齢) VALUES('テスト都道府県3','テスト姓','テスト名','テスト性別',10)"
exec_sql_csv(csv_file, strsql)
print(strsql)
# UPDATE
print("############################################################################################################")
strsql = "UPDATE temp_table SET 年齢 = 20 WHERE 都道府県 = 'テスト都道府県1'"
exec_sql_csv(csv_file, strsql)
print(strsql)
サンプルを実行する前にpandasモジュール、pandassqlモジュールのインストールが必要ですのでインストールしていない場合には対応してください。
pip install pandas
pip install pandasql実行結果
SELECTした結果とDELETE、INSERT、UPDATE文が流れます。
SELECT処理した例です。
都道府県で、京都府と大阪府をlikeで取得、かつ姓は佐藤さんで抽出した結果です。
############################################################################################################
SELECT 都道府県,姓,名,性別,年齢 FROM temp_table WHERE 都道府県 like '%府' and 姓 = '佐藤' ORDER BY 都道府県,姓
都道府県 姓 名 性別 年齢
0 京都府 佐藤 幸司 男 40
1 京都府 佐藤 正浩 男 12
2 京都府 佐藤 栄治 男 49
3 大阪府 佐藤 梨紗 女 40
4 大阪府 佐藤 美桜 女 56
5 大阪府 佐藤 達雄 男 50
SELECTでGROUP BY句を利用した例です。
都道府県で、京都府と大阪府をlikeで取得、かつ都道府県ごとで年齢や平均年齢と件数を取得した結果です。
############################################################################################################
SELECT 都道府県,SUM(年齢) as 年齢計,AVG(年齢) as 平均年齢 ,COUNT(年齢) as 件数 FROM temp_table WHERE 都道府県 like '%府' GROUP BY 都道府県
都道府県 年齢計 平均年齢 件数
0 京都府 72617 34.464642 2107
1 大阪府 75762 35.026352 2163DELETEを利用した例です。
テスト都道府県という都道府県名をlike指定で削除しています。※次のINSERT文のデータを２回目の処理以降で消すために入れています。
############################################################################################################
DELETE FROM temp_table WHERE 都道府県 like 'テスト都道府県%'INSERTを利用した例です。
テスト都道府県1,2,3をINSERTしています。
############################################################################################################
INSERT INTO temp_table(都道府県,姓,名,性別,年齢) VALUES('テスト都道府県1','テスト姓','テスト名','テスト性別',10)
INSERT INTO temp_table(都道府県,姓,名,性別,年齢) VALUES('テスト都道府県2','テスト姓','テスト名','テスト性別',10)
INSERT INTO temp_table(都道府県,姓,名,性別,年齢) VALUES('テスト都道府県3','テスト姓','テスト名','テスト性別',10)CSVファイルを開くと一番最後の行に３行追加されているのが見て取れます。
UPDATEを利用した例です。
テスト都道府県1のデータの年齢を１０から２０に変更しています。
############################################################################################################
UPDATE temp_table SET 年齢 = 20 WHERE 都道府県 = 'テスト都道府県1'年齢が20にUPDATEされます。
サンプルデータは１０万件のデータですが、実際に実行すると速度がでているのがわかるかと思います。
処理の流れ
select_sql_csv関数
CSVファイルからデータを読み込み指定されたSQLクエリを実行し、結果をデータフレームとして返します。具体的にはresult_df = sqldf(sql_query, locals())の処理でSQLクエリを処理しています。
exec_sql_csv関数
CSVファイルを読み込みconn = sqlite3.connect(':memory:')の処理で一時的にSQLiteデータベースにインポートし、SQL文（INSERT, UPDATE, DELETE）を実行します。変更内容を元のCSVファイルに書き戻した後一時的に作成されたデータベース接続を閉じるとSQLiteデータベースはなくなります。
注意事項
以下の図のようにヘッダーがダブルコーテーションでくくられている場合でもexec_sql_csv関数を利用するとCSVファイルに書き戻し処理が行われるのでダブルコーテーションが消えてしまいます。
運用の兼ね合いで問題がある場合にはこの関数を利用しないことを検討する必要があります。
最後に
以上がPythonでCSVファイルをSQLで操作する例となります。この方法を利用するとソースコードの簡略化やSQLの再利用などが検討できますので利用してみてはいかがでしょうか。
コメント