从csv文件导入到mysql时不断出现错误

Question

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...')
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!')

所以我上面的代码试图从我的venv到mysql获得一个特定的。csv文件。从数据中制作一个列表，跳过第一行(因为那些是头)，并试图将其导入mysql。但是我一直得到以下错误:

CSV-bestand in de MySQL-database aan het laden...
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/conversion.py in to_mysql(self, value)
179         try:
--> 180             return getattr(self, "_{0}_to_mysql".format(type_name))(value)
181         except AttributeError:
AttributeError: 'MySQLConverter' object has no attribute '_tuple_to_mysql'
During handling of the above exception, another exception occurred:
TypeError                                 Traceback (most recent call last)
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py in _process_params(self, params)
430 
--> 431             res = [to_mysql(i) for i in res]
432             res = [escape(i) for i in res]
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py in <listcomp>(.0)
430 
--> 431             res = [to_mysql(i) for i in res]
432             res = [escape(i) for i in res]
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/conversion.py in to_mysql(self, value)
181         except AttributeError:
--> 182             raise TypeError("Python '{0}' cannot be converted to a "
183                             "MySQL type".format(type_name))
TypeError: Python 'tuple' cannot be converted to a MySQL type
During handling of the above exception, another exception occurred:
ProgrammingError                          Traceback (most recent call last)
/var/folders/yc/mz4bq04s7wngrglphldwpwfc0000gn/T/ipykernel_17482/929148642.py in <module>
38 insert_query = "INSERT INTO winners(year, winner, gender, country, time, marathon) VALUES (%s, %s, %s, %s, %s, &s);"
39 
---> 40 c.executemany(insert_query, marathons)
41 c.commit()
42 
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py in executemany(self, operation, seq_params)
665                 self._rowcount = 0
666                 return None
--> 667             stmt = self._batch_insert(operation, seq_params)
668             if stmt is not None:
669                 self._executed = stmt
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py in _batch_insert(self, operation, seq_params)
607                         tmp, self._process_params_dict(params))
608                 else:
--> 609                     psub = _ParamSubstitutor(self._process_params(params))
610                     tmp = RE_PY_PARAM.sub(psub, tmp)
611                     if psub.remaining != 0:
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py in _process_params(self, params)
433             res = [quote(i) for i in res]
434         except Exception as err:
--> 435             raise errors.ProgrammingError(
436                 "Failed processing format-parameters; %s" % err)
437         else:
ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type

我可能错过了一些()或括号，还是我错过了别的东西?由于

Answer 1

问题出在以下几行:

year = rij[0],
winner = rij[1],
gender = rij[2],
country = rij[3],
time = rij[4],
marathon = rij[5],

后面的逗号导致year,winner,gender等被创建为1元组。这和写

是一样的

year = (rij[0],)
winner = (rij[1],)
# and so on...

删除后面的逗号，然后重试。

Answer 2

您的sql命令中有&而不是%.

我还简化了数据循环

import csv
import mysql.connector as mysql
marathons = []

with open ("test2.csv") as file:
data = csv.reader(file)
next(data)
marathons =  [tuple(row) for row in data]
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...')
insert_query = "INSERT INTO winners(year, winner, gender, country, time, marathon) VALUES (%s, %s, %s, %s, %s, %s);"
c.executemany(insert_query, marathons)
conn.commit()
print('Bestand succesvol geladen!')