mysql - 使用 mysql.connector 时如何在 python mysql 语句中使用 "IN"的占位符

Question

我正在使用 mysql.connector 从 python 数据库中查询值。我需要在 where 子句中编写一个带有“IN”的选择查询。我尝试将值作为列表、元组等传递。但它抛出错误“Python'数据类型'无法转换为MySQL类型”

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

Answer 1

根据此处的文档https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html参数必须是元组类型，并且每个参数都应该有一个 %s。
我建议进行以下更改:

gender_values = ('Male', 'Female')
registeredQuery = "SELECT COUNT(DISTINCT uid) AS registered FROM `users` WHERE gender IN (%s, %s)"
placeholders = gender_values

动态性别值(value)观

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