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!')
So i have this code above trying to get a certain .csv file from my venv to mysql. made a list from the data and skipped the first line (since those were headers) and tried to import it to mysql. But i keep getting the following Error:
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
I probably missed some () or brackets or am i missing something else? Thanks
CodePudding user response：
The problem is in these lines:
year = rij[0],
winner = rij[1],
gender = rij[2],
country = rij[3],
time = rij[4],
marathon = rij[5],
The trailing commas cause year, winner, gender and so on to be created as 1-tuples. It's the same as writing
year = (rij[0],)
winner = (rij[1],)
# and so on...
Delete the trailing commas and try again.
CodePudding user response：
Your sql comad had a & instead of a %.
I additionally simplified the data loop
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!')
CodePudding user response：
You dont have to write code in order to import a csv file into mysql. Once the table is defined you can use LOAD DATA in order to import the data.
See here for more.