CODEWITHSUNDEEP
pythonmysqlutf-8
user1161599
user1161599

Reputation: 271

(Python MySQLdb) when trying to insert UTF-8 into MySQL

I cannot find a solution. Can you help me with this question please?

    dic={'username':u'\uc774\ud55c\ub098','userid':u'david007', 'nation':u'\ub300\ud55c\ubbfc\uad6d'}
    c=MySQLdb.connect(host=ddb['host'],user=ddb['user'],passwd=ddb['passwd'],db=ddb['db'], use_unicode=True, charset="utf8")
    s=c.cursor()
    sql="INSERT INTO "+db+" "+col+" VALUES "+str(tuple(dic.values()))
    s.execute(sql)

    "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 ''\\uc774\\ud55 ... at line 1")

    print sql
    INSERT INTO user_tb (username, userid, nation) VALUES (u'\uc774\ud55c\ub098', u'david007', u'\ub300\ud55c\ubbfc\uad6d')

And the error is:

Upvotes: 2

Views: 892

Answers (1)

Pedro Romano
Pedro Romano

Reputation: 11203

You need to use a parametrised query:

sql = "INSERT INTO " + db + " " + col + " VALUES (%s, %s, %s)"
s.execute(sql, dic.values())

When you simply concatenate the tuple to your query, the u prefix of the unicode strings will make those strings invalid SQL. With parameters MySQLdb, will do the right thing with the parameter replacement (i.e. encoding the unicode strings to a byte representation) and generate valid SQL.

Anyway as a general principle you should always use parameters in your queries to prevent SQL injections.

Upvotes: 4

Related Questions