Êtes-vous confus à propos des espaces réservés SQLite Python ? Il est temps de mieux comprendre ! Découvrez comment dans ce didacticiel.
Présentation
SQLite est un moteur de base de données relationnelle open source intégré. Ses développeurs, de SQLite.org, l'appellent un moteur de base de données SQL autonome, sans serveur, sans configuration, rapide, fiable et transactionnel. Cela simplifie les choses. SQLite "fonctionne tout simplement".
La bibliothèque SQLite est également intégrée dans un certain nombre de langages de script populaires tels que Python. Le module intégré sqlite3 avec Python a été écrit par Gerhard Häring. Il fournit une interface SQL conforme à la spécification DB-API 2.0 décrite par la PEP 249.
Selon "The Python Library Reference", les instructions SQL peuvent être paramétrées (c'est-à-dire des espaces réservés au lieu de littéraux SQL). Le module sqlite3 prend en charge deux types d'espaces réservés :les points d'interrogation (style qmark) et les espaces réservés nommés (style nommé). Deux autres méthodes utilisant l'espace réservé traditionnel %s et l'opération de chaîne sont également disponibles pour les instructions SQL.
Exemples
## Dependencies
import sqlite3
## Create Groundwater Assay Table
def create_db():
sql_ = "CREATE TABLE IF NOT EXISTS gw_assay \
( \
point_id TEXT NOT NULL, \
analyte TEXT NOT NULL, \
content REAL NOT NULL DEFAULT -999, \
sampling_date, \
PRIMARY KEY (point_id, analyte, sampling_date) \
);"
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
cnn.commit()
cnn.close()
## Add a batch of records into the db - using "?" placeholders, or question marks (qmark style)
def add_records():
sql_ = "INSERT OR IGNORE INTO gw_assay VALUES (?, ?, ?, ?)"
# Simplified assay data extracted from real project
records = [("M09", "1,2-Dichloroethane", 0.0014, "2019-04-03"),
("M09", "1,2-Dichloroethane", 11.4, "2019-05-28"),
("M09", "1,2-Dichloroethane", 21.5, "2019-07-11"),
("M09", "1,2-Dichloroethane", 28.7, "2019-07-25"),
("M09", "1,2-Dichloroethane", 0.184, "2019-09-16"),
("M09", "1,2-Dichloroethane", 1.64, "2019-11-08"),
("M09", "1,2-Dichloroethane", 2.05, "2019-12-09"),
("M09", "1,2-Dichloroethane", 0.365, "2019-12-31"),
("M09", "1,2-Dichloroethane", 0.38, "2020-01-14"),
("M09", "1,2-Dichloroethane", 0.453, "2020-02-13"),
("M09", "COD", 687, "2019-04-03"),
("M09", "COD", 836, "2019-05-28"),
("M09", "COD", 57.4, "2019-07-10"),
("M09", "COD", 608, "2019-07-11"),
("M09", "COD", 520, "2019-07-25"),
("M09", "COD", 576, "2019-07-26"),
("M09", "COD", 63.2, "2019-09-16"),
("M09", "COD", 219, "2019-12-09"),
("M09", "COD", 131, "2019-12-31"),
("M09", "COD", 111, "2020-01-14"),
("M09", "COD", 118, "2020-02-13"),
("M09", "COD", 103, "2020-02-27"),
("M09", "pH", 7.57, "2019-04-03"),
("M09", "pH", 6.32, "2019-05-28"),
("M09", "pH", 6.3, "2019-07-11"),
("M09", "pH", 6.11, "2019-07-25"),
("M09", "pH", 5.64, "2019-09-16"),
("M09", "pH", 6.31, "2019-11-08"),
("M09", "pH", 6.89, "2019-12-09"),
("M09", "pH", 6.67, "2019-12-31"),
("M09", "pH", 6.72, "2020-01-14"),
("M09", "pH", 6.63, "2020-02-13"),
("M09", "pH", 6.76, "2020-02-27"),
("SMD13", "1,2-Dichloroethane", 0.176, "2019-05-13"),
("SMD13", "1,2-Dichloroethane", 1.52, "2019-05-28"),
("SMD13", "1,2-Dichloroethane", 6.27, "2019-06-04"),
("SMD13", "1,2-Dichloroethane", 1.11, "2019-09-15"),
("SMD13", "1,2-Dichloroethane", 0.829, "2019-12-10"),
("SMD13", "1,2-Dichloroethane", 1.32, "2019-12-31"),
("SMD13", "1,2-Dichloroethane", 1.72, "2020-01-14"),
("SMD13", "1,2-Dichloroethane", 0.0137, "2020-02-13"),
("SMD13", "COD", 34.7, "2019-05-13"),
("SMD13", "COD", 28.9, "2019-05-28"),
("SMD13", "COD", 14.7, "2019-06-04"),
("SMD13", "COD", 219, "2019-09-15"),
("SMD13", "COD", 37, "2019-12-10"),
("SMD13", "COD", 395, "2019-12-31"),
("SMD13", "COD", 334, "2020-01-14"),
("SMD13", "COD", 18.8, "2020-02-13"),
("SMD13", "COD", 4.52, "2020-02-26"),
("SMD13", "pH", 3.81, "2019-05-13"),
("SMD13", "pH", 3.73, "2019-05-28"),
("SMD13", "pH", 3.71, "2019-06-04"),
("SMD13", "pH", 3.48, "2019-09-15"),
("SMD13", "pH", 3.99, "2019-12-10"),
("SMD13", "pH", 3.63, "2019-12-31"),
("SMD13", "pH", 3.68, "2020-01-14"),
("SMD13", "pH", 4.38, "2020-02-13"),
("SMD13", "pH", 6.49, "2020-02-26")]
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.executemany(sql_, records)
cnn.commit()
cnn.close()
## Select data - using "?" placeholders, or question marks (qmark style)
# Put ? as a placeholder wherever you want to use a value,
# and then provide a tuple of values as the second argument to the cursor’s execute() method
def query_qmark(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = ? AND analyte = ? AND sampling_date = ?"
par_ = (point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return records
## Select data - using named placeholders (named style)
def query_named(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = :id AND analyte = :a AND sampling_date = :d"
par_ = {"id": point_id, "a": analyte, "d": sampling_date}
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return records
## Select data - using traditional "%s" placeholder
def query_format_s(point_id="", analyte="", sampling_date=""):
sql_ = 'SELECT * FROM gw_assay WHERE point_id = "%s" AND analyte = "%s" ' \
'AND sampling_date = "%s"' %(point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return records
## Select data - using string concatenation
# You shouldn’t assemble your query using Python’s string operations because doing so is insecure;
# it makes your program vulnerable to an SQL injection attack
def query_string_concatenation(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = " + '"' + point_id + '"' \
+ " AND " + "analyte = " + '"' + analyte + '"' \
+ " AND " + "sampling_date = " + '"' + sampling_date + '"'
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return records
#========main=============
if __name__ == "__main__":
create_db()
add_records()
print(query_qmark("M09", "COD", "2019-07-11"))
print(query_named("M09", "COD", "2019-07-11"))
print(query_format_s("M09", "COD", "2019-07-11"))
print(query_string_concatenation("M09", "COD", "2019-07-11")) Méthode Qmark - Officiellement recommandée
## Select data - using "?" placeholders, or question marks (qmark style)
# Put ? as a placeholder wherever you want to use a value,
# and then provide a tuple of values as the second argument to the cursor’s execute() method
def query_qmark(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = ? AND analyte = ? AND sampling_date = ?"
par_ = (point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return records La méthode de style Qmark ("méthode Qmark") est un moyen concis pour un nombre fixe de conditions SQL (paramètres de requête). Il est recommandé de « Mettre ? comme espace réservé partout où vous souhaitez utiliser une valeur, puis fournissez un tuple de valeurs comme deuxième argument de la méthode execute() du curseur. ”
Méthode nommée - Officiellement recommandée
## Select data - using named placeholders (named style)
def query_named(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = :id AND analyte = :a AND sampling_date = :d"
par_ = {"id": point_id, "a": analyte, "d": sampling_date}
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return records La méthode de style nommée ("Méthode nommée") est également une méthode assez Pythonique pour les conditions SQL fixes.
Méthode %s – Traditionnelle, non sécurisée
## Select data - using traditional "%s" placeholder
def query_format_s(point_id="", analyte="", sampling_date=""):
sql_ = 'SELECT * FROM gw_assay WHERE point_id = "%s" AND analyte = "%s" ' \
'AND sampling_date = "%s"' %(point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return records La méthode de formatage %s ("méthode %s") est une méthode traditionnelle dans les bases de code des débutants et des programmeurs Python qui passent à Python à partir d'autres langages de programmation tels que Java ou C++. C'est lisible mais "The Python Library Reference" recommande "Ne jamais faire ça - non sécurisé !"
Méthode de chaîne :non sécurisée, parfois utile
## Select data - using string concatenation
# You shouldn’t assemble your query using Python’s string operations because doing so is insecure;
# it makes your program vulnerable to an SQL injection attack
def query_string_concatenation(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = " + '"' + point_id + '"' \
+ " AND " + "analyte = " + '"' + analyte + '"' \
+ " AND " + "sampling_date = " + '"' + sampling_date + '"'
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return records La méthode de concaténation de chaînes ("méthode de chaîne") ne semble pas aussi concise que les trois (3) autres méthodes. Bien que "The Python Library Reference" indique "Vous ne devriez pas assembler votre requête en utilisant les opérations de chaîne de Python car cela n'est pas sûr" , comme indiqué dans et dans la figure ci-dessous (https://xkcd.com/327/), il est très utile et flexible si votre requête a une combinaison arbitraire de conditions SQL et de plusieurs tables. Vous pouvez utiliser de nombreuses clauses "if" pour combiner des commandes de requête. Faites juste attention à vos instructions SQL.
Résumé
La méthode Qmark et la méthode nommée sont recommandées par "The Python Library Reference", %s La méthode et la méthode de chaîne peuvent être non sécurisées mais utiles pour certaines instances spécifiques.
Références
- Guido van Rossum et l'équipe de développement Python, The Python Library Reference (Release 3.8.2) , 18 mars 2020
- Jay A. Kreibich, Utilisation de SQLite – Petit, rapide, fiable, choisissez-en trois , août 2010
