Database Query with dynamic parameters in python

Question

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.

Always include error messages (and not just the name of the exception), these contain vital information that helps to isolate your issue. — Tim
– Tim, Commented Mar 28, 2020 at 10:56

Booboo · Accepted Answer · 2020-03-29 16:56:06Z

2

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:

There are no quotes, ', around the %s parameters.
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)

edited Mar 29, 2020 at 16:56

answered Mar 28, 2020 at 11:55

Booboo

45.7k4 gold badges46 silver badges74 bronze badges

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

9 Comments

Jose25X Over a year ago

I have passed a dictionary to kwargs, for instance kwargs contain {'param':'value', 'param2':'value2', 'param3':'value3'}. Since code need to be completed, I have limited code with one key-value pair(kwargs['param']). This number of dict values are varying based on input. I'm stuck now with part after WHERE - %s (number of placeholder to be there for kwargs) & passing all kwargs key to the placeholder. On searching, read about SQL injection. Still learning about that :). Thanks for info on passing passing tuple '''(kwargs['param'],)'''

Booboo Over a year ago

I have updated my answer what may be useful information.

Jose25X Over a year ago

Did some small changes to get the output[docs.python.org/2/library/sqlite3.html], where_clause='WHERE'+'AND'.join([''+k+'=?'for k in kwargs.keys()]) On using '%s' as placeholder in where_clause(Editedwhere_clause='WHERE'+'AND'.join(['' +k+'=\'%s\''for k in kwargs.keys()])) I observed error,

cursor=conn.execute(sql,values) sqlite3.ProgrammingError:Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied

Found lot of solutions but none for current statement uses 0. Asking this for my undersanding. Huge thanks for that solution.

Jose25X Over a year ago

And may I ask any reference to understand that .join function used.I'm clear with .join functionality. But, in the mentioned solution, it is quite difficult for me to understand(e.g Key values getting placed between WHERE & AND).

Booboo Over a year ago

Does this help? docs.python.org/3/library/stdtypes.html?highlight=join#str.join For example, ','.join(['a','b','c']) -> 'a,b,c'

|

Collectives™ on Stack Overflow

Database Query with dynamic parameters in python

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related