How to fix bulk loading with MySQL python connector

Question

I am trying to bulk load multiple csv files into a MySQL database that has already been set up. My main issue is with the actual execution of the loading because it looks like everything else is set up properly

I have tried looking around on stack to possibly find a solution to this problem, but since I am new to MySQL and python I have had very little luck

delimiter=r','

dateString=r'%d-%b-%Y'
file=fileName.split("/")[-1]

c.execute("Load data local infile %s into table cmStaging fields terminated by %s ignore 1 lines(symbol,series,open,high,low,close,last,prevclose,tottrdqty,tottrdval,@timestamp,totaltrades,isin) SET timestamp = STR_TO_DATE(@timestamp, %s)",(fileName,delimiter,dateString))

Traceback (most recent call last): File "/Users/admin/PycharmProjects/untitled4/loadCMfiles.py", line 59, in insertRows(localExtractFilePath+"/"+file,c) File "/Users/admin/PycharmProjects/untitled4/loadCMfiles.py", line 48, in insertRows c.execute("Load data local infile %s into table cmStaging fields terminated by %s ignore 1 lines(symbol,series,open,high,low,close,last,prevclose,tottrdqty,tottrdval,@timestamp,totaltrades,isin) SET timestamp = STR_TO_DATE(@timestamp, %s)",(fileName,delimiter,dateString)) File "/Users/admin/.conda/envs/W/lib/python2.7/site-packages/mysql/connector/cursor_cext.py", line 266, in execute raw_as_string=self._raw_as_string) File "/Users/admin/.conda/envs/W/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 395, in cmd_query sqlstate=exc.sqlstate) mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version

score 0 · Answer 1 · answered Aug 19 '19 at 16:08

I think I know the exact LoonyCorn assignment you're doing :)

Make sure you're passing allow_local_infile=True to the mysql.connector.connect(**config) statement like so:

import mysql.connector

config = {
    'user': 'username',
    'password': 'password',
    'host': '127.XXXX',
    'database': 'nse',
    'use_pure': True,
    'allow_local_infile': True
}

conn = mysql.connector.connect(**config)

Just a heads up: you may need to additionally follow the steps here if this doesn't work--I tried the steps in the video, including updating /etc/mysql/my.cnf file as advised here and none of them worked; however they may still be necessary with the above solution.

Good luck!

How to fix bulk loading with MySQL python connector

1 Answers1