0

I wanted to write a csv file into my mysql database but receive the following error message:

(mysql.connector.errors.ProgrammingError) 1148 (42000): The used command is not allowed with this MySQL version [SQL: LOAD DATA LOCAL INFILE 'File.csv' IGNORE INTO TABLE product FIELDS TERMINATED BY ';' LINES TERMINATED BY "\n" (Column1, Column2);] (Background on this error at: http://sqlalche.me/e/f405)

It woorked before but somehow it seems with the new mysql version it doesnt anymode. The suggested link also does not really help.

I want to have my code in that fashion because using pandas dumping the df via to_sql is not the way how I can control what I want to do.

The code looks like the following:

import sqlalchemy as sqlal
import pandas_datareader.data as pdr
import pandas as pd

mysql_engine = sqlal.create_engine('mysql+mysqlconnector://root:xxx@localhost/TableWrite')

mysql_engine.raw_connection()

metadata = sqlal.MetaData()


TableWrite  = sqlal.Table('TableWrite', metadata,
                       sqlal.Column('Column1', sqlal.String(10), primary_key=True, nullable=False, unique=True),                   
                       sqlal.Column('Column2', sqlal.String(12), nullable=True)
                       )

metadata.create_all(mysql_engine) 

product_data = """LOAD DATA LOCAL INFILE 'File.csv' IGNORE INTO TABLE product FIELDS TERMINATED BY ';' LINES TERMINATED BY "\\n" (Column1, Column2);"""
mysql_engine.execute(product_data)

The error message I receive is the following:

Traceback (most recent call last):
  File "/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 395, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: The used command is not allowed with this MySQL version

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
    cursor.execute(statement, parameters)
  File "/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    raw_as_string=self._raw_as_string)
  File "/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 398, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "File.py", line 37, in <module>
    mysql_engine.execute(product_data)
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2166, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return self._execute_text(object_, multiparams, params)
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text
    parameters,
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value.with_traceback(tb)
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
    cursor.execute(statement, parameters)
  File "/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    raw_as_string=self._raw_as_string)
  File "/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 398, in cmd_query
    sqlstate=exc.sqlstate)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1148 (42000): The used command is not allowed with this MySQL version
[SQL: LOAD DATA LOCAL INFILE 'File.csv' IGNORE INTO TABLE product FIELDS TERMINATED BY ';' LINES TERMINATED BY "\n" (Column1, Column2);]
(Background on this error at: http://sqlalche.me/e/f405)
MCM
  • 1,479
  • 2
  • 17
  • 22
  • Possible duplicate of [Load CSV data into MySQL in Python](https://stackoverflow.com/questions/10154633/load-csv-data-into-mysql-in-python) – shaik moeed Aug 03 '19 at 06:47
  • and [this](https://stackoverflow.com/a/24170168/8353711) – shaik moeed Aug 03 '19 at 06:51
  • Thanks for the answer. But that is using mysqldb or direct load using mysql workbench. I am using sqlalchemy and somehow I have to setup the proper config but would like doing it in python – MCM Aug 03 '19 at 07:03
  • Also related: [ERROR 1148: The used command is not allowed with this MySQL version](https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version). And since you're using mysql-connector, see [Python2.7 MySQL Connector Error on LOAD DATA LOCAL INFILE](https://stackoverflow.com/questions/19143526/python2-7-mysql-connector-error-on-load-data-local-infile). – Ilja Everilä Aug 03 '19 at 16:39
  • What's left then is how to pass the required connection arguments to the mysql-connector: [How to set connection timeout in SQLAlchemy](https://stackoverflow.com/questions/35640726/how-to-set-connection-timeout-in-sqlalchemy), [sqlalchemy, setting MySQL charset as `create_engine` argument](https://stackoverflow.com/questions/15784357/sqlalchemy-setting-mysql-charset-as-create-engine-argument). – Ilja Everilä Aug 03 '19 at 16:47
  • @IljaEverilä, thanks for the answer. I tried doing it via local_infile but is is already set to 1: SHOW VARIABLES LIKE 'local_infile'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | ON | +---------------+-------+ 1 row in set (0,01 sec) – MCM Aug 06 '19 at 06:59

0 Answers0