I have a GBK encoding data table. Sometimes, a insert SQL with unicode string failed with exception:
mysql.connector.errors.ProgrammingError: Failed processing pyformat-parameters; 'gbk' codec can't encode character u'\u2022' in position 14: illegal
It is caused by encoding a unicode object without param 'ignore' in mysql-connector-python library. But I cannot modify the code. How to solve this problem?
Comment of hago already mentioned to filter Unicode characters which are not part of GBK, but I would like to give a full example using MySQL Connector/Python.
# -*- coding: utf-8 -*-
import mysql.connector
cnx = mysql.connector.connect(
database='test', charset='gbk', use_unicode=False
)
cur = cnx.cursor()
cur.execute("DROP TABLE IF EXISTS gbktest")
table = (
"CREATE TABLE gbktest ("
"id INT AUTO_INCREMENT KEY, "
"c1 VARCHAR(40)"
") CHARACTER SET 'gbk'"
)
cur.execute(table)
data = {
'c1': u'\u2022国家标准'.encode('gbk', 'ignore')
}
cur.execute("INSERT INTO gbktest (c1) VALUES (%(c1)s)", data)
cnx.commit()
cur.execute("SELECT id, c1 FROM gbktest")
rows = cur.fetchall()
# Terminal using UTF-8 encoding:
#print rows[0][1].decode('gbk')
# Terminal using GBK encoding:
print rows[0][1]
The last two lines need to be commented/uncommented depending on whether your Terminal is using UTF-8 or GBK encoding.
