【 Python + PostgreSQL 】with ステートメントとコネクションプール ( connection pool ) を活用して、効率的で安全なデータベースコネクション、カーソル ( cursor ) の供給、利用ができるようにしよう!(その1) 投稿一覧へ戻る

Tags: Python , PostgreSQL , connectionpool , with , psycopg2

Published 2020年7月9日23:05 by T.Tsuyoshi

今回はちょっと趣向を変えてデータベースを扱います。


まずは前提です。


システムにはすでに PostgreSQL と psycopg2 がインストールされており Python プログラムから利用可能になっていて、Learning データベースには countries テーブルがあり以下のようなデータが入っています。


create table if not exists countries (
id serial primary key,
name character varying(255),
code character varying(10)
);

insert into countries (name, code) values ('Japan', 'JPN');




また、このテーブルのデータを扱うために Country クラスを作成しました。


import psycopg2


class Country:
def __init__(self, name, code, id=None):
self.name = name
self.code = code
self.id = id

def __repr__(self):
return f"Country(name={self.name!r}, code={self.code!r}, id={self.id})"

def save_to_db(self):
with psycopg2.connect(database="Learning", user="postgres", password="1234", host="localhost") as connection:
with connection.cursor() as cursor:
cursor.execute('INSERT INTO countries (name, code) VALUES (%s, %s)',
(self.name, self.code))

@classmethod
def load_from_db_by_name(cls, name):
with psycopg2.connect(database="Learning", user="postgres", password="1234", host="localhost") as connection:
with connection.cursor() as cursor:
cursor.execute('SELECT * FROM countries WHERE name = %s', (name,))
country = cursor.fetchone()
return cls(name=country[1], code=country[2], id=country[0])



動作確認です。


result = Country.load_from_db_by_name('Japan')
print(result)
# Country(name='Japan', code='JPN', id=1)


new_country = Country('Taiwan', 'TWN')
new_country.save_to_db()


result = Country.load_from_db_by_name('Taiwan')
print(result)
# Country(name='Taiwan', code='TWN', id=2)



さて、ここからが本番です。


まず Country クラスで気になるのは、データベースコネクションを作成するコードの重複です。
冗長ですし、データベース名、ユーザー名等を変更する際は全ての箇所を書き直さなければなりません。
別に関数を作って1箇所にまとめてしまいましょう。


def connect():
return psycopg2.connect(database="Learning", user="postgres", password="1234", host="localhost")


class Country:
def __init__(self, name, code, id=None):
self.name = name
self.code = code
self.id = id

def __repr__(self):
return f"Country(name={self.name!r}, code={self.code!r}, id={self.id})"

def save_to_db(self):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute('INSERT INTO countries (name, code) VALUES (%s, %s)',
(self.name, self.code))

@classmethod
def load_from_db_by_name(cls, name):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute('SELECT * FROM countries WHERE name = %s', (name,))
country = cursor.fetchone()
return cls(name=country[1], code=country[2], id=country[0])


result = Country.load_from_db_by_name('Taiwan')
print(result)
# Country(name='Taiwan', code='TWN', id=2)


new_country = Country('New Zealand', 'NZL')
new_country.save_to_db()


result = Country.load_from_db_by_name('New Zealand')
print(result)
# Country(name='New Zealand', code='NZL', id=3)



さて次に気になるのは...
Country クラスの各メソッドは呼び出されるたびに with ステートメント内でデータベースとのコネクションを確立します。
そして作業が終了すれば、with ステートメントが connection.commit()、connection.close() を実行してデータを保存しコネクションを閉じてくれます。


しかし、データベースとのやり取りを行う上で、データベースとの接続を確立する作業はパフォーマンス的にコストが高いですよね。
ですから、コネクションの確立、廃棄を繰り返すというのは可能な限り避けたいわけです。


そこで、確立したコネクションをすぐさま閉じるのではなく、取っておいて使い回しをしよう、ということになります。
これがコネクションプール ( connection pool ) の基本的な考えです。


まず手始めに、コネクションプールが作られた時点で1つのデータベースコネクションを作成するように設定し、その1つのコネクションを使い回してみます。


from psycopg2 import pool


connection_pool = pool.SimpleConnectionPool(minconn=1,
maxconn=1,
database="Learning",
user="postgres",
password="1234",
host="localhost")


class Country:
def __init__(self, name, code, id=None):
self.name = name
self.code = code
self.id = id

def __repr__(self):
return f"Country(name={self.name!r}, code={self.code!r}, id={self.id})"

def save_to_db(self):
with connection_pool.getconn() as connection:
with connection.cursor() as cursor:
cursor.execute('INSERT INTO countries (name, code) VALUES (%s, %s)',
(self.name, self.code))

@classmethod
def load_from_db_by_name(cls, name):
with connection_pool.getconn() as connection:
with connection.cursor() as cursor:
cursor.execute('SELECT * FROM countries WHERE name = %s', (name,))
country = cursor.fetchone()
return cls(name=country[1], code=country[2], id=country[0])


result = Country.load_from_db_by_name('New Zealand') # 1:
print(result)
# Country(name='New Zealand', code='NZL', id=3)


new_country = Country('Germany', 'DEU')
new_country.save_to_db() # 2:
# Traceback (most recent call last):
# psycopg2.pool.PoolError: connection pool exhausted



おやおや、エラーが出力されました。プールにコネクションが無いよ、というエラーですね。


今の設定ではコネクションプールには1つのコネクションしかありません。
ですが、それを使い回しするために「プール」から取り出したり戻したりするわけです。では何故エラーが?


これには with ステートメントの働きが関わっています。


1: のコードを実行すると、load_from_db_by_name() 内の with connection_pool.getconn() ... ステートメントによってコネクションプールからコネクションが1つ取り出されます。


作業が終わって with ステートメントを抜ける時には何が行われるんでしたっけ?
connection.commit() と connection.close() によってデータが保存され、接続が切られちゃうんでした。


コネクションプールの中に唯一あったコネクションが破棄されちゃったわけです。ですからコネクションプールは空です。
この状態で続けて 2: のコードを実行して再びコネクションプールからコネクションを取り出そうとしたのでエラーとなったわけです。


つまり今回問題になっているのは、プールから取り出したコネクションを、本来 connection_pool.putconn(connection) でプールへ戻してやらなければいけないにもかかわらず、with ステートメントでは「プールから取ったコネクションだから戻さなきゃ」という判断ができないために「作業終わったから閉じなきゃ」とコネクションを破棄してしまったことなんです。


では with ステートメントの「後処理はちゃんとします」という機能を活かしつつ、使い終わったコネクションを閉じずにプールに戻すにはどうしたらいいのでしょうか?


解決策は、with ステートメントでの利用をサポートする __enter__ 特殊メソッドと __exit__ 特殊メソッドを実装した独自クラスを作成することです。


class ConnectionPool:
def __init__(self):
self.connection_pool = pool.SimpleConnectionPool(minconn=1,
maxconn=1,
database="Learning",
user="postgres",
password="1234",
host="localhost")

def __enter__(self):
""" with ステートメントに入る段階で呼び出されます"""
return self.connection_pool.getconn()

def __exit__(self, exc_type, exc_val, exc_tb):
""" with ステートメントから出る段階で呼び出されます"""
pass



まだ __exit__ 特殊関数は実装していませんから with ステートメント終了時にコネクションを回収できていないため、
データベースへのアクセスを連続して行おうとするとエラーになるはずです。


ちょっと試してみましょう。
ただし、with ステートメントを抜ける段階でデータの保存もできないのは困りますから、save_to_db() には connection.commit() を追加しています。


class Country:
def __init__(self, name, code, id=None):
self.name = name
self.code = code
self.id = id

def __repr__(self):
return f"Country(name={self.name!r}, code={self.code!r}, id={self.id})"

def save_to_db(self):
with ConnectionPool() as connection:
with connection.cursor() as cursor:
cursor.execute('INSERT INTO countries (name, code) VALUES (%s, %s)',
(self.name, self.code))
connection.commit()

@classmethod
def load_from_db_by_name(cls, name):
with ConnectionPool() as connection:
with connection.cursor() as cursor:
cursor.execute('SELECT * FROM countries WHERE name = %s', (name,))
country = cursor.fetchone()
return cls(name=country[1], code=country[2], id=country[0])


result = Country.load_from_db_by_name('New Zealand')
print(result)
# Country(name='New Zealand', code='NZL', id=3)


new_country = Country('Germany', 'DEU')
new_country.save_to_db()


result = Country.load_from_db_by_name('Germany')
print(result)
# Country(name='Germany', code='DEU', id=4)



????????
エラーが出ずに正常に終了しました、コネクションを3回も消費しているのに...


コネクションプールにはコネクションが1つしかありません。
ConnectionPool クラスの __exit__ 特殊メソッドは何も行いませんし、
コード中の何処を探してもコネクションをプールに戻すための putconn() の記述はありません。


それなのに正常終了?


これはある意味で単純なミステークです。
今回の with ステートメントの構文は with ConnectionPool() as connection: と、クラスをインスタンス化しているわけです。
ですから with ステートメントによる __enter__ 特殊メソッドの呼び出しの前に __init__ 初期化メソッドが呼ばれているんですね。


つまり with ステートメントを実行するたびに、
1: 新しいコネクションプールが作成され
2: 新しいコネクションが作成されて渡される
という手順を踏んでいたわけです。


結局、パフォーマンス的に状況は悪くなってしまっているんです。いちいちコネクションプールまで呼ばれる回数分作っているわけですから。


ではどうしたらいいのでしょう?


まずやらなければいけないことは
1: __exit__ 特殊関数において __enter__ 特殊関数で渡したコネクションをプールに戻すこと
2: そして、with ステートメントが開始される時点でいちいちコネクションプールが作成されないようにする、ことです。


今回もちょっと長めになってしまいましたが、まだまだ話は続きます。
でも今日はここまで。


ぜひ上の課題の解決策を考えてみてください。

「その2」の記事は こちら からどうぞ。

この投稿をメールでシェアする

0 comments

コメントはまだありません。

コメントを追加する(不適切と思われるコメントは削除する場合があります)