Python导入CSV数据到MySQL报错tuple类型无法转换为MySQL类型求助

错误1：变量赋值后多余逗号导致嵌套元组 #

你在遍历CSV行给year、winner等变量赋值时，每行末尾都加了多余的逗号：

year = rij[0],
winner = rij[1],
# 其余变量同理

Python语法中，变量值后加逗号会自动将值封装为单元素元组，这就导致你最后存入marathons列表的每条记录都是嵌套元组结构，MySQL连接器无法将元组类型转换为合法的SQL字段值，就是报错里提到的Python 'tuple' cannot be converted to a MySQL type的根因。

错误2：SQL占位符书写错误 #

你的INSERT插入语句中最后一个占位符将%s误写为&s，不符合mysql-connector的参数占位符语法要求。

修复后完整代码 #

import csv
import mysql.connector as mysql

marathons = []

with open("marathon_results.csv") as file:
    data = csv.reader(file)
    next(data)
    for rij in data:
        # 去掉末尾多余的逗号，避免转为元组
        year = rij[0]
        winner = rij[1]
        gender = rij[2]
        country = rij[3]
        time = rij[4]
        marathon = rij[5]
        marathons.append((year, winner, gender, country, time, marathon))

conn = mysql.connect(
     host="localhost",
     user="root",
     password=""
)

c = conn.cursor()

create_database_query = 'CREATE DATABASE IF NOT EXISTS marathon_file'
c.execute(create_database_query)

c.execute('USE marathon_file')
c.execute("""CREATE TABLE IF NOT EXISTS winners(
                year INT(100),
                winner VARCHAR(255),
                gender VARCHAR(255),
                country VARCHAR(255),
                time TIME,
                marathon VARCHAR(255)
                )
            """)
print('CSV-bestand in de MySQL-database aan het laden...')

# 修正最后一个占位符为%s
insert_query = "INSERT INTO winners(year, winner, gender, country, time, marathon) VALUES (%s, %s, %s, %s, %s, %s);"

c.executemany(insert_query, marathons)
c.commit()

print('Bestand succesvol geladen!')

额外优化建议 #

• 可以在读取year字段时转为int类型，避免后续插入数据库时的隐式类型转换问题
• 建议在操作完数据库后主动调用c.close()和conn.close()释放连接资源

内容的提问来源于stack exchange，提问作者Lars Medendorp