--Using Oracle Cloud
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.
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.
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.
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.
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.
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 ().
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.
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