Passing multiple arguments in SQL - Python

Question

I am trying to pass two arguments into a SQL statement as below:

cursor.execute(f"""select * from table 
 where product_name = '{prod_name}' and date = '{sale_date}'"""")

I am trying to have this run through a loop for several combination so I am trying to see how I can have this altered accordingly.

prod_name = ['prod_a','prod_b']
sale_date = ['2020-01-01','2020-02-01']

I know how to pass one argument through a loop but I am not sure how to pass more than one argument together at the same.

Answer 1

It's a security danger to add variables directly to your SQL query. cursor.execute provides sanitizing as long as you pass the arguments as the second argument of the function call.

Example:

cursor.execute("select * form table where product_name = '%s' and date = '%s'", (prod_name, sale_date))

To loop through multiple lists at once you can do the following (assuming the lists have the same amount of values):

for i in range(len(prod_name)):
 cursor.execute("select * form table where product_name = '%s' and date = '%s'", (prod_name[i], sale_date[i]))

By looping through a range I get the numbers of 0 - len(prod_name) and as I loop with the index of i I can use that to retrieve the first item in both lists.

Sam Mason had a good comment about using the zip function which combines iterators and can be used like so:

for args in zip(prod_name, sale_date):
 cursor.execute("select * form table where product_name = '%s' and date = '%s'", args)

• scott martin: I made a small edit to my initial post wherein I did add one more info that I am trying to loop this through several combinations and hence this ask..
• chrislondon: @scottmartin Updated my answer
• Sam Mason: @chrislondon the zip operator can also be useful, e.g. for params in zip(prod_name, sale_date): cur.execute(sql, params)

Answer 2

try this :

results = ()
dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425', '103,2748102', '42,1966402', '42,30262834', '42,6667711', '18,13737683', '42,28921168', '42,26076925', '103,3733654', '42,23313527', '64,3307344', '103,3973533', '42,6360982', '48,11846077', '103,3775309', '64,10122050', '42,1965119', '103,4265810', '103,3971645', '103,4962583', '103,689615', '42,22834366', '103,761655', '95,1184', '64,9594482', '42,22855603', '48,8654764', '103,4226756', '42,23366982', '103,3897036', '42,11339650', '101,6369', '42,25830920', '103,5009291', '42,29238961', '59,6299475', '42,22931663', '42,25839056', '43,11864458', '43,41346192', '103,4261645', '42,3747082', '103,4795050', '42,9417503', '103,4245623', '42,61431911']
try:
 sql = "SELECT * FROM tbl1 WHERE id1 in (%s) AND id2 in (%s)"
 in_ids = ', '.join(map(lambda x: '%s', dc))
 in_ids = in_ids % tuple(dc)
 sql = sql % (in_ids, in_ids)
 cursor.execute(sql)
 res = cursor.fetchall()
 results = results + res
except Exception, e:
 print e