I have a strange unicode problem with mySQL and sqlite.
In my application I get a table as a sqlite table which is being compared to an
existing mySQL Table.
The sqlite drive returns all strings from the table as a unicode string which
is Ok.
The mysql drive returns all strings as utf-8 coded strings (no unicode!).
When opening the mySQL database, use unicode is set to true, so the driver
should return
unicode strings.
Any ideas ?
This is the mySQL table definition:
CREATE TABLE `USERNAMES` (
`NAME` varchar(256) COLLATE utf8_bin NOT NULL,
`ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`NAME`),
KEY `BYID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=59325 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
COMMENT='Table for mapping user names to IDs'
The sqlite Table was created this way:
sq3Cursor.execute("create table USERNAMES(NAME text, ID integer)")
When I query a value from both tables I get:
sqlite:
>>> SrcCursor.execute("select * from USERNAMES where ID=49011")
<sqlite3.Cursor object at 0x2b6096bfc240>
>>> SrcCursor.fetchone()
(u'J\xd6RG R\xd6\xdfMANN', 49011)
>>> print u'J\xd6RG R\xd6\xdfLER/BD_FH'.encode("utf8")
JÖRG RÖßMANN
This is Ok.
Now mysql:
>>> DstCursor.execute("select * from USERNAMES where ID=49011")
1L
>>> DstCursor.fetchone()
('J\xc3\x96RG R\xc3\x96\xc3\x9fMANN', 49011)
This is the same result, but returned as a utf-8 coded string, not unicode
>>> 'J\xc3\x96RG R\xc3\x96\xc3\x9fMANN'.decode("utf8")
u'J\xd6RG R\xd6\xdfMANN'
The mySQL database has been opened this way:
DstCon = MySQLdb.connect(host = DstServer, user = config["DBUser"], passwd =
config["DBPasswd"], db = DstDBName, use_unicode = True, charset = "utf8")
DstCursor = DstCon.cursor()
Since use_unicode is set to True, I expect query results to be unicode (for
string data types).
--
http://mail.python.org/mailman/listinfo/python-list
