I am using mysql.connector to query values from the database in python. I need to write a select query with "IN" in the where clause. I tried to pass the values as a list, tuple, etc. But it is throwing the error that the "Python 'data-type' cannot be converted to a MySQL type"
import mysql.connector
from mysql.connector import Error
analyticsdb = mysql.connector.connect(
pool_name = 'analyticspool',
pool_size = 10,
host="localhost",
user="root",
passwd="",
database="analytics"
)
analyticsCursor = analyticsdb.cursor(dictionary=True)
gender_values = ['Male', 'Female']
registeredQuery = "SELECT COUNT(DISTINCT uid) AS registered FROM `users` WHERE gender IN (%s)"
placeholders = (gender_values)
analyticsCursor.execute(registeredQuery, placeholders)
regCount = analyticsCursor.fetchone()
According to the documentation here https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html the parameters need to be of type tuple and there should be one %s for each of them.
I would suggest the following changes:
gender_values = ('Male', 'Female')
registeredQuery = "SELECT COUNT(DISTINCT uid) AS registered FROM `users` WHERE gender IN (%s, %s)"
placeholders = gender_values
Dynamic gender values
registeredQuery = "SELECT COUNT(DISTINCT uid) AS registered FROM `users` WHERE gender IN ("
for i in range(len(gender_values)):
if i< len(gender_values)-1:
registeredQuery += " %s,"
else:
registeredQuery += " %s)"
placeholders = gender_values # or tuple(gender_values) if they are a list