Python MySQLdb LOAD LOCAL INFILE problems

Question

The problem is a simple one. When I execute the following I get different results depending on whether I run it from the MySQL console and from inside a Python Script using MySQLdb:

LOAD DATA LOCAL INFILE '/tmp/source.csv' INTO TABLE test FIELDS TERMINATED BY '|' IGNORE 1 LINES;

Console gives the following results:

Records: 35002 Deleted: 0 Skipped: 0 Warnings: 0

Python (via .info()) returns the following:

Records: 34977 Deleted: 0 Skipped: 0 Warnings: 8

So in summary, same source file, same SQL request, different results.

From the console I can 'SHOW WARNINGS' an get a better handle on which records are causing the problems and why but from Python I can't idenitify how to do this or more importantly what the cause of the problem could be.

Any suggestions?

MySQL Server '5.1.41-3ubuntu12.1'
Python '2.6.5'
Tables are MyISAM

Have you tried isolating a single line that gives different behaviour? You can bisection (divide an conquer) to isolate a line which gives different results in O(log(n)) of programmer's time. — Mark Byers
– Mark Byers, Commented May 27, 2010 at 15:41
After many weeks I have come to the conclusion that LOAD DATA LOCAL ... is very sensitive to the last column value, particularly if they are NULLs. Simply changing the column order worked for the same data file ... but like most things there is always a low-level workaround. — belvoir
– belvoir, Commented Jul 2, 2010 at 16:20

Jürgen Steinblock · Accepted Answer · 2010-05-27 16:08:15Z

After loading the data, execute

SELECT @@warning_count;

check if greater than 0. If it is than execute

SHOW WARNINGS;

and dump the result (returns 3 columns: Level, Code, Message) or throw an exception. You can execute both statements exactly like every other select * from ... query.

After failing miserably to query, SELECT @@warning_count;, I discovered that in MySQLdb or more specifically _mysql you need to use the conn.warning_count() to get the warning count, else you tend to get a 2014 Commands out of sync error.

Collectives™ on Stack Overflow

Python MySQLdb LOAD LOCAL INFILE problems

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related