How to use placeholders for "IN" in python mysql statements when using mysql.connector

Question

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

The parameter is inserted as solid string literal. For gender (2 values) use simple equiality. For multi-values CSV list use FIND_IN_SET() instead of IN. — Akina
– Akina, Commented Jan 20, 2020 at 13:23

Dimitra Paraskevopoulou · Accepted Answer · 2020-01-22 08:49:03Z

1

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

edited Jan 22, 2020 at 8:49

answered Jan 21, 2020 at 13:49

Dimitra Paraskevopoulou

1012 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ktrev Over a year ago

Thank You. But for me gender_values are dynamic. It can be either 1, 2 or 3 values.

Dimitra Paraskevopoulou Over a year ago

If dynamic is the only issue, you can build the registeredQuery dynamically too. I will edit my answer with the way to do it.

Mohamad Hamouday · Accepted Answer · 2024-07-22 01:52:32Z

I had the same question before and couldn't find a solution. So, I created a function that checks all query parameters and converts IN to multiple placeholders.

  def support_in_placeholder(query, values):
    # Update the query placeholders to have %s + index so we can update the placeholders in the query based on the index
    query = query % tuple(["%s" + str(index) for index in range(len(values))])

    new_values = []
    # Loop through the tuple values and add them to the values list, updating the placeholders in the query that have the same index as the value
    for index, value in enumerate(values):
        if isinstance(value, list):
            # Update the query to have multiple placeholders for the list values
            # Generate placeholders for each element in the value list
            placeholders = ", ".join([f"%s{index}" for _ in range(len(value))])

            # Replace the placeholder in the query with the generated placeholders
            query = query.replace(f"%s{index}", placeholders)
            # Add the list values to the new_values list as separate values
            for x in value:
                new_values.append(x)
        else:
            new_values.append(value)

    # Remove the index from the placeholders in the final query
    query = query.replace("%s" + str(index), "%s")

    return query, new_values

Just pass your query and your parameters, and it will return the updated version of the query.

Collectives™ on Stack Overflow

How to use placeholders for "IN" in python mysql statements when using mysql.connector

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related