How to insert unicode string to mysql

Question

the code is like below:

Connect server

MySQLdb.connect(host=ip, user='root', passwd='root',db='test',use_unicode=True,charset="utf8")
......
sql = "INSERT INTO ci(id,name) VALUES (493,u'Hello')"
print sql
ret = root.execute(sql)
.....

In the server, the tyoe of name is VARCHAR(1000). Then when i run this script, it shows error ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

But when i replace u'Hello' with 'Hello', it is OK. So maybe it doesn't support unicode,then i insert unicode string such as "你好" to the table by GUI manually, it is also OK. I can not find what is the reason, who can help me

Well you're sending the notation u'Hello' to MySQL, but that's python notation. MySQL will accept unicode, but you need to find their notation for it and get Python to send it in that form. — Gijs
– Gijs, Commented Sep 13, 2013 at 8:03

user4035 · Accepted Answer · 2013-09-13 08:26:29Z

3

MySQL needs strings to be enclosed in straight quotes: '你好', 'u' symbol is not allowed. Just declare the whole string as Unicode and pass it to MySQL. Here I am using a prepared statement:

sql = u"INSERT INTO ci(id,name) VALUES (493,'你好')"

Don't forget to run "SET NAMES 'UTF-8'" (or UTF-16 - don't know, what encoding you are using) after you connect to MySQL to ensure, that the server will correctly interpret the string you send it.

edited Sep 13, 2013 at 8:26

answered Sep 13, 2013 at 8:10

user4035

24k11 gold badges69 silver badges101 bronze badges

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

3 Comments

Michael Kazarian Over a year ago

better on this manner: sql = u"INSERT INTO ci(id,name) VALUES (493, ?)"; ret = root.execute(sql, ['你好']). It allow avoid problems related whith different data types

user4035 Over a year ago

@MichaelKazarian Yes, prepared statements are better. Shouldn't it be [u'你好']?

Michael Kazarian Over a year ago

I show right way. I can't test it because I don't use your locale.

Collectives™ on Stack Overflow

How to insert unicode string to mysql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related