PythonでPostgreSQLのORM操作（SQLAlchemy + pg8000）

「個人情報漏えいを防ぎたい」
「SQLインジェクション対策を実施したい」
「PythonでPostgreSQLをORMにより操作したい」

このような場合には、この記事の内容が参考になります。
この記事では、PythonでPostgreSQLをORM操作する方法を解説しています。

本記事の内容

SQLAlchemyによるPostgreSQLのORM操作
動作確認のための準備
pg8000によるデータ取得
SQLAlchemy（pg8000を内部で利用）によるデータ取得

それでは、上記に沿って解説していきます。

SQLAlchemyによるPostgreSQLのORM操作

SQLAlchemyは、PythonでデータベースをORM操作するライブラリです。
ただし、SQLAlchemyはあくまでORMライブラリに過ぎません。

SQLAlchemyのインストールについては、次の記事で説明しています。

このSQLAlchemyに加えて、PostgreSQL用のドライバーが必要となります。
今回は、そのドライバーにpg8000を利用します。

pg8000については、次の記事で説明しています。

以上、SQLAlchemyによるPostgreSQLのORM操作を説明しました。
次は、動作確認のための準備を説明します。

動作確認のための準備

PostgreSQLには、サンプルデータベースが用意されています。

このサンプルデータベースを用いて、動作確認を行います。
具体的には、次のテーブルからデータを取得する動作を確認します。

categoryテーブルには、全部で16件のデータが登録されています。

dvdrental=# select * from category;
 category_id |    name     |     last_update     
-------------+-------------+---------------------
           1 | Action      | 2006-02-15 09:46:27
           2 | Animation   | 2006-02-15 09:46:27
           3 | Children    | 2006-02-15 09:46:27
           4 | Classics    | 2006-02-15 09:46:27
           5 | Comedy      | 2006-02-15 09:46:27
           6 | Documentary | 2006-02-15 09:46:27
           7 | Drama       | 2006-02-15 09:46:27
           8 | Family      | 2006-02-15 09:46:27
           9 | Foreign     | 2006-02-15 09:46:27
          10 | Games       | 2006-02-15 09:46:27
          11 | Horror      | 2006-02-15 09:46:27
          12 | Music       | 2006-02-15 09:46:27
          13 | New         | 2006-02-15 09:46:27
          14 | Sci-Fi      | 2006-02-15 09:46:27
          15 | Sports      | 2006-02-15 09:46:27
          16 | Travel      | 2006-02-15 09:46:27
(16 rows)

以上、動作確認のための準備を説明しました。
次は、pg8000によるデータ取得を説明します。

pg8000によるデータ取得

pg8000によるデータ取得を確認します。
pg8000には、次の2つのAPIが存在しています。

ネイティブなpg8000 API
DB-API 2.0標準API

それぞれのコードを載せておきます。
DB接続のための情報は、各自の環境に合わせて変更してください。

ネイティブなpg8000 API

import pg8000.native
 
con = pg8000.native.Connection(
    host="localhost",
    port="5432",
    user="postgres",
    password="postgres",
    database="dvdrental")
 
result = con.run("SELECT category_id, name FROM category ORDER BY category_id LIMIT 3")
for target in result:
    print(target[0], target[1])
 
con.close()

DB-API 2.0標準API

import pg8000.native
 
con = pg8000.native.Connection(
    host="localhost",
    port="5432",
    user="postgres",
    password="postgres",
    database="dvdrental")
 
result = con.run("SELECT category_id, name FROM category ORDER BY category_id LIMIT 3")
for target in result:
    print(target[0], target[1])
 
con.close()

上位3件（category_idの昇順）をSELECTするSQL文を実行しています。
それぞれのコードを実行した結果は、以下。

1 Action
2 Animation
3 Children

両方のコードとも、全く同じ結果となります。

以上、pg8000によるデータ取得を説明しました。
次は、SQLAlchemy（pg8000を内部で利用）によるデータ取得を説明します。

SQLAlchemy（pg8000を内部で利用）によるデータ取得

SQLAlchemyによるORM操作の場合は、次のコードを用います。
こちらも設定は、各自の環境に合わせてください。

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

DATABASE = 'postgresql+pg8000'
USER = 'postgres'
PASSWORD = 'postgres'
HOST = 'localhost'
PORT = '5432'
DB_NAME = 'dvdrental'

CONNECT_STR = '{}://{}:{}@{}:{}/{}'.format(DATABASE, USER, PASSWORD, HOST, PORT, DB_NAME)

Base = declarative_base()


class Category(Base):
    category_id = Column(Integer, primary_key=True)
    name = Column(String(length=25))

    __tablename__ = 'category'


engine = sqlalchemy.create_engine(CONNECT_STR, echo=False)
session = sessionmaker(bind=engine)()
query_result = session.query(Category).order_by(Category.category_id).limit(3)
for target in query_result:
    print(target.category_id, target.name)

Categoryクラスは、categoryテーブルをもとに作成しています。
また、今回はドライバーにpg8000を利用します。

そのことは、次の箇所で設定しています。