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
我可能錯過了一些 () 或括號，或者我錯過了其他東西？謝謝
uj5u.com熱心網友回復：
問題出在這些行中：
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...
洗掉尾隨逗號并重試。
uj5u.com熱心網友回復：
你的 sql comad 有一個 & 而不是 %。
我還簡化了資料回圈
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!')
uj5u.com熱心網友回復：
您不必撰寫代碼即可將 csv 檔案匯入 mysql。定義表后，您可以使用LOAD DATA匯入資料。
請參閱此處了解更多資訊。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/322113.html