Python: Passing the list variables as parameters in the query

Question

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?

In parmist.yr_range[3] the [3] specifies the fourth item. Try parmist.yr_range. - docs.python.org/3/tutorial/introduction.html#lists — wwii
– wwii, Commented Aug 30, 2018 at 21:33
@wwii I have updated my question with the latest trials made. — Alpha001
– Alpha001, Commented Aug 30, 2018 at 23:02
Is the year range always between a lower and upper year bounds? Ex: 2015 <= b.range <= 2018 Because if so then you don't need to look for every matching value, only those greater then the smallest year and less then the largest year. — Karl
– Karl, Commented Aug 31, 2018 at 0:18
@Karl Doesn't seems that year range is the issue here. The issue is that the same query is getting executed using Pandas but not using Cursor via Python code. If possible, please help me to understand the reason for this. — Alpha001
– Alpha001, Commented Aug 31, 2018 at 0:33
Well one problem I notice is you are using 3 ?'s seeming like you want to pass in parameters to the query, but you never actually specify what those paremeters are supposed to be. — Karl
– Karl, Commented Aug 31, 2018 at 0:48

Karl · Accepted Answer · 2018-08-31 00:53:14Z

2

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))

answered Aug 31, 2018 at 0:53

Karl

1,7442 gold badges14 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Python: Passing the list variables as parameters in the query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related