mysql - 执行准备好的语句的参数数量不正确 - Python/MySQL

Question

当执行以下代码以通过变量从 python 插入多个值到 mysql 时，我得到:

'Incorrect number of arguments executing prepared statement ' error after executing 'result = cursor.executemany(sql_insert_query, records_to_insert)'

如果我删除“准备=真”，错误变成:

'Not all parameters were used in the SQL statement'

import mysql.connector
connection = mysql.connector.connect(host='localhost',
                         database='majorprediction',
                         user='root',
                         password='')
records_to_insert = [ ('x') ,
                     ('y'),
                     ('z') ]
sql_insert_query = """ INSERT INTO majorpred (Major) 
                   VALUES (%s) """
cursor = connection.cursor(prepared=True)
result  = cursor.executemany(sql_insert_query, records_to_insert)
connection.commit()

任何人都可以指定问题出在哪里？

Answer 1

您正在传递字符列表而不是元组。例如，如果您尝试运行:

for record in records_to_insert:
    print(record, isinstance(record, str), isinstance(record, tuple))

你会得到:

x True False
y True False
z True False

要在 python 中使用单个元素创建元组，您可以执行以下操作:

records_to_insert = [
    ('x',),
    ('y',),
    ('z',) 
]

如果您有一个参数列表并希望将它们全部转换为元组，您可以执行以下操作:

list_of_elements = list("list_of_characters")
tuples = [
    tuple(e) for e in list_of_elements
]

希望这可以帮助!