[PYTHON] [Introduction to cx_Oracle] (Part 7) Handling of bind variables

Verification environment

--Using Oracle Cloud

Introduction

In the past series, only fixed SQL statements were dealt with, but in reality, there are many situations where you want to use bind variables (placeholders). This time, I will explain how to issue a query using bind variables.

Advance preparation

This time we will use the sample SH schema table. If you are not using Autonomous Database, please use the [Manual](https://docs.oracle.com/cd/F19136_01/comsc/installing-sample-schemas.html#GUID-A2C3DC59-CDA1-47C0-] depending on your environment. You need to create a sample table of SH schema according to BA6B-F6EA6395A85F). In addition, it is necessary to adjust sample SQL statements and permissions according to the environment. You may modify the sample to use another table.

What is a bind variable?

If you know the significance of using bind variables in other development environments, please skip this as it is a boring explanation.

Depending on the logic of the application, the SQL statement with exactly the same contents except for the condition value may be executed many times while the application is running. Oracle Database caches information about SQL statements once issued, and when the same SQL statement is issued, performance is gained by using the cached information without rechecking the SQL from scratch. However, since the target of cache hit is exactly the same SQL, for example, "SELECT ... WHEWE COL1 = 1" and "SELECT ... WHEWE COL1 = 2" differ only in the last condition value, but different SQL. It is treated as. It tends to be SQL that requires a primary key as a condition, but if a lot of such SQL is issued, there is a performance problem that SQL parsing (syntax check, permission check, etc.) must be performed every time. , You have to cache a lot of SQL, which takes up memory. To avoid such a situation, Oracle Database enables such SQL sharing by making a condition value called a bind variable (some DBMS is called a placeholder) variable. For example, "SELECT ... WHEWE COL1 =: B01" and the "1" and "2" parts are replaced with the variable ": B01", and the actual value is set (bound) at runtime. The parts that can be specified for the bind variable are the parts related to the data contents in the table. For example, you cannot replace column or table names with bind variables. Bind variables can be specified for column values and SELECT lists.

SELECT statement using bind variables

Before explaining the SELECT statement with bind variables, let's present a pattern that does not use bind variables. I will explain this in the form of revision.

sample07a.py


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)

SQL1 and SQL2 are not shared in coding that does not use bind variables, such as sample07a.py. There are two ways to use bind variables to be shared.

Specify the contents of the bind variable at the time of execute ()

sample07b.py


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")

First, because it shares SQL, the number of SQL statements is reduced to one. ": Pc" and ": ps" in the SQL statement are bind variables. Start with a ":" and then specify a name that follows Python's naming conventions. The contents of the bind variables: pc,: ps are specified as arguments of the execute () method.

Create a dictionary type of bind variable and specify it at runtime

sample07c.py


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)

In the middle of the sample (the part of the set of bind_variables1 and 2), create a dictionary that is a pair of the bind variable name and the corresponding value, and specify the dictionary name at the time of execute ().

SQL statement Prepare

When issuing SQL using bind variables, using the statement cache will make it run faster. Simple SQL like the sample in this article doesn't make much difference, but cx_Oracle's manual ) Says up to 100 times, so there is no reason not to use it.

sample07d.py


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)

The prepare () method on the third line from the bottom puts the SQL in the statement cache. Although not specifically mentioned in the source, the default size of the statement cache is 20 (20 SQL statements). You can refer to and change the cache size by referring to the attribute value stmtcachesize of the Connection object or changing the value. When executing SQL, since the corresponding SQL statement has already been prepared, specify None as the first argument of the execute () method. It works even if you specify a prepared SQL str type variable instead of None. Personally, I think it's easier to understand that those who specify None are using prepare, but please use the one you like.

Avoid constructing SQL statements with F strings or string concatenation

I often see cases where SQL statements are directly constructed in the form of f" ... where prod_category = {pc} " or ... where prod_category =" + pc, but this is not very good. It's coding. There are two main reasons.

--The SQL statements generated by these logics are reprinted from the explanation given earlier this time, but they may prevent the sharing of SQL statements that can be made into bind variables, causing performance down and memory exhaustion. there is. --In the case of an application that can pass a character string to be added to SQL from outside the application such as an argument, it becomes possible to cause a security breach by SQL injection.

However, such SQL is not always bad. Unless you have SQL that runs only once a day and doesn't seem to make sense for caching, or SQL that you don't have to worry about SQL injection, and you have a faster execution plan without using bind variables. Is not. Keep in mind the flexible implementation on a case-by-case basis, based on the guidelines provided in this article.

Recommended Posts

[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] (16th) Handling of LOB types
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
Introduction to PyQt4 Part 1
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
Introduction to Ansible Part ③'Inventory'
Series: Introduction to cx_Oracle Contents
Introduction to Ansible Part ④'Variable'
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
Introduction to Ansible Part 2'Basic Grammar'
Introduction to Python Hands On Part 1
Introduction to Ansible Part 1'Hello World !!'
Web-WF Python Tornado Part 3 (Introduction to Openpyexcel)
[Introduction to Udemy Python3 + Application] 65. Exception handling
[Introduction to Data Scientists] Basics of Probability and Statistics ♬ Probability / Random Variables and Probability Distribution
Introduction to Scapy ① (From installation to execution of Scapy)
[Introduction to Data Scientists] Basics of Python ♬
Kaggle: Introduction to Manual Feature Engineering Part 1
[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
[Introduction to Udemy Python 3 + Application] 26. Copy of dictionary
[Introduction to Udemy Python 3 + Application] 19. Copy of list
From the introduction of pyethapp to the execution of contract
[Introduction to Python] Basic usage of lambda expressions
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction of Python
Introduction of scikit-optimize
Introduction to Tkinter 1: Introduction
Introduction of PyGMT
Introduction to PyQt
Introduction to Scrapy (2)
[Linux] Introduction to Linux
Introduction to machine learning ~ Let's show the table of K-nearest neighbor method ~ (+ error handling)
Introduction to Scrapy (4)
Introduction to discord.py (2)
Introduction of cymel
Introduction to discord.py
Introduction of Python
[Chapter 6] Introduction to scikit-learn with 100 knocks of language processing
[Introduction to Udemy Python3 + Application] 53. Dictionary of keyword arguments
[Chapter 3] Introduction to Python with 100 knocks of language processing
[Chapter 2] Introduction to Python with 100 knocks of language processing
I tried to erase the negative part of Meros
[Introduction to Python] Basic usage of the library matplotlib
[Introduction to Udemy Python3 + Application] 52. Tupleization of positional arguments
Introduction to Python numpy pandas matplotlib (~ towards B3 ~ part2)
[Chapter 4] Introduction to Python with 100 knocks of language processing