Python prints wrong encoding from MySQL database

Question

I'm having issues with my Python when calling data from my MySQL database.

The database is set as UTF-8 and is containing, special letters such as 'Æ' and 'Ø'.

This is the code i use to call the Usernames from my Table

# -*- coding: UTF-8 -*-
import pymysql
db = pymysql.connect(
    host="localhost",
    user="root",
    password="XXXXXXXX",
    db="pythonconnectiontest", 
    charset="utf8"
)
cursor = db.cursor()
cursor.execute("SELECT Username FROM Account")

numrows = cursor.rowcount 
for i in range (numrows):
    row = cursor.fetchone()
    print row

The expected output is:

ThisTextIsBrøken
Tæst 
Word

Actual output:

(u'ThisTextIsBr\xf8ken',)
(u'T\xe6st',)
(u'Word',)

I do get that the 'U' in front of the value, indicates that this is UNICODE and i probably just need to make python interpret the text correctly with encode() or decode().

I've spent many hours trying to figure this out, and the solution is probably very simple.

I hope someone can help me fix this.

Thanks for reading.

Can you try use print unicode(row) it might solve your problem — mtkilic
– mtkilic, Commented Jan 21, 2019 at 21:58
Please provide SELECT HEX(col), col FROM ... If 'Tæst' comes out 54657374, then you really have latin1 encoding in the table! If everything is utf8, then the hex will be 54C3A67374. — Rick James
– Rick James, Commented Jan 26, 2019 at 1:07

Fynn Becker · Accepted Answer · 2022-12-09 12:50:46Z

The unicode strings it's outputting are perfectly fine. You can verify this by trying print u'ThisTextIsBr\xf8ken' in a python shell:

➜ python2
Python 2.7.15 (default, Jan 10 2019, 23:20:52)
[GCC 8.2.1 20181127] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> print u'ThisTextIsBr\xf8ken'
ThisTextIsBrøken

Your confusion is merely about how python represents unicode strings that are wrapped in another object. In this case your rows each represent a tuple of columns or more specifically a tuple containing a single unicode string.

You can verify this by adjusting your code to print the actual unicode string rather than the tuple containing it.

# -*- coding: UTF-8 -*-
import pymysql
db = pymysql.connect(
    host="localhost",
    user="root",
    password="XXXXXXXX",
    db="pythonconnectiontest", 
    charset="utf8"
)
cursor = db.cursor()
cursor.execute("SELECT Username FROM Account")

numrows = cursor.rowcount 
for i in range (numrows):
    row = cursor.fetchone()
    print row[0]

This should now output the expected strings.

Collectives™ on Stack Overflow

Python prints wrong encoding from MySQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related