SQLite Python Placeholder – Four Methods for SQL Statements

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 records

Qmark 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 records

Named 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 records

String 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!

To become successful in coding, you need to get out there and solve real problems for real people. That’s how you can become a six-figure earner easily. And 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?

Practice projects is how you sharpen your saw in coding!

Do you want to become a code master by focusing on practical code projects that actually earn you money and solve problems for people?

Then become 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.

Join my free webinar “How to Build Your High-Income Skill Python” and watch how I grew my coding business online and how you can, too—from the comfort of your own home.

Join the free webinar now!