Search code examples
pythonmysqlutf-8emojiutf8mb4

retreive emojii from mysql database

I'm trying to select from Mysql a utf8 string that contains an emojii and instead of emojii I'm getting question mark

import MySQLdb
db=MySQLdb.connect(host='host', user='user', passwd='password', db='db', charset='utf8mb4', use_unicode=True)
c=db.cursor()
c.execute("SET NAMES UTF8")

c.execute("SELECT message FROM chat WHERE...")
res = c.fetchone()[0]
#the res variable should contain '😀234абв'. I checked in database explorer

#saving the res into the file to avoid terminal encodeing issues
with open('test.txt','w',encoding='utf-8-sig') as f:
    f.write(res)

#and voila! I'm gettin '?234абв' in the file!!!

My database do uses 'utf8mb4' encoding with collation 'utf8mb4_unicode_ci'. The mysql connection was initialized with charset='utf8mb4' option. I did run 'set names utf8'.

I'm using python 3.8.5.

What am I missing here?

Solution

  • And as always, the best way to find a solution is to ask someone. Rubber duck debugging

    I figured out that I should use c.execute("SET NAMES UTF8mb4") instead of c.execute("SET NAMES UTF8")