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.

chrislondon · Accepted Answer · 2020-07-30 21:44:31Z

3

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)

edited Jul 30, 2020 at 21:44

answered Jul 30, 2020 at 14:15

chrislondon

12.6k5 gold badges29 silver badges66 bronze badges

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

3 Comments

scott martin Over a year ago

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 Over a year ago

@scottmartin Updated my answer

Sam Mason Over a year ago

@chrislondon the zip operator can also be useful, e.g. for params in zip(prod_name, sale_date): cur.execute(sql, params)

Florence · Accepted Answer · 2020-07-30 14:24:49Z

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

Collectives™ on Stack Overflow

Passing multiple arguments in SQL - Python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related