PythonでSQLAlchemyを使ってpostgreSQLを操作してみる

ヒノマルク

pythonからデータベースに接続するライブラリでSQLAlchemyというものがあります色々な記事で使い方が載っていますが、よく理解せずに利用していました。今回公式ページをきちんと読んでまとめてみました。

SQLAlchemyに関しては下記ページでpsycopg2をインストールするときに少しご紹介しています。

PythonからPsycopg2を使ってPostgreSQLのデータを操作してみる

明けましておめでとうございます。2022年の最初の投稿になります、ヒノマルクです。前回投稿した日から暫く経ってしまいました。PythonからPostgreSQLにアクセスできるようにしようと思います。Psycopgについて今回はPsycop...

SQLAlchemyを利用する上で理解しておきたい点
SQLAlchemyでpostgreSQLへアクセス
参照

SQLAlchemyを利用する上で理解しておきたい点

Object Relational Mapper(ORM)ライブラリなので、別途DBと接続するためのライブラリはインストールが必要
1. Pythonだったら、psycopg2やpy8000などのインストール。Oracleだったらcx_Oracleのインストールが事前に必要です。
2. hiveやBigQueryもPyHiveやpybigqueryというライブラリでの接続が対応されているようです。
対応ライブラリは下記ページにまとめられています。

Dialects — SQLAlchemy 1.4 Documentation
docs.sqlalchemy.org
データの抽出してpandasにデータを格納するのみの利用であれば、わざわざORMを利用しなくてもよいのかも。ただしDBへのURLを変更するだけで、異なるDBへもアクセスできるので汎用性は高い。好み次第。
postgreSQLしか調べていないが、対応バージョンがSQLAlchemyとpsycopg2で異なる。

SQLAlchemy

Fully tested in CI 9.6, 10, 11, 12, 13

psycopg2

Supports PostgreSQL versions from 7.4 to 14

※ どちらも22年1月29日時点の情報です。

古いバージョンや新しいバージョンはSQLAlchemyだと遅れての対応になっているのかも知れません。接続しようとしているデータベースのバージョンは要チェックです。企業だと古いバージョンを使っている場合があるかも知れません。

SQLAlchemyでpostgreSQLへアクセス

仮想環境を準備

SQLAlchemy用の仮想環境を用意

$ python3 -m venv sqlalchemy-test

まっさら状態での挙動を確かめたかったので、仮想環境を新たに用意します。

pipを最新版にアップグレード

$ /Users/hinomaruc/Desktop/notebooks/sqlalchemy-test/bin/python3 -m pip install --upgrade pip

Out[0]

Requirement already satisfied: pip in ./sqlalchemy-test/lib/python3.9/site-packages (21.1.3)
Collecting pip
  Using cached pip-21.3.1-py3-none-any.whl (1.7 MB)
Installing collected packages: pip
  Attempting uninstall: pip
    Found existing installation: pip 21.1.3
    Uninstalling pip-21.1.3:
      Successfully uninstalled pip-21.1.3
Successfully installed pip-21.3.1

SQLAlchemyのインストール

$ /Users/hinomaruc/Desktop/notebooks/sqlalchemy-test/bin/python3 -m pip install sqlalchemy

Out[0]

Collecting sqlalchemy
  Downloading SQLAlchemy-1.4.31.tar.gz (8.0 MB)
     |████████████████████████████████| 8.0 MB 143 kB/s
  Preparing metadata (setup.py) ... done
Collecting greenlet!=0.4.17
  Downloading greenlet-1.1.2.tar.gz (91 kB)
     |████████████████████████████████| 91 kB 6.9 MB/s
  Preparing metadata (setup.py) ... done
Using legacy 'setup.py install' for sqlalchemy, since package 'wheel' is not installed.
Using legacy 'setup.py install' for greenlet, since package 'wheel' is not installed.
Installing collected packages: greenlet, sqlalchemy
    Running setup.py install for greenlet ... done
    Running setup.py install for sqlalchemy ... done
Successfully installed greenlet-1.1.2 sqlalchemy-1.4.31

greenletというライブラリもインストールされました。
並列処理用のライブラリのようです。
psycopg2などもインストールされるかと思いましたが、依存関係としてはインストールされないようです。

jupyter notebookのインストール

$ /Users/hinomaruc/Desktop/notebooks/sqlalchemy-test/bin/python3 -m pip install jupyter notebook

Out[0]

Collecting jupyter
  Downloading jupyter-1.0.0-py2.py3-none-any.whl (2.7 kB)
Collecting notebook
  Downloading notebook-6.4.8-py3-none-any.whl (9.9 MB)
     |████████████████████████████████| 9.9 MB 56 kB/s
・・・省略・・・
Successfully installed
・・・省略・・・

それぞれの仮想環境にjupyterをインストールしなくても、特定のjupyter上で複数の仮想環境を選択する方法もあります。

とても便利なので、そちらはまた別途記事にしておこうと思います。

記事にしました。 ↓

[Mac] 複数のPython仮想環境をJupyter notebook上で切り替える方法

今回は Pythonの仮想環境をvenvで作成する方法の応用版になります。jupyter notebook内で複数の仮想環境を切り替えて利用できるようにする方法をまとめました。図にすると下記のようになります。図1:メイン環境のmy-venv...

またjupyterで動作確認をするとパソコンが重くなるといった人は、ipythonを利用するかcolaboratoryで動作確認をすることをおすすめします。

sqlalchemyをインポートしpostgreSQLにアクセス

sqlalchemyのcreate_engineをインポート

from sqlalchemy import create_engine

SQLAlchemyのengineを作成

# isolation_level="AUTOCOMMIT"にしておく。しておかないとセッションが残り続けてテーブルをロックしていることがあった。
# 詳しくは　https://docs.sqlalchemy.org/en/14/dialects/postgresql.html を参照
engine = create_engine('postgresql://postgres:secret@localhost:5432/postgres',isolation_level="AUTOCOMMIT")

Out[0]

・・・省略・・・
--> 782     import psycopg2
    784     return psycopg2
ModuleNotFoundError: No module named 'psycopg2'

予想はしていましたが、psycopg2がないとエラーが出ますので、インストールします。

psycopg2のインストール

export PATH=/Applications/Postgres.app/Contents/Versions/14/bin/:$PATH
/Users/hinomaruc/Desktop/notebooks/sqlalchemy-test/bin/python3 -m pip install --no-binary :all: psycopg2

Out[0]

Collecting psycopg2
  Using cached psycopg2-2.9.3.tar.gz (380 kB)
  Preparing metadata (setup.py) ... done
Skipping wheel build for psycopg2, due to binaries being disabled for it.
Installing collected packages: psycopg2
    Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.9.3

コネクションの作成とSQLの実行

from sqlalchemy import text
with engine.connect() as conn:
    conn.execute(text("CREATE TABLE some_table (x int, y int)"))
    conn.execute(text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),[{"x": 1, "y": 1}, {"x": 2, "y": 4}])

テーブルの作成とデータの挿入をSQLコマンドで実行しました。
SQLAlchemyのtext関数を利用してSQL文を発行するようです。

作成したテーブルをSELECT文で抽出してみる

with engine.connect() as conn:
    result = conn.execute(text("SELECT x, y FROM some_table"))
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

Out[0]

x: 1  y: 1
x: 2  y: 4

表示されました。

コネクションをクローズしておく

conn.close()

自動的にコネクションが閉じているような気がしますが、念のためクローズしておきます。

SQLAlchemyからpandasのデータフレームにデータを格納する

やっぱりやりたいのはデータフレームに格納ですよね？
pandasのread_sqlメソッドで出来ます。

まずはSQLAlchemyのエンジンの作成

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:secret@localhost:5432/postgres',isolation_level="AUTOCOMMIT")

当記事のコードを最初から順に実行されている方は、もう実行済みなのでスキップしてもかまいません。

pandasのread_sqlメソッドでデータを読み込む

import pandas as pd
query = """SELECT x, y FROM some_table"""
df = pd.read_sql(query,engine)

先ほど作成した some_tableテーブルを使います。
作成していない方は下記queryでテーブル一覧を取得しても問題ありません。

query = """SELECT tablename FROM pg_catalog.pg_tables"""

上から5件取得

df.head()

Out[0]

	x	y
0	1	1
1	2	4

参照

https://docs.sqlalchemy.org/en/14/tutorial/engine.html
https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.create_engine
https://docs.sqlalchemy.org/en/14/core/engines.html#database-urls
https://github.com/dropbox/PyHive#sqlalchemy
https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#basics-of-statement-execution
https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#
https://www.psycopg.org/features/