LoginSignup
3
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Inclusive_Solutions_LLC

Pythonを使って、CSV形式のデータをSQLライクに操作してみる。

Last updated at Posted at 2023-12-30

やってみたこと

Pythonのライブラリであるpandasを使って、CSVファイルをSQLライクに加工・集計・分析を行う方法をまとめておきます。

sqlite、pandasといったPythonライブラリを組み合わせると、様々な形式のデータをリレーショナルデータベース（RDB）のテーブルとして扱うことができ、SQLを適用することが可能となります。

csv　→　pandas　→　SQLite の流れ

以下は、ローカル環境にあるCSVファイルにSQLをいきなり発行するPythonの関数です。

CsvファイルにSQLを適用する関数.ipynb
def exec_sql(query,path):
    df = pd.read_csv(path)
    table_name = os.path.splitext(os.path.basename(path))[0]
    conn = sqlite3.connect(':memory:')

    df.to_sql(table_name,conn,if_exists='replace',index=False)
    result = pd.read_sql(query,conn)
    return result

上の関数は、一度pandasのデータフレームを経由して、sqliteのテーブルに変換し、SQLを適用する流れをまとめています。

特にポイントとなるのが、メモリ上にデータベースを構築してくれる以下の記述。

インメモリでデータベースを構築
conn = sqlite3.connect(':memory:')

コネクションが閉じれば失われる、いわば「使い捨て」のデータベースが構築されます

「どんどんSQLを記述・実行して、実行結果が見てみたい」というだけなら、これで充分でしょう。

データ保管の永続性や、複数スレッドからの参照制御といった詳細なデータベースの議論を省けば、コードもこのくらいシンプルになります。

なお、この関数を実際に呼び出して、実行する記述は以下の通り。

上記関数の呼び出し.ipynb
#SQLをテキスト形式で以下に記述
query = f'''
SELECT
    血液型
    ,COUNT(*) AS 人数
FROM
    dummy 
GROUP BY 血液型
'''

#上記のクエリテキストを代入し、対象CSVファイルに適用
exec_sql(query,"/content/dummy.csv")

拡張子（.csv)　を外したファイル名が、そのままテーブル名として使えており、そのままFROM句で指定可能なテーブルになっています。あたかもcsvファイルに直接SQLを発行しているような感覚で、ソースコードの記述・解読が進められます。

なお、Google Colabでの操作感は以下画像の感じ。

image.png
※画像のテストデータは個人情報テストデータジェネレーターによる、ダミーデータです。

画面も見やすくていい感じ。これならOracleやMySQLなどのRDBに近い感覚で操作できそうです。

どんなとき役に立つのか

SQLで行うデータの抽出・加工は、大抵のことはpandasでも行えます。しかし、以下のような場面では、SQLライクの記述方式も一定役に立つのではないでしょうか。

  • SQLに慣れており、得意なSQLをPythonでも使い倒したい
  • RDBをまだ構築していないが、今後構築する予定があり、予めSQLでデータの抽出方法を記述しておきたい
  • SQLが得意なメンバー・クライアントに向けて、データパイプラインの要件・仕様を説明したい
  • これから新たにSQLの書き方を勉強したいと考えており、どんどんSQLを書ける環境を簡単に用意したい
  • データはあるがKPIが定まっておらず、まずはどんな項目を使って、どんな集計が行えそうかだけでも事前に調査したい

とくにSQLには、

  • マーケティングや経営企画など、エンジニア以外のビジネス職にも一定普及している
  • 歴史が非常に長く、幅広い年代の技術者にとって受け入れやすい

といった長所があります。pandasのプログラムのままではなく、あえてSQLの形式で記述まとめていくことも、状況次第では役に立ちそうです。

Google Colabでツール化してみる

こんな感じで色々な表形式データにSQLを適用していく構想は、Google ColabやJupyter Notebookなどと相性が良さそうです。アドホックにPythonを記述し、すぐに実行・結果の確認まで行える点がSQLと相性がよく、SQLの記述がとても捗ります。

作成したGoogle Colabのツールはこちら。

なお、Google Colabでの使用を想定したJupyterNotebook(拡張子は.ipynb）形式のコードは以下のようになっています。

Google Colabの環境を利用して、Google Driveなどに保管したCSVファイルにもSQLを発行し、結果を確認することができます。Googleアカウントを持っている相手であれば、簡単・手軽にツールとして配布できる点も便利ですね。

Google Colabで使用するipynb形式データ
{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# CSV → SQL への簡易変換ツール\n",
        "\n",
        "---\n",
        "## ■できること\n",
        "CSVファイルをリレーショナルデータベースのテーブルに見立てて、SQLによるでーた抽出・加工のイメージを確認することができます。\n",
        "\n",
        "各種データ集計、レポート作成等の業務の自動化・業務改善に活用できます。\n",
        "\n",
        "SQLの練習などにもご活用ください。\n",
        "\n",
        "## ■注意\n",
        "\n",
        "\n",
        "*   CSVファイルの先頭には、数字を使用することはできません。\n",
        "*   ファイル名にハイフン(-)や、ピリオド（．)などを使ってはいけません。\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "2NPXSHTdhQsT"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 1.事前準備\n",
        "必要なライブラリを用意する。"
      ],
      "metadata": {
        "id": "wQQf-CLWid4u"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xtYjo39fhE5m"
      },
      "outputs": [],
      "source": [
        "!pip install itables\n",
        "from itables import init_notebook_mode #SQL適用結果の外観調整\n",
        "init_notebook_mode(all_interactive=True)\n",
        "\n",
        "import pandas as pd\n",
        "import sqlite3\n",
        "\n",
        "import os\n",
        "import datetime\n",
        "import openpyxl\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2.データの確認・デフォルトのクエリ作成\n",
        "※ファイル名は以下の規則に準拠してください。\n",
        "\n",
        "\n",
        "*   ファイル名の先頭には、数字を使用しないでください。（NG例:20230408_サンプル人事マスタ)\n",
        "\n",
        "*   ピリオドやハイフンも使ってはいけません。\n",
        "\n"
      ],
      "metadata": {
        "id": "OZuJoL7DjJ30"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "DataFrame = pd.core.frame.DataFrame\n",
        "\n",
        "\n",
        "csv_file_path = \"\\u5DE6\\u30B5\\u30A4\\u30C9\\u30D0\\u30FC\\u306E\\u30D5\\u30A1\\u30A4\\u30EB\\u4E00\\u89A7\\u304B\\u3089\\u3001CSV\\u5F62\\u5F0F\\u306E\\u30C7\\u30FC\\u30BF\\u3092\\u9078\\u629E\" # @param {type:\"string\"}\n",
        "\n",
        "\n",
        "\"\"\"\n",
        "関数１：CSVファイルをRDBのテーブルに変換\n",
        "\"\"\"\n",
        "\n",
        "def csv_to_RDB(query:str,path:str) ->DataFrame:\n",
        "  try:\n",
        "    df = pd.read_csv(path)\n",
        "    table_name = os.path.splitext(os.path.basename(path))[0]\n",
        "    conn = sqlite3.connect(':memory:')\n",
        "\n",
        "    df.to_sql(table_name,conn,if_exists='replace',index=False)\n",
        "    result = pd.read_sql(query,conn)\n",
        "\n",
        "  finally:\n",
        "    if os.path.splitext(os.path.basename(path))[0][0].isdigit():\n",
        "      print(\"※ファイル名の先頭が数字になっています。csvファイル名を見直してください。\")\n",
        "\n",
        "    if \"-\" in os.path.splitext(os.path.basename(path))[0]:\n",
        "      print(\"※ファイル名にハイフン（-）は使用できません。csvファイル名を見直してください。\")\n",
        "\n",
        "    if \"．\" in os.path.splitext(os.path.basename(path))[0]:\n",
        "      print(\"※ファイル名にピリオド（．）は使用できません。csvファイル名を見直してください。\")\n",
        "\n",
        "  return result\n",
        "\n",
        "\"\"\"\n",
        "関数２：SQLクエリの基本形を作成\n",
        "\"\"\"\n",
        "def showDefaultQuery(df:DataFrame) -> None:\n",
        "  header = list(df.columns)\n",
        "  col =  f''',\\n    '''.join(header)\n",
        "  query = f'''SELECT \\n    {col}\\nFROM\\n    {table_name}\\nWHERE\\n    1 = 1'''\n",
        "  print(query)\n",
        "\n",
        "\n",
        "\n",
        "#--　関数を使って、クエリのテンプレートを作成\n",
        "\n",
        "table_name = os.path.splitext(os.path.basename(csv_file_path))[0]\n",
        "all_record = csv_to_RDB(f'''SELECT * FROM {table_name} ''',csv_file_path)\n",
        "showDefaultQuery(all_record)\n",
        "\n"
      ],
      "metadata": {
        "id": "ecL3iFgejI-f"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 3.SQLを記述し、データを抽出・集計\n",
        "---\n",
        "任意のSQLクエリを記述する。2で出力されたクエリのテンプレートを参考に、任意のクエリを記述して使用します。"
      ],
      "metadata": {
        "id": "8dlEMOCppufe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "'''\n",
        "SQLの記述はこの直下で行う。\n",
        "'''\n",
        "\n",
        "query = f'''\n",
        "SELECT * FROM テーブル名\n",
        "'''\n",
        "\n",
        "result = csv_to_RDB(query,csv_file_path)\n",
        "display(result)"
      ],
      "metadata": {
        "id": "rZH9XDCQoWVj"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}

上記コードを使用する流れは以下の通り。

  • 上記Jupyter Notebookのテキストをメモ帳などに貼り付け
  • 拡張子を.ipynbとして、好きなファイル名にする
  • Google Driveに当該ファイルをアップロード
  • Google Colabで上記ドライブ上のファイルを開く
ipynb形式のテキストデータは、Google Colabから開くことができます。

どんな展望があるのか

今回紹介したのは、サーバー・ローカルマシン上に保管されたCSVファイルの話に留まります。しかし理論上はCSV以外にも、様々な半構造化データにも応用できそうです。

たとえば、

  • Googleスプレッドシートに記載された表形式のデータ
  • WebサイトにアップロードされたCSV、Excel形式のデータ
  • Webサイトに掲載されたHTML形式のテーブルデータ
  • APIがgetリクエストに返してきたjson形式のデータ

などなど。表形式のデータにまとまりさえすれば原理は同じです。つまりpandasのデータフレームに入りさえすれば、CSVに限らずどんな形式であってもRDBのテーブルのようにSQLで取り扱いが可能ということですね。

まとめ

データベース・テーブルの構築時に行うスキーマの定義作業は、何かと煩雑で負担になりがちです。極力簡単に済ませて、「とりあえずどんどんSQLで操作してみたい」と思ったときは、Google ColabとPythonが便利そうです。

SQLとデータ基盤の実務に興味を持ったら、まずはGoogle ColabでSQLとPythonの記述を練習してみるのはアリかもしれません。

参考リンク

※csv形式のデータセットの作成には「個人情報テストデータジェネレーター」が便利です。

筆者の編集後記

2025年1月7日：2025年現在でも本記事で紹介した手法は実務で十分通用すると考えています。しかし、データの件数が多く、Pandasでは遅い・動かないといった課題が生じたら、Pandas ではなく、同じPythonライブラリであるPolarsが便利です。PolarsはRustによるデータ処理を基調としており、Pandasよりも遥かに高速にビッグデータ処理が可能です。

加えてPolarsには、

・遅延実行（LazyFrame)の機能が便利で、実行速度・パフォーマンスの改善がしやすい
・メソッドチェーンを用いた記述により、読みやすいコードが書きやすい
・polars SQLという機能がもとからあり、わざわざsqliteと組み合わせなくとも、SQLとPythonプログラムのシームレスな連携が可能

といったメリットがあります。

まだまだPolarsよりはPandasのほうがメジャーで、使い慣れているという人も多いと思います。しかし、品質面をさらに追求するなら、Polarsにも多くの魅力がある点も確かでしょう。

3
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?