PyMySQL error/exception on (file) data load to remote MySQL instance

Question

I am using PyMySQL-0.5.0 and facing an obscure'ish error/exception when loading data from a file to a remote MySQL instance. Upon executing a 'load data local infile ...' statement, I am seeing an exception that says: The used command is not allowed with this MySQL version.

Any clues, if this operation is supported in PyMySQL at all(and/or if this is supported in some other version)

PS:

1) Error details:

2012-05-17 11:05:24,524 - 8988 - DEBUG - Loading table table_2012_05_16 
from file: /path/to/data/file_2012-05-16
2012-05-17 11:05:24,524 - 8988 - DEBUG - Executing update: load data local 
infile '/path/to/data/file_2012-05-16' into table table_2012_05_16(@dummy, c1, 
c2, c3, c4, c5, c6, c7);
2012-05-17 11:05:24,528 - 8988 - ERROR - Exception while executing update: 
<class 'pymysql.err.InternalError'> - (1148, u'The used command is not allowed 
with this MySQL version')

2) MySQL docs on the 'load data local infile...' support/syntax.

3) This data load works fine if I use the mysql client(i.e. IMHO there shouldn't be any impediments - permissions, privileges, what-have-you - to this load):

load_machine:~$ mysql -htarget_machine.baz -ufoo -pbar db -e "load data local 
infile '/path/to/data/file_2012-05-16' into table table_2012_05_16(@dummy, c1, 
c2, c3, c4, c5, c6, c7)"

load_machine: ~$ mysql -htarget_machine.baz -ufoo -pbar db -e "select count(*) 
from table_2012_05_16;"
+----------+
| count(*) |
+----------+
| 38563191 |
+----------+

Tunaki · Accepted Answer · 2016-11-15 22:37:06Z

8

PYMySQL does support LOAD DATA LOCAL INFILE but you must enable it in the database connection parameters. Example:

connection = pymysql.connect(host='localhost', user='username', password='password', database='databasename', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor, local_infile=True)

The default is set to False.

edited Nov 15, 2016 at 22:37

Tunaki

138k46 gold badges370 silver badges443 bronze badges

answered Nov 15, 2016 at 22:24

David Darby

1333 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shiv Over a year ago

Thank you. It worked for me after including local_infile=True

Stacey · Accepted Answer · 2015-07-27 18:44:42Z

3

PyMySQL supports LOAD DATA LOCAL INFILE as of version 0.6.4

answered Jul 27, 2015 at 18:44

Stacey

763 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:30:28Z

2

From the manual:

If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version

PyMySQL does not yet support LOAD DATA LOCAL. It has been a medium-priority defect on their bug tracker for over 10 months, nobody yet assigned to fix it, no milestone set.

See this answer for how to perform a LOAD DATA LOCAL using Python's MySQLdb instead.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered May 17, 2012 at 15:53

eggyal

127k18 gold badges220 silver badges244 bronze badges

1 Comment

decimus phostle Over a year ago

Thanks for pointing out the bug. Guess PyMySQL is a no-go. Will likely just programmatically invoke mysql client. MySQLdb seems kind of a hassle - build dependencies and such. Would be great if PyMySQL would patch this.

Collectives™ on Stack Overflow

PyMySQL error/exception on (file) data load to remote MySQL instance

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related