Database Query with dynamic parameters in python

I’m building a code to query a database using python. Input is from a dictionary. I have written code for passing one parameter(key) in the query,

cursor = conn.execute("SELECT * FROM table1 WHERE param = '%s'" % kwargs['param'])

The dictionary items count I’m gonna pass to this line may vary. So, after WHERE the code need to be written to query all the keys of dictionary to database.

My code is almost completed except for this part. Tried Python Dynamic Parameterized Query. But it throws some Operational error. I’m using sqlite3 here.

Answer

Without seeing the error message, I can’t be sure what the error is. But you were not doing a parameterized query. The way you want to be doing the query is as follows:

Passing actual value(s) as a tuple:

cursor = conn.execute("SELECT * FROM table1 WHERE param = %s", (kwargs['param'],))

Or passing actual value(s) as a list:

cursor = conn.execute("SELECT * FROM table1 WHERE param = %s", [kwargs['param']])

Note:

1. There are no quotes, ', around the %s parameters.
2. The actual values for the %s parameters are supplied in either a list or tuple.

Note above that when passing the actual value in a tuple, the tuple is specified as (kwargs['param'],). The expression (kwargs['param']) (without the comma) would be interpreted as a simple term with a parentheses around it and not as a tuple, so the comma at the end is required when you have a single value.

You were doing textual substitution of kwargs['param'] for %s and then surrounding the result with quotes, which is altogether different (what if kwargs['param'] contained a single quote?). And depending on the source of kwargs['param'], you would be leaving yourself open to a SQL Injection attack (you should investigate this topic).

Update

If you have a dictionary, kwargs, whose keys are the names of the columns to be used in the WHERE clause, for example:

kwargs = {'param1': 1, 'param2': 'a', 'param3': 'x'}

then:

>>> kwargs = {'param1': 1, 'param2': 'a', 'param3': 'x'}
>>> where_clause = 'WHERE ' + ' AND '.join(['`' + k + '` = %s' for k in kwargs.keys()])
>>> where_clause
'WHERE `param1` = %s AND `param2` = %s AND `param3` = %s'
>>> values = list(kwargs.values())
>>> values
[1, 'a', 'x']

And so we get:

where_clause = 'WHERE ' + ' AND '.join(['`' + k + '` = %s' for k in kwargs.keys()])
values = list(kwargs.values())
sql = "SELECT * FROM table1 " + where_clause
cursor.execute(sql, values)