Put in this MySQL command, via Python and MySQLdb:
LOAD DATA LOCAL INFILE "c:\\docume~1\\nagle\\locals~1\\temp\\tmpi16zz2.tx t" REPLACE
INTO TABLE companyindex CHARACTER SET utf8 FIELDS ENCLOSED BY '"' ESCAPED BY
'\\' TERMINATED
BY ',' (conformed_company_name, domain, location, state, postal_code,
country_code, database_name, record_id)
Received:
> _mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL se
> rver version for the right syntax to use near \'CHARACTER SET utf8 FIELDS ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' TERMINATED BY \',\'
> (con\' at line 1')
> Press any key to continue
If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS
and all, and loads ASCII data correctly.
I get exactly the same error by pasting the command above into the
MySQL query browser, so it's not a Python problem.
That should be the right syntax, per
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Here's my MySQL installation:
MySQL version: 5.0.27.
character_set_client: utf8
character_set_connection: utf8
character_set_database: utf8
chacter_set_results: utf8
character_set_server: utf8
character_set_system: utf8
so everything is Unicode-enabled.
But, according to this bug report:
http://bugs.mysql.com/bug.php?id=15126
[16 Mar 11:29] Alexander Barkov
Dear doc team,
Can you please also document that the CHARACTER SET
clause was added into LOAD DATA syntax:
LOAD DATA INFILE 'loaddata6.dat' INTO TABLE t1 CHARACTER SET koi8r;
the MySQL 5.0 documentation was recently updated to show this feature.
But it looks like that feature actually went into MySQL 5.1 in this patch
http://lists.mysql.com/commits/16915
on December 13, 2006, and isn't in MySQL 5.0.
In fact, if I paste the LOAD DATA INFILE statement from the bug
report into the MySQL query browser, I get a syntax error near
"'CHARACTER SET koi8r' at line 1". So it really isn't implemented
in MySQL 5.0.27.
Is that correct?
Given that, what's the correct way to tell MySQL that it
is reading a UTF8 file? (And does the Python interface to
the MySQL client need to do something about this?)
John Nagle