CODEWITHSUNDEEP
pythonmysqlpython-3.x
Deva K
Deva K

Reputation: 87

Pass list values to MySQL query using Python

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

Upvotes: 2

Views: 2985

Answers (2)

user1986815
user1986815

Reputation:

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)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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

Upvotes: 2

Related Questions