I want to pass list values along with other parameter values. following is my scenario. I want to pass multiple values for column "Code" and want to pass single value to "Continent" column.
param = [('AFG', 'IND'),'Asia']
query = "select * from country where Code in (%s) AND Continent = %s"
cursor.execute(query,param)
while executing in Python, I am getting following error.
Failed to execute Query: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type
The trick here is the WHERE IN clause, which isn't really amenable to being parameterized. One option generates an IN clause with the exact number of placeholders in your list:
codes = ('AFG', 'IND')
continent = 'Asia'
params = codes + (continent,)
where_in = ','.join(['%s'] * len(codes))
sql = "SELECT * FROM country WHERE Code IN (%s) AND Continent = %s" % (where_in, '%s')
cursor.execute(sql, params)
To see what the above script actually did, lets look at the various parts:
print(where_in)
print(sql)
%s,%s
SELECT * FROM country WHERE Code IN (%s,%s) AND Continent = %s
The trick here is that we actually use a %s placeholder twice, once for the Python string, and a second time for the SQL query string. Also, we bind a single level tuple containing all bound values:
('AFG', 'IND', 'ASIA')
where_in variables, one for each WHERE IN clause, and you would have to substitute and then bind both of them. I won't update my answer, but if you still have doubts feel free to open a new question.first you split the list and then you split the tuple.
param = [('AFG', 'IND'),'Asia']
p1,p2=param[0]
query = "select * from country where Code in ('%s','%s') AND Continent = %s" % (p1,p2,param[1])
cursor.execute(query)
%s placeholders should not be in single quotes (which partially defeats the purpose of using a statement). Also, you are hardcoding WHERE IN plus two items, when in fact future readers of this question might want a solution for an arbitrary number.