Strange MySQL / sqlite3 Problem with unicode

Discussion in 'Python' started by Hans Müller, Dec 3, 2009.

  1. Hans Müller

    Hans Müller Guest

    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).
     
    Hans Müller, Dec 3, 2009
    #1
    1. Advertising

  2. On Thu, 03 Dec 2009 15:46:56 +0100, Hans Müller <>
    declaimed the following in gmane.comp.python.general:

    > When opening the mySQL database, use unicode is set to true, so the driver should return
    > unicode strings.
    >

    From the MySQLdb documentation:
    http://mysql-python.sourceforge.net/MySQLdb.html

    doc> use_unicode
    doc>
    doc> If True, CHAR and VARCHAR and TEXT columns are returned as
    Unicode strings, using the configured character set.

    Note the last clause. And...

    doc> charset
    doc>
    doc> If present, the connection character set will be changed to
    this character set, if they are not equal.

    And for MySQL itself:
    http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html

    doc> MySQL 5.0 supports two character sets for storing Unicode data:
    doc>
    doc> * ucs2, the UCS-2 encoding of the Unicode character set
    using 16 bits per character
    doc> * utf8, a UTF-8 encoding of the Unicode character set
    using one to three bytes per character

    > Since use_unicode is set to True, I expect query results to be unicode (for string data types).


    Based upon the documentation I referenced, you are getting exactly
    what you asked for -- string data returned as unicode encoded in UTF8
    --
    Wulfraed Dennis Lee Bieber KD6MOG
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Dec 4, 2009
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robert Mark Bram
    Replies:
    0
    Views:
    4,048
    Robert Mark Bram
    Sep 28, 2003
  2. Hans Müller
    Replies:
    1
    Views:
    298
    Hans Müller
    Dec 3, 2009
  3. Grzegorz ¦liwiñski
    Replies:
    2
    Views:
    1,026
    Grzegorz ¦liwiñski
    Jan 19, 2011
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    538
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    289
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page