CODEWITHSUNDEEP
pythonsql
Alpha001
Alpha001

Reputation: 371

Python: Passing the list variables as parameters in the query

The requirement is to pass the parameters in a list to the sql query present in python code



parmlist.py
  
sale_call = [12,88]
yr_range = [2015,2016,2017,2018]
    


Codefile.py

     conn = <connecting to MySQL>
    
     cursor = conn.cursor()

cursor.execute(‘Insert into sales_repo select distinct a.yr_sll from cust_data a join proc_data b 
On a.prd_key = b.prd_key where b.range1=?,b.range2=?,b.range3=? and b.yr in ?’)

I did the following:



cursor.execute(‘Insert into sales_repo select distinct a.yr_sll from cust_data a join proc_data b 
On a.prd_key = b.prd_key where b.range1=?,b.range2=?,b.range3=? and b.yr in ?’ ,parmist.sale_call[0],parmist.sale_call[1],parmist.yr_range[3])
    


But it seems that the parmist.yr_range[3] is just taking 2018. Instead of taking whole list of yr_range = [2015,2016,2017,2018]

It's just taking the last value i.e. 2018



How can I pass the whole list as a variable in the query?

Update_1:

I tried the following:

sale_call = [12,88]
yr_range = [2015,2016,2017,2018]

cursor.execute(‘Insert into sales_repo select distinct a.yr_sll from cust_data a join proc_data b 
On a.prd_key = b.prd_key where b.range1=?,b.range2=?,b.range3=? and b.yr in (' + ','.join(map(str, yr_range))’)

The above is not working when I tried to execute it through the python code. But executing when using pandas as:

pd.read_sql_query(‘Insert into sales_repo select distinct a.yr_sll from cust_data a join proc_data b 
    On a.prd_key = b.prd_key where b.range1=?,b.range2=?,b.range3=? and b.yr in (' + ','.join(map(str, yr_range))’,conn)

Any hint why it's not working?

Upvotes: 0

Views: 545

Answers (1)

Karl
Karl

Reputation: 1714

I think the error is just somewhere in how you were formatting your query string. It seems wrong in both cases but maybe the pandas query is handling it. This makes the query a bit easier to follow.

And you need to specify what the parameters are if you use the ? for the b.range values in the WHERE clause. I took them out because I am not sure how you were intending to use them. The .format() function replaces what is {} in the string to be the years as a comma separated string value.

years = ','.join(map(str, yr_range))
cursor.execute("INSERT INTO sales_repo SELECT DISTINCT a.yr_sll FROM cust_data a "
               "JOIN proc_data b ON a.prd_key = b.prd_key " 
               "WHERE b.yr IN ({});".format(years))

Upvotes: 2

Related Questions