【Python】PostgreSQLを操作する③ -SQLにパラメータを渡す

DB

前回はpsycopg2でPostgresqlに対してSELECT文を実行する方法について紹介しました。

【Python】PostgreSQLを操作する② -SELECT結果を取得する

psycopg2では、SQLの中にパラメータ（変数）を埋め込むこともできます。変数を埋め込むことで、プログラム内で動的にSQLを実行することができるようになります。

今回は、パラメータをSQLに埋め込んで実行する方法について紹介します。

目次

準備

まずはpsycopg2のインポートおよびDBへの接続です。

import psycopg2

#接続
connector =  psycopg2.connect('postgresql://{user}:{password}@{host}:{port}/{dbname}'.format( 
            user="postgres",        #ユーザ
            password="postgresql",  #パスワード
            host="localhost",       #ホスト名
            port="5432",            #ポート
            dbname="dvdrental"))    #データベース名

#カーソル取得
cursor = connector.cursor()

パラメータを渡してSELECT

それではパラメータを渡してSELECT文を実行する流れを見ていきます。

まずはパラメータを加えず、そのままSQLを実行する例からいきましょう。

cursor.execute('SELECT * FROM city WHERE city_id = 50')
row = cursor.fetchone() 
print(row)

>> (50, 'Balikesir', 97, datetime.datetime(2006, 2, 15, 9, 45, 25))

決め打ちで文字列を作成してSQLを実行しました。では、上記の50をパラメータとして文字列の外から入力します。

cursor.execute('SELECT * FROM city WHERE city_id = %s', (50,))
row = cursor.fetchone() 
print(row)

>> (50, 'Balikesir', 97, datetime.datetime(2006, 2, 15, 9, 45, 25))

%sはpythonにおける文字列のプレースホルダです。%sには後に指定したある値が文字列として入ります。

psycopg2ではパラメータをタプルで指定します。上記の場合は%sには文字列に変換された50が入ります。なお、パラメータはタプルで指定するので、値が1つであっても後ろのカンマは外さないように注意してください（エラーになります）。

複数のパラメータを同時に渡す

上の例の応用として、2つ以上のパラメータも同時に指定することができます。

cursor.execute('SELECT * FROM city WHERE city_id = %s AND country_id = %s', (50, 97))
row = cursor.fetchone() 
print(row)

>> (50, 'Balikesir', 97, datetime.datetime(2006, 2, 15, 9, 45, 25))

この場合、指定したパラメータ前方から順番に代入されていきます。例えば上記の場合、実行されるSQLは以下のようになります。

SELECT * FROM city WHERE city_id = 50 AND country_id = 97

これくらいのクエリならすんなり理解できますが、実際のクエリはもう少し長く複雑な場合が多いです。そんなときに前から順番にパラメータを、、なんて言ってたら、一体どこの%sになにを代入しているのかが一目ではわからなくなってしまいます。そんなときのために名前付きでパラメータを渡すこともできます。

名前付きパラメータを渡す

今度はパラメータ1つ1つの値に名前を付けてSQLに代入していきましょう。

cursor.execute('SELECT * FROM city WHERE city_id = %(city_id)s AND country_id = %(country_id)s', 
                   {"city_id":50, "country_id":97})

row = cursor.fetchone() 
print(row)


>> (50, 'Balikesir', 97, datetime.datetime(2006, 2, 15, 9, 45, 25))

1つ目のパラメータには”city_id”という名前を、2つ目のパラメータには”country_id”という名前を付けました。この場合パラメータの指定はタプルではなく辞書形式で記述します。

パラメータの代入位置と値は名前で紐づいているので、代入の順番は考慮されません（気にせず書いてよいです）。また、city_idを複数箇所に何度も代入するといったことも可能です。

まとめ

psycopg2を使ったSQLで、パラメータを渡して実行する方法を紹介しました。

実際の開発では変数を渡した動的なSQL実行はほぼ必須だと思うので、是非活用してください。

ではでは👋

DBの最新記事8件