Using parameters in query to search between dates-CodePudding

Silly newbe here. So I'm banging my head on this: Can't quite figure out the parameterized query and if it's properly formatted.

import sqlite3

def readSqliteTable():
    try:
        sqliteConnection = sqlite3.connect('testDB.sqlite')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")
        startdate = "2022-11-05"
        enddate = "2022-11-25"
        print("startdate =", startdate, "enddate =", enddate)
        cursor.execute("SELECT * FROM tz WHERE UL_Time BETWEEN '%s' AND '%s'" % (startdate, enddate))
        print(cursor.fetchall())
        records = cursor.fetchall()
        print("Total rows are:  ", len(records))
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Updated: ", row[1])
            print("Title: ", row[2])
            print("UL_Time: ", row[3])
            print("Size: ", row[4])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

It works fine if I substitute arbitrary dates as:

cursor.execute("SELECT * FROM tz WHERE UL_Time BETWEEN 2022-11-01 AND 2022-11-25")

but won't work in this form

CodePudding user response：

First of all, you don't understand what a parameterized query is. Read the official Python documentation and do tutorials.

Your expression

"SELECT * FROM tz WHERE UL_Time BETWEEN '%s' AND '%s'" % (startdate, enddate))

shows string interpolation and has nothing to do with parameterized queries. This query also implements textual filter. It should work properly, so long all your dates are formatted as YYYY-MM-DD.

The second query is meaningless, as your WHERE clause defines an integer filter:

WHERE UL_Time BETWEEN 2010 AND 1986

CodePudding user response：

Got it. I made a goof in the format of the initialization of the two variables startdate & enddate by having a hyphen between year and month. And I see my stupid arithmetic calculation in the second query. Sometimes you have to have another point out the obvious to you.

Thanks!