I'm trying to import from CSV files values into MySQL using Python (Jupyter Notebook from Anaconda, Python 3.8, using a dashboard_venv virtual environment with mysql.connector)
The code is here (each empty line shows another cell in Jupyter, and I commented where it stops working):
import csv
import sys
db = mysql.connector.connect(
host = "localhost",
user = "user",
password = "password",
database = "dv",
)
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS v1;')
cursor.execute("CREATE TABLE v1 (c1 VARCHAR (32), c2 VARCHAR(32), c3 VARCHAR(32), c4 VARCHAR(32), c5 VARCHAR(32), c6 VARCHAR(32), c7 VARCHAR(32), c8 VARCHAR(32), c9 VARCHAR(32), c10 VARCHAR(32), c11 VARCHAR(32), c12 VARCHAR(32), c13 VARCHAR(32), c14 VARCHAR(32), c15 VARCHAR(32), c16 VARCHAR(32), c17 VARCHAR(32), c18 VARCHAR(32), c19 VARCHAR(32), c20 VARCHAR(32), c21 VARCHAR(32), c22 VARCHAR(32), c23 VARCHAR(32), c24 VARCHAR(32), c25 VARCHAR(32), c26 VARCHAR(32), log_id INTEGER AUTO_INCREMENT PRIMARY KEY)")
#as in 27 columns in total
import pandas as pd
csv_data = pd.read_csv(open('2020-11-01-1.csv'))
csv_data.head()
#stops working here:
for i,row in csv_data.iterrows():
sql = "INSERT INTO dv.v1 VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(sql, tuple(row))
I changed all the relevant names from my program, and get one of the following 2 errors:
This is the error that happens when the %s in VALUES is the same number or larger than the number of columns
IndexError Traceback (most recent call last)
~/Desktop/Project/dashboard_venv/lib/python3.8/site-packages/mysql/connector/cursor_cext.py in __call__(self, matchobj)
66 try:
---> 67 return self.params[index]
68 except IndexError:
IndexError: tuple index out of range
During handling of the above exception, another exception occurred:
ProgrammingError Traceback (most recent call last)
/tmp/ipykernel_397395/241987079.py in <module>
4 for i,row in csv_data2.iterrows():
5 # sql = "INSERT INTO dv.v1 VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
----> 6 cursor.execute("INSERT INTO dv.v1 VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", tuple(row))
~/Desktop/Project/dashboard_venv/lib/python3.8/site-packages/mysql/connector/cursor_cext.py in execute(self, operation, params, multi)
262 elif isinstance(prepared, (list, tuple)):
263 psub = _ParamSubstitutor(prepared)
--> 264 stmt = RE_PY_PARAM.sub(psub, stmt)
265 if psub.remaining != 0:
266 raise errors.ProgrammingError(
~/Desktop/Project/dashboard_venv/lib/python3.8/site-packages/mysql/connector/cursor_cext.py in __call__(self, matchobj)
67 return self.params[index]
68 except IndexError:
---> 69 raise errors.ProgrammingError(
70 "Not enough parameters for the SQL statement")
71
ProgrammingError: Not enough parameters for the SQL statement
And this is what I get when the %s in VALUES is at least 1 smaller than the number of columns
MySQLInterfaceError Traceback (most recent call last)
~/Desktop/Project/dashboard_venv/lib/python3.8/site-packages/mysql/connector/connection_cext.py in cmd_query(self, query, raw, buffered, raw_as_string)
516 query = query.encode('utf-8')
--> 517 self._cmysql.query(query,
518 raw=raw, buffered=buffered,
MySQLInterfaceError: Column count doesn't match value count at row 1
During handling of the above exception, another exception occurred:
DataError Traceback (most recent call last)
/tmp/ipykernel_397395/158871487.py in <module>
4 for i,row in csv_data2.iterrows():
5 # sql = "INSERT INTO dv.v1 VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
----> 6 cursor.execute("INSERT INTO dv.v1 VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", tuple(row))
~/Desktop/Project/dashboard_venv/lib/python3.8/site-packages/mysql/connector/cursor_cext.py in execute(self, operation, params, multi)
268
269 try:
--> 270 result = self._cnx.cmd_query(stmt, raw=self._raw,
271 buffered=self._buffered,
272 raw_as_string=self._raw_as_string)
~/Desktop/Project/dashboard_venv/lib/python3.8/site-packages/mysql/connector/connection_cext.py in cmd_query(self, query, raw, buffered, raw_as_string)
520 query_attrs=self._query_attrs)
521 except MySQLInterfaceError as exc:
--> 522 raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
523 sqlstate=exc.sqlstate)
524 except AttributeError:
DataError: 1136 (21S01): Column count doesn't match value count at row 1
I'll assume the second error message is redundant, but I included it just to be certain.
I saw other posts about "Not all / enough parameters for the SQL statement", here Not all parameters were used in the SQL statement (Python, MySQL) and here Insert record into mysql table using python : ProgrammingError: Not enough parameters for the SQL statement,
the support there doesn't help, all my Values are %s 's and I send enough. (the data types in the CSV are diverse, some strings, some date-times in dd/mm/yyyy hh:mm:ss format, which isn't typical for MySQL, but I don't think there's the problem)
I also considered checking for empty fields (if someone knows if there are any special instructions to import them from file into MySQL that would be useful too, but maybe for another post), but since I didn't know how, I simply edited the CSV file to eliminate all rows which weren't fully filled, so this isn't the problem.
The way I called the insert function is detailed here too https://www.projectpro.io/recipes/connect-mysql-python-and-import-csv-file-into-mysql-and-create-table
I currently can't think of anything else, does anyone know where does the ProgrammingError: Not enough parameters for the SQL statement error comes from and how to fix it?
