Are you confusing about SQLite Python Placeholders? It’s time for better understanding! Learn how in this tutorial.
Introduction
SQLite is an embedded open-source relational database engine. Its developers, from SQLite.org, call it a self-contained, serverless, zero-configuration, fast, reliable and transactional SQL database engine. It keeps things simple. SQLite “just works.”
The SQLite library is also integrated into a number of popular scripting languages such as Python. The built-in sqlite3 module with Python was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.
According to “The Python Library Reference”, the SQL statements may be parameterized (i.e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style). Other two methods using traditional %s placeholder and string operation are also available for SQL statements.
Examples
## 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"))Qmark Method – Officially Recommended
## 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 recordsQmark Style Method (“Qmark Method”) is a concise way for fixed number of SQL conditions (query parameters). It is recommended to “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. ”
Named Method – Officially Recommended
## 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 recordsNamed Style Method (“Named Method”) is also a quite Pythonic way for fixed SQL conditions.
%s Method – Traditional, Insecure
## 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%s Formatting Method (“%s Method”) is a traditional method in code bases of Python beginners and programmers who switch to Python coming from other programming languages such as Java or C++. It’s readable but “The Python Library Reference” recommends “Never do this — insecure!”
String Method – Insecure, Sometimes Useful
## 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 recordsString Concatenation Method (“String Method”) looks not as concise as other three (3) methods. Although “The Python Library Reference” states “You shouldn’t assemble your query using Python’s string operations because doing so is insecure”, as shown in and in below figure (https://xkcd.com/327/) it is very useful and flexible if your query has arbitrary combination of SQL conditions and multiple tables. You can use many “if” clauses to combine query commands. Just be careful for your SQL statements.
Summary
Qmark Method and Named Method are recommended by “The Python Library Reference”, %s Method and String Method may be insecure however useful for some specific instances.
References
- Guido van Rossum and the Python development team, The Python Library Reference (Release 3.8.2), 18 March 2020
- Jay A. Kreibich, Using SQLite – Small, Fast, Reliable, Choose Any Three, August 2010
Where to Go From Here?
Enough theory. Let’s get some practice!
Coders get paid six figures and more because they can solve problems more effectively using machine intelligence and automation.
To become more successful in coding, solve more real problems for real people. That’s how you polish the skills you really need in practice. After all, what’s the use of learning theory that nobody ever needs?
You build high-value coding skills by working on practical coding projects!
Do you want to stop learning with toy projects and focus on practical code projects that earn you money and solve real problems for people?
🚀 If your answer is YES!, consider becoming a Python freelance developer! It’s the best way of approaching the task of improving your Python skills—even if you are a complete beginner.
If you just want to learn about the freelancing opportunity, feel free to watch my free webinar “How to Build Your High-Income Skill Python” and learn how I grew my coding business online and how you can, too—from the comfort of your own home.