pyMySQL set connection character set

Question

I'm developing a fairly straightforward web app using Flask and MySQL.

I'm struggling with unicode. Users sometimes paste stuff that they copied from Word and it's falling over with the old smart quotes u'\u201c'.

A little bit of investigation shows that the connection I have to MySQL is using the Latin1 charset (seems to be the default).

How can I specify for it to use unicode for its connection?

I'm using pyMySQL, which purports to be a drop-in replacement for MySQLdb. MySQLdb defines a set_character_set(self, charset) function for connection objects, but pyMySQL doesn't (I get an error if I try).

You could try a raw query for every connection you have stating `SET NAMES UTF8` — Sjaak Trekhaak, Jun 18 '12 at 12:32

score 67 · Accepted Answer · edited Oct 17 '15 at 04:54

67

I worked it out by poking around in the pyMySQL source (I had tried, but couldn't find the right place!).

You can specify it when you create the connection:

conn = pymysql.connect(host='localhost',
                       user='username',
                       passwd='password',
                       db='database',
                       charset='utf8')

Solved my problem.

edited Oct 17 '15 at 04:54

Martin Thoma

124,992
159
614
958

answered Jun 18 '12 at 12:47

MalphasWats

3,255
6
34
40

7

I was trying to use pymysql with sqlalchemy, so the fix for me was to change the url to `mysql+pymysql://user_name@host_name/database_name?charset=utf8mb4` ; this answer helped a lot! – Devin Howard Oct 23 '15 at 02:53

pyMySQL set connection character set

1 Answers1