String encoding with Python and MySQL

Question

I need to insert into a MySQL 5.7 database strings with several non-ASCII characters, like è or … or even other alphabets, using a Python3 script. I converted all the columns of the interested table in utf8mb4.

Connecting with

db1 = MySQLdb.connect (
host="host1",
user="user1",
passwd="secret",
db="db1"
)

cursor1 = db1.cursor()
cursor1.execute("USE db1")

I can correctly store strings with è. Strings with … generate instead the following error:

UnicodeEncodeError: 'latin-1' codec can't encode character '\u2026' in position 1022: ordinal not in range(256)

Vice-versa, connecting with

db1 = MySQLdb.connect (
host="host1",
user="user1",
passwd="secret",
db="db1"
)

cursor1 = db1.cursor()
cursor1.execute("USE db1")
cur.execute("SET NAMES utf8mb4;")
cur.execute("SET CHARACTER SET utf8mb4;")
cur.execute("SET character_set_connection=utf8mb4;")

an error due to è is generated:

_mysql_exceptions.OperationalError: (1366, "Incorrect string value: '\\xE8 string...' for column 'column1' at row 1")

è has hex code E8.

What's wrong?

Rudresh Panchal · Accepted Answer · 2017-06-22 12:12:59Z

1

You should try setting the encoding to UTF8 while connecting to the DB by doing something like this:

db1 = MySQLdb.connect (
host="host1",
user="user1",
passwd="secret",
db="db1"
use_unicode=True, 
charset="utf8"
)

answered Jun 22, 2017 at 12:12

Rudresh Panchal

1,0106 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String encoding with Python and MySQL

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