Python

SQLAlchemyとPandasでデータベースから任意データを取得する -雛形ソースコードあり-

SQLAlchemyとPandasでデータベースから任意データを取得する

こんにちは。現役エンジニアの”はやぶさ”@Cpp_Learningです。Python使ってデータ分析などをしています。

本記事ではPythonライブラリの SQLAlchemyPandas を使って、データベースから任意データを取得し、データフレームに変換する方法を紹介します。

モチベーション

Pythonでデータ分析をするとき、以下のフローで処理する人が多いと思います。

  1. クラウドストレージやデータベースなどからデータ取得
  2. データの前処理
  3. データ可視化

データベースからデータ取得する際のSQLクエリを頑張れば、前処理は不要かもしれませが、大雑把にデータ取得して、Pythonで細かい前処理をする方が楽なときもあります。

上記の❶と❷をシームレスに実現する方法をいくつか検討し、ベストだと感じたのがSQLAlchemyとPandasの組み合わせだったので、本記事でやり方を解説します。

SQLAlchemyとは

SQLAlchemyとは、Python専用のSQLツールキットであり、かつORMでもあります。公式サイトでは以下のように紹介しています。

The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

引用元:SQLAlchemy|公式サイト

ORMについては、以下の記事で説明しています。

XORMによるデータベース操作 -ORM入門-
【Go言語(Golang)】XORMによるデータベース操作 -ORM入門-Go言語(Golang)用のORMの1つXORMの基本的な使い方をソースコード付きで解説した記事です。ORM入門したい人やGoでデータベース操作したい人にオススメの内容です。...
スポンサーリンク

実践 SQLAlchemyとPandasでデータベース操作

SQLAlchemyとPandasを使って、データベースから任意データを取得する方法は何通りもありますが、基本的なフローは以下の通りです。

  1. SQLAlchemyでエンジン作成
  2. SQLAlchemyでクエリ組み立て(※)
  3. pandas.read_sqlでクエリ結果をデータフレームに格納

(※)上記❷でクラスの使用・未使用、filterの使用・未使用など様々

何パターンか実践してみます。なお本記事で使うバージョンは以下の通りです。

  • SQLAlchemy==1.4.23
  • pandas==1.3.3

以降からソースコード書いていきます。

SQLAlchemyのみを使用するパターン

まずはPandasを使わず、SQLAlchemyのみを使ってデータベースから任意データを取得します

❶エンジン生成

最初にデータベースと接続するためのエンジンを生成します。SQLiteの場合は以下の通りです。

user.dbにはユーザー情報を登録した以下のテーブルが存在します。

xormでレコード作成

本記事で扱うデータベースは SQLite ですが、SQLAlchemyを使うことでデータベースの違いを吸収できます。

❷SQLAlchemyのsession機能を使用してSELECT

SQLAlchemyのみでデータベースから任意データを取得するコードが以下です。

id:1, name:Kururu, age:5

生SQLを使わずにクエリを組み立て、Python側で定義したUserクラスに従ってクエリ結果をマッピングすることで型安全を保障しています。

つまりusersに格納されたクエリ結果の id:1, name:Kururu, age:5 の内、nameはstr型、ageはint型で扱うことができます。

最後の session.close() engine.dispose() の書き忘れに要注意

➌クエリ結果をPandasデータフレームに格納

以下のコードで先ほど組み立てたクエリのSQL文を取得できます。またpandas.read_sql_queryを使うと簡単にデータベースへの接続とクエリ結果のデータフレーム格納を実現できます。

ただし、このやり方だと usersdf の両方にクエリ結果が格納されます。df のみに格納したい場合は、以降から紹介する session を使わないコードの方がスマートです。

SQLAlchemyとPandasとクラスを使ってクエリ結果を取得するパターン

先ほど紹介したコードをベースに session未使用にしたものが以下です。

============== row SQL =================
SELECT “user”.id, “user”.name, “user”.age
FROM “user”
WHERE “user”.name = :name_1 AND “user”.age >= :age_1
LIMIT :param_1
========================================

=========== query result ===============
id name age
0 1 Kururu 5
========================================

SQLAlchemyのORM機能…というよりクエリビルド機能をフル活用して、クエリを組み立て、Pandasでクエリ結果の取得とデータフレームへの格納を行っています。

以上がSQLAlchemyとPandasとクラスを使うパターンです。

session が無いので、engine.dispose() のみでOK

クラスを使わないパターン

クラスを使わずにクエリ結果をデータフレームに格納するパターンは以下の通りです。

============== row SQL =================
SELECT id, name, age
FROM “user”
WHERE name IN ([POSTCOMPILE_name_1]) AND age >= :age_1
LIMIT :param_1
========================================

=========== query result ===============
id name age
0 1 Kururu 5
========================================

クラス使いたくないし、生SQLも書きたくない人にオススメのコードです。

クエリを組み立てるとき、filterのかわりに select_from(テーブル).where(条件)という書き方もできます

スポンサーリンク

SQLAlchemyとPandasによるデータベース操作(SELECT)の雛形コード

クラスを使う・使わない(filterを使う・使わない)を選択できる雛形コード作成したので、紹介します。

アプリに合わせて雛形コードを改良して使うのが便利です。

【応用】柔軟なクエリの組み立て

最後に雛形コードを改良して、柔軟なクエリ組み立てを実現した例を紹介します。

============== row SQL =================
SELECT id, name, age
FROM “user”
WHERE name IN ([POSTCOMPILE_name_1]) AND age >= :age_1
LIMIT :param_1
========================================

=========== query result ===============
id name age
0 1 Kururu 5
1 2 Hayabusa 300
========================================

このようなに柔軟なクエリも組めるので、目的に応じて雛形コードを改良してみてください。

まとめ

SQLAlchemy と Pandas を使って、データベースから任意データを取得し、データフレームに変換する方法を紹介しました。

雛形コードも作成・公開したので、自由に改良して使ってください。本記事を参考にアプリを作った人が出てきたら最高に嬉しいです。

はやぶさ
はやぶさ
自由で楽しいプログラミングを満喫してくださいね
Amazonギフト券チャージで最大2.5%ポイント還元
Amazonはチャージがお得

Amazonプライム会員なら、Amazonギフト券を現金でチャージ(コンビニ・銀行払い)すると最大2.5%ポイント還元!

クレジットカード払いでもキャンペーンエントリー0.5%ポイント還元中です。

Amazonでお得に買い物をするならまずはチャージから。

\チャージがお得/

詳細をチェックする

Amazonプライム無料体験中でもOK!

PICK UP BOOKS

  • 数理モデル入門
    数理モデル
  • Jetoson Nano 超入門
    Jetoson Nano
  • 図解速習DEEP LEARNING
    DEEP LEARNING
  • Pythonによる因果分析
    Python