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?

Yes you have to create SQL string

itzMEonTV
– itzMEonTV

2015-04-06 01:03:09 +00:00
Commented Apr 6, 2015 at 1:03 — itzMEonTV
– itzMEonTV, Commented Apr 6, 2015 at 1:03

CL. · Accepted Answer · 2015-04-06 07:31:16Z

5

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)

answered Apr 6, 2015 at 7:31

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optional parameters in a SQLite SELECT statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related