Optional parameters in a SQLite SELECT statement

Question

I am using the sqlite3 module in Python 3 to work with SQLite.

I have a SELECT statement that may execute with a varied number of parameters depending on user input. For example, if no parameter is provided by the user, then the statement is simply:

SELECT * FROM TestTable WHERE User=?

where each user's ID is known by default.

On the other hand, if one or more parameters are supplied by user to refine the selection, then the statement can become something like:

SELECT * FROM TestTable WHERE User=? AND ColA=? AND ColB=?

To handle this kind of varied user input, is there a native way in SQLite to support optional parameters in SELECT statements? Or I just need to programmatically generate the appropriate SQL string first using Python based on user input, and then use the derived string to execute the SQL?

Answer 1

It would be possible to set unused parameters to NULL, and check for that in the query:

sql = "SELECT ... WHERE User=?1 AND (?2 IS NULL OR ColA=?2) ..."
cursor.execute(sql, [user, a_or_null])

However, this makes the query both complex and slow to execute.

It would be easier to construct the query string dynamically:

sql = "SELECT ... WHERE User=?"
params = [user]
if a_is_specified:
    sql += " AND ColA=?"
    params += [a]
...
cursor.execute(sql, params)