「SQLインジェクションを対策したい」
「PythonでPostgreSQLのORM操作を行いたい」
このような場合には、この記事の内容が参考になります。
この記事では、PythonでPostgreSQLをORM操作する方法を解説しています。
本記事の内容
- SQLAlchemyによるPostgreSQLのORM操作
- 動作確認のための準備
- psycopg2によるデータ取得
- SQLAlchemy（psycopg2を内部で利用）によるデータ取得
それでは、上記に沿って解説していきます。
SQLAlchemyによるPostgreSQLのORM操作
SQLAlchemyは、PythonでデータベースをORM操作する際の選択肢となります。
ただし、SQLAlchemyはあくまでORMライブラリに過ぎません。
SQLAlchemyのインストールについては、次の記事で説明しています。
データベースへの接続には、ドライバーを利用します。
今回であれば、対象データベースがPostgreSQLになります。
PostgreSQLのインストールについては、以下の記事で説明しています。
PostgreSQLのドライバーと言えば、psycopg2の名前が真っ先に出てきます。
PythonにおけるPostgreSQLのドライバーとしては、psycopg2が最も利用されているでしょう。
これらを用いて、SQLAlchemyによるPostgreSQLのORM操作が可能となります。
そのため、次の２つをインストールする必要があります。
- SQLAlchemy
- psycopg2
以上、SQLAlchemyによるPostgreSQLのORM操作について説明しました。
次は、動作確認のための準備を説明します。
動作確認のための準備
PostgreSQLには、サンプルデータベースが用意されています。
このサンプルデータベースを用いて、動作確認を行います。
具体的には、次のテーブルからデータを取得する動作を確認します。
データ件数は、全部で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)
あと、PythonからPostgreSQLにアクセスするユーザーを用意する必要があります。
上記記事に従えば、ユーザーpostgresでサンプルデータベースを作成しているはずです。
postgresで作成している場合は、postgresのパスワードを設定しておきましょう。
postgresには、パスワード未設定の場合がありえます。
その場合は、Pythonからアクセスできません（パスワードを求められる）。
パスワードの設定には、次のクエリを実行します。
ただし、ユーザーpostgresでアクセスした状態である必要があります。
ALTER USER postgres WITH PASSWORD 'postgres';
プロンプト表示が「postgres=#」であれば、ユーザーpostgresでアクセスした状態です。
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
以上、動作確認のための準備を説明しました。
次は、psycopg2によるデータ取得を説明します。
psycopg2によるデータ取得
まずは、psycopg2によるデータ取得を確認します。
各自で必要な箇所は、変更してください。
import psycopg2
DATABASE = 'postgresql'
USER = 'postgres'
PASSWORD = 'postgres'
HOST = 'localhost'
PORT = '5432'
DB_NAME = 'dvdrental'
CONNECT_STR = '{}://{}:{}@{}:{}/{}'.format(DATABASE, USER, PASSWORD, HOST, PORT, DB_NAME)
conn = psycopg2.connect(CONNECT_STR)
cur = conn.cursor()
cur.execute('SELECT category_id, name FROM category ORDER BY category_id LIMIT 3')
rows = cur.fetchall()
for target in rows:
print(target[0], target[1])
cur.close()
conn.close()
昇順にソートして、上位3件をSELECTするSQL文を実行しています。
上記コードを実行した結果は、以下。
1 Action 2 Animation 3 Children
想定通りの動きをしています。
以上、psycopg2によるデータ取得を説明しました。
次は、SQLAlchemy（psycopg2を内部で利用）によるデータ取得を説明します。
SQLAlchemy（psycopg2を内部で利用）によるデータ取得
SQLAlchemyによるORM操作の場合は、次のコードを用います。
こちらも設定は、各自の環境に合わせてください。
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
DATABASE = 'postgresql'
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テーブルをもとに作成しています。
データ取得は、次のコードで実現。
query_result = session.query(Category).order_by(Category.category_id).limit(3)
全データ取得は、次のコードで表現できます。
query_result = session.query(Category)
今回は、昇順に上位3件を取得します。
そのため、以下をコードに追記する形となります。
| 昇順にソート | order_by(Category.category_id) |
| 上位3件 | limit(3) |
上記コードを実行した結果は、以下。
1 Action 2 Animation 3 Children
psycopg2のみで取得した結果と同じです。
ORM操作では、SQL文を記載していません。
しかし、裏では同じSELECT文が実行されているはずです。
それを確認したい場合は、次のコードを変更します。
engine = sqlalchemy.create_engine(CONNECT_STR, echo=False)
↓↓↓
engine = sqlalchemy.create_engine(CONNECT_STR, echo=True)
変更して実行すると、以下の結果となります。
2022-07-09 13:31:40,066 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2022-07-09 13:31:40,066 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-07-09 13:31:40,069 INFO sqlalchemy.engine.Engine select current_schema()
2022-07-09 13:31:40,069 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-07-09 13:31:40,069 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2022-07-09 13:31:40,069 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-07-09 13:31:40,070 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-09 13:31:40,073 INFO sqlalchemy.engine.Engine SELECT category.category_id AS category_category_id, category.name AS category_name
FROM category ORDER BY category.category_id
LIMIT %(param_1)s
2022-07-09 13:31:40,074 INFO sqlalchemy.engine.Engine [generated in 0.00024s] {'param_1': 3}
1 Action
2 Animation
3 Children
結果から、実行するクエリを確認できますね。
以上、SQLAlchemy（psycopg2を内部で利用）によるデータ取得を説明しました。
