python sqlite qmark "consume" parameters-CodePudding

my python code:

datefilter="2022-01-11"
if condition:
  sql="SELECT * FROM TABLE;"
else:
  sql="SELECT * FROM TABLE WHERE DATE = ?;"
curs.execute(sql,(datefilter,))

The error I got when condition is true:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

Is there a way to "consume" this parameter without using it into the sql query?

I already tried the following but it's not working:

sql="SELECT * FROM TABLE; -- ?"

CodePudding user response：

Any non-null expression that does not evaluate to 0, which is interpreted as false when used as a boolean expression, can be used wherever you want the evaluation to be true.

This query:

SELECT * FROM TABLE;

is equivalent to:

SELECT * FROM TABLE WHERE '2022-01-11'; -- yes, this is valid in SQLite

because '2022-01-11' is implicitly converted to a non-zero numeric value (=2022 which is the starting numeric part of '2022-01-11'), so it is interpreted as true, making the query equivalent to:

SELECT * FROM TABLE WHERE TRUE;

For your case, assuming that the parameter that you pass is a date, so that it will always be converted to true when used as described above, you could write the code like this:

datefilter="2022-01-11"
if condition:
  sql="SELECT * FROM TABLE WHERE ?;"
else:
  sql="SELECT * FROM TABLE WHERE DATE = ?;"
curs.execute(sql,(datefilter,))