--Utilisation d'Oracle Cloud
Dans la série précédente, seules les instructions SQL fixes étaient traitées, mais en réalité, il existe de nombreuses situations dans lesquelles vous souhaitez utiliser des variables de liaison (espaces réservés). Cette fois, je vais expliquer comment émettre une requête à l'aide de variables de liaison.
Cette fois, nous utiliserons l'exemple de table de schéma SH. Si vous n'utilisez pas la base de données autonome, veuillez sélectionner [Manuel](https://docs.oracle.com/cd/F19136_01/comsc/installing-sample-schemas.html#GUID-A2C3DC59-CDA1-47C0- Vous devez créer un exemple de table de schéma SH selon BA6B-F6EA6395A85F). Il est également nécessaire d'ajuster les exemples d'instructions SQL et les autorisations en fonction de l'environnement. Vous pouvez modifier l'exemple pour utiliser une autre table.
Si vous connaissez l'importance d'utiliser des variables de liaison dans d'autres environnements de développement, veuillez ignorer ceci car c'est une explication ennuyeuse.
Selon la logique de l'application, l'instruction SQL avec exactement le même contenu à l'exception de la valeur de la condition peut être exécutée plusieurs fois pendant l'exécution de l'application. Oracle Database met en cache les informations sur les instructions SQL une fois émises, et lorsque la même instruction SQL est émise, il ne revérifie pas le SQL à partir de zéro, mais utilise les informations mises en cache pour améliorer les performances. Cependant, la cible de l'atteinte du cache est le même SQL dans toute la phrase, par exemple, "SELECT ... WHEWE COL1 = 1" et "SELECT ... WHEWE COL1 = 2" ne diffèrent que par la dernière valeur de condition, mais un SQL différent. Sera traité comme. Cela a tendance à être du SQL qui requiert la clé primaire comme condition, mais si un grand nombre de ces SQL est émis, il y a un problème de performances que l'analyse SQL (vérification de syntaxe, vérification des autorisations, etc.) doit être effectuée à chaque fois. , Vous devez mettre en cache beaucoup de SQL, ce qui met la mémoire sous pression. Afin d'éviter une telle situation, Oracle Database permet un tel partage SQL en créant des variables de valeurs de condition appelées variables de liaison (certains SGBD sont appelés espaces réservés). Par exemple, "SELECT ... WHEWE COL1 =: B01" et les parties "1" et "2" sont remplacées par la variable ": B01", et la valeur réelle est définie (liée) au moment de l'exécution. Les parties qui peuvent être spécifiées pour la variable de liaison sont les parties liées au contenu des données dans la table. Par exemple, vous ne pouvez pas remplacer les noms de colonne ou de table par des variables de liaison. Des variables de liaison peuvent être spécifiées pour les valeurs de colonne et les listes SELECT.
Avant d'expliquer l'instruction SELECT avec des variables de liaison, présentons un modèle qui n'utilise pas de variables de liaison. J'expliquerai cela sous forme de révision.
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 et SQL2 ne sont pas partagés si le codage n'utilise pas de variables de liaison telles que sample07a.py. Il existe deux façons d'utiliser les variables de liaison à partager.
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")
Premièrement, comme il partage SQL, le nombre d'instructions SQL est réduit à un. ": Pc" et ": ps" dans l'instruction SQL sont des variables de liaison. Commencez par ":", puis spécifiez un nom qui respecte les conventions de dénomination de Python. Le contenu des variables de liaison: pc ,: ps est spécifié comme arguments de la méthode execute ().
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)
Au milieu de l'exemple (la partie de l'ensemble de bind_variables1 et 2), créez un dictionnaire qui est une paire du nom de la variable de liaison et de la valeur correspondante, et spécifiez le nom du dictionnaire au moment de execute ().
Lors de l'émission de SQL à l'aide de variables de liaison, l'utilisation du cache d'instructions le rendra plus rapide. Le SQL simple comme l'exemple de cet article ne fait aucune différence, mais le [Manual] de cx_Oracle (https://cx-oracle.readthedocs.io/en/latest/api_manual/connection.html#Connection.stmtcachesize) ) Dit jusqu'à 100 fois, il n'y a donc aucune raison de ne pas l'utiliser.
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)
La méthode prepare () sur la troisième ligne à partir du bas place le SQL dans le cache des instructions. Bien que cela ne soit pas spécifiquement mentionné dans la source, la taille par défaut du cache d'instructions est de 20 (20 instructions SQL). Il est possible de faire référence et de modifier la taille du cache en se référant à ou en modifiant la valeur d'attribut stmtcachesize de l'objet Connection. Lors de l'exécution de SQL, spécifiez None comme premier argument de la méthode execute () car l'instruction SQL correspondante a déjà été préparée. Cela fonctionne même si vous spécifiez une variable de type str SQL préparée au lieu de None. Personnellement, je pense qu'il est plus facile de comprendre que ceux qui spécifient None utilisent prepare, mais veuillez utiliser celui que vous aimez.
Je vois souvent des cas où les instructions SQL sont directement construites sous la forme de f" ... où prod_category = {pc} " ou ... où prod_category =" + pc, mais ce n'est pas très bon. C'est du codage, il y a deux raisons principales.
Cependant, un tel SQL n'est pas toujours mauvais. Sauf si vous avez un SQL qui ne s'exécute qu'une fois par jour et qui ne semble pas logique pour faire la mise en cache, ou un SQL dont vous n'avez pas à vous soucier de l'injection SQL et un plan d'exécution plus rapide qui n'utilise pas de variables de liaison. N'est pas. Gardez à l'esprit la mise en œuvre flexible au cas par cas, sur la base des directives fournies dans cet article.
Recommended Posts