Check for multiple values exists in database in MySQLdb python?

Question

My requirement is to check if there are entries in a table matching a list of values for a column. I am using the following query:

insert_enquiry =  """SELECT P_ID,Jobname FROM build_table WHERE Build_number IN %s;""" % insert_constraint

insert_constraint here is a list of numbers. Build_number is a column which has integers. If I try to execute this query using:

cursor.execute(insert_enquiry)

I get the following error:

MySQL 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 '[184, 931, 1005, 1070, 165, 99, 365, 930, 164, 98' at line 1

I believe this is because of the square brackets that appear in the list. I tried to remove them by converting the list to string, but then I get the same error as now the integer list is converted to a string.

I have also tried the following syntax:

insert_enquiry =  """SELECT P_ID,Jobname FROM product_build WHERE Build_number IN %s;"""
cursor.executemany(insert_enquiry, insert_constraint)

This give the following error:

MySQL 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 '184' at line 1

Edit:

insert_enquiry =  """SELECT P_ID,Jobname FROM product_build WHERE Build_number IN (%s);"""
cursor.executemany(insert_enquiry, insert_constraint)

This takes only the last element in the list insert_constraint for some reason.

Yes, I have made an edit to the questions. It somehow take the last element in the list insert_constraint and skips all the elements from the list. — prakyath j, Oct 21 '16 at 14:41

score 0 · Answer 1 · edited May 23 '17 at 10:27

Thanks to https://stackoverflow.com/a/4574647/4077331.

The idea was to add len(insert_constraint) number of %s to the query string. By doing this, the query will accept a list for the WHERE IN clause

insert_enquiry =  "SELECT build_number,jobname FROM %s WHERE build_number IN (%s);"
insert_enquiry_string =', '.join(map(lambda x: '%s', insert_constraint_build_num))
insert_enquiry = insert_enquiry % (table_name, insert_enquiry_string)
cursor.execute(insert_enquiry, insert_constraint_build_num)

This solved the issue.

Check for multiple values exists in database in MySQLdb python?

1 Answers1