検証環境
- Oracle Cloud利用
- Oracle Linux 7.7 (VM.Standard2.1)
- Python 3.6
- cx_Oracle 7.3
- Oracle Database 19.5 (ATP, 1OCPU)
- Oracle Instant Client 18.5
はじめに
過去の連載では固定SQL文のみ扱っていましたが、実際にはバインド変数(プレースホルダ)を使用したい場面も多いかと思います。今回はバインド変数を使用したクエリの発行方法について解説します。
事前準備
今回はサンプルのSHスキーマのテーブルを使用します。Autonomous Databaseをお使いでない方は、環境に応じて、マニュアルに従ってSHスキーマのサンプルテーブルを作成する必要があります。また、環境に応じてサンプルのSQL文や権限などの調整も必要です。他のテーブルを利用する形にサンプルを改変いただいても構いません。
バインド変数とは
他の開発環境などでバインド変数を利用することの意義をご存じの方には、退屈な解説なので読み飛ばしてください。
アプリケーションのロジックによっては、アプリケーション稼働中に、条件値以外は全く同じ内容のSQL文を何度も実行する場合があります。Oracle Databaseでは一度発行したSQL文に関する情報をキャッシュして、同じSQL文が発行された際に、一からSQLをチェックし直したりせず、キャッシュされた情報を使用することでパフォーマンスを稼ぎます。しかし、キャッシュヒットする対象は全文が全く同じSQLですので、例えば「SELECT ... WHEWE COL1 = 1」と「SELECT ... WHEWE COL1 = 2」は最後の条件値が異なるだけですが、異なるSQLとして扱われます。主キーを条件にするようなSQLでありがちですが、このようなSQLが多数発行されると、毎回SQLの解析(構文チェックや権限チェックなど)を実施せねばならなくなるというパフォーマンスの問題もありますが、多数のSQLをキャッシュしなければならなくなり、メモリを圧迫してしまいます。このような事態を避けるため、Oracle Databaseではバインド変数(プレースホルダと呼ぶDBMSもあります)という、条件値を変数化させることで、このようなSQLの共有を可能にしています。例えば、「SELECT ... WHEWE COL1 = :B01」と、「1」や「2」の部分を「:B01」という変数に置き換え、実行時に実際の値をセット(バインド)させる形を取ります。
なお、バインド変数に指定できる箇所は、テーブル内のデータ内容に関わる個所となります。例えば列名やテーブル名をバインド変数に置き換えることはできません。バインド変数は列の値やSELECTリストなどを対象に指定が可能です。
バインド変数を使用したSELECT文
バインド変数を使用したSELECT文を説明する前に、バインド変数を使用していないパターンを提示します。これを改定していく形で解説していきます。
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
select prod_id, prod_name from sh.products
where prod_category = 'Photo' and prod_subcategory = 'Camera Batteries'
"""
SQL2 = """
select prod_id, prod_name from sh.products
where prod_category = 'Software/Other' and prod_subcategory = 'Bulk Pack Diskettes'
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.execute(SQL1)
cursor.execute(SQL2)
sample07a.pyのような、バインド変数を使用しないコーディングだと、SQL1とSQL2は共有されません。共有されるようにバインド変数を使用するには、2種類の方法があります。
execute()時にバインド変数の内容を指定
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select prod_id, prod_name from sh.products
where prod_category = :pc and prod_subcategory = :ps
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.execute(SQL, pc="Photo", ps="Camera Batteries")
cursor.execute(SQL, pc="Software/Other", ps="Bulk Pack Diskettes")
まず、SQLを共有するので、SQL文が一つに減っています。SQL文中の「:pc」と「:ps」がバインド変数です。「:」で始めて、あとはPythonの命名規則に従った名前を指定してください。バインド変数:pc, :psの内容は、execute()メソッドの引数として指定します。
バインド変数の辞書型を作成して、実行時に指定
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select prod_id, prod_name from sh.products
where prod_category = :pc and prod_subcategory = :ps
"""
bind_variables1 = {"pc":"Photo", "ps":"Camera Batteries"}
bind_variables2 = {"pc":"Software/Other", "ps":"Bulk Pack Diskettes"}
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.execute(SQL, bind_variables1)
cursor.execute(SQL, bind_variables2)
サンプルの真ん中あたり(bind_variables1,2のセットの部分)で、バインド変数名と対応する値の組になる辞書を作成し、execute()時にその辞書名を指定します。
SQL文のPrepare
バインド変数を使用してSQLを発行する場合、ステートメントキャッシュを利用すると、より高速に動くようになります。本稿のサンプルのようなシンプルなSQLだと差はなかなか出ませんが、cx_Oracleのマニュアルには最大100倍と書かれていますので、利用しない手はありません。
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select prod_id, prod_name from sh.products
where prod_category = :pc and prod_subcategory = :ps
"""
bind_variables1 = {"pc":"Photo", "ps":"Camera Batteries"}
bind_variables2 = {"pc":"Software/Other", "ps":"Bulk Pack Diskettes"}
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.prepare(SQL)
cursor.execute(None, bind_variables1)
cursor.execute(None, bind_variables2)
下から3行目の、prepare()メソッドでSQLをステートメントキャッシュに入れています。ソース中には特に出て来ていませんが、ステートメントキャッシュのデフォルトサイズは20(SQL文20個分)です。Connectionオブジェクトの属性値stmtcachesizeを参照したり値を変更したりすることでキャッシュサイズを参照・変更することが可能です。
SQLを実行する際には、該当のSQL文は既にprepareしているので、execute()メソッドの第一引数にはNoneを指定します。NoneではなくprepareしたSQLのstr型変数を指定しても動きます。個人的にはNone指定の方がprepareを利用していることがわかりやすいかなと思いますが、お好きな方をご利用ください。
F文字列や文字列連結でSQL文を組み立てることを避ける
f"... where prod_category = {pc}"とか、... where prod_category = " + pcといった形でSQL文を直接組み立てているケースをよく見ますが、これは非常によくないコーディングです。理由は大きく二つあります。
- これらのロジックで生成されたSQL文は、今回の最初の方で行った説明の再掲になりますが、バインド変数化できる部分のSQL文の共有を妨げ、パフォーマンスダウンやメモリ枯渇を引き起こす可能性があります。
- 引数などアプリケーション外部からSQLへ付加する文字列を渡せるアプリケーションの場合、SQLインジェクションによるセキュリティ侵害を発生させることが可能になってしまいます。
ただ、このようなSQLが常にダメというわけではありません。1日1回しか稼働しない、キャッシングを行う意味がなさそうなSQLや、SQLインジェクションの心配がないようなSQLで、さらにバインド変数を使用しない方がより高速な実行計画になる場合は、その限りではありません。本稿で書かれている指針をベースとしつつ、ケースバイケースで柔軟な実装を心がけてください。