Flask/MySQL WHERE IN clause not working with just one entry

Question

I have a website that gets user input from checkboxes and returns the corresponding rows from a MySQL table.

For example, the user can select a couple colors and the website will show the objects from the table that are that color.

The problem is when the user only selects one color the MySQL query is not created properly and I get an error. Note the colors array below:

So this works:

import MySQLdb
db = MySQLdb.connect(host="...", user="...", port=..., db="...", passwd="...")
colors = ["red", "blue"]
cursor.execute("SELECT * FROM `objects` WHERE `color` IN %s",(colors,))

And this does not:

import MySQLdb
db = MySQLdb.connect(host="...", user="...", port=..., db="...",passwd="...")
colors = ["red"]
cursor.execute("SELECT * FROM `objects` WHERE `color` IN %s", (colors,))

Is there a way to correct this? For now, I am temporarily adding a bogus "color" (one that has no objects linked to it in the database) but this is obviously not an ideal solution.

@MarcB When I do that I get the following error: "1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1"" — ctrlc-sigkill, May 06 '12 at 22:53
It would help to see the query that MySQL sees (from the server logs). Anyway this answer might help: http://stackoverflow.com/questions/4574609/executing-select-where-in-using-mysqldb — jd., May 09 '12 at 08:36

Anurag · Answer 1 · 2013-02-21T11:36:02.910

You can use .join operator.

colors = ["red", "blue"]
colors = ",".join(colors)

output:'red,blue'

colors = ["red"]
colors = ",".join(colors)

output: 'red'

So Code Look Like

import MySQLdb as mdb
con = mdb.connect('', '','','')
with con:
    cur = con.cursor(mdb.cursors.DictCursor)
    colors = ["red","blue"]
    query = """SELECT * FROM objects where color in (""" + ",".join(colors) + """)
    cur.execute(users_query)
    rows = cur.fetchall()

score 0 · Answer 2 · answered May 06 '15 at 12:09

you should confirm your version of MySQLdb egg, I met this question before, this library used connection.literal(o) to deal with the sql vaules like that:

sql = "select * from test where id in %s"
sql_val = [100]
# get connection and cur 
cur.execute(sql, tuple([sql_val]))
# see the last query sql 
cur._last_executed

# version MySQL_python-1.2.3c1-py2.6-linux-x86_64.egg execute exception and the query sql is:
# attention to the last comma after '100' 
select * from test where id in ('100',)

# version MySQL_python-1.2.3c1-py2.6-linux-x86_64.egg execute successfully and the query sql is:
# have no comma after '100' now 
select * from test where id in ('100')

so, maybe you should upgrade your MySQLdb egg to the newest version to fix it.

Flask/MySQL WHERE IN clause not working with just one entry

2 Answers2