0

I'm getting this error when i try to insert certain data into my table:

'latin-1' codec can't encode character '\u2019' in position 8: ordinal not in range(256)

The issue is that I'm not using latin-1 anywhere - the table is utf8mb4 and collation is utf8mb4_unicode_520_ci. To make sure this is the case I checked:

mysql> SHOW FULL COLUMNS FROM CustomCommands_u1eae585f88c8ab055a227488b2b5adb1;
+-----------------+---------------+------------------------+------+-----+---------+-------+---------------------------------+---------+
| Field           | Type          | Collation              | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------+---------------+------------------------+------+-----+---------+-------+---------------------------------+---------+
| command_id      | int(10)       | NULL                   | YES  |     | NULL    |       | select,insert,update,references |         |
| text            | varchar(2000) | utf8mb4_unicode_520_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| contentMetadata | varchar(1000) | utf8mb4_unicode_520_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| contentType     | int(3)        | NULL                   | YES  |     | NULL    |       | select,insert,update,references |         |
+-----------------+---------------+------------------------+------+-----+---------+-------+---------------------------------+---------+

And sure enough its definitely utf8mb4. This is the python code I'm using to insert data into the table:

def _add_cc(self, rid, text, contentMetadata, contentType):
    query = "INSERT INTO `CustomCommands_%s` (`command_id`,`text`,`contentMetadata`,`contentType`) VALUES (%s,%s,%s,%s)" % (self._mid,'%s','%s','%s','%s')
    tup = (rid,text,str(contentMetadata),contentType)
    print(query)
    print(tup)
    self._cur.execute(query, tup)

The query it printed was:

INSERT INTO `CustomCommands_u1eae585f88c8ab055a227488b2b5adb1` (`command_id`,`text`,`contentMetadata`,`contentType`) VALUES (%s,%s,%s,%s)

And the tuple was :

(1470115915, '@Lil Cap’n Jack ', '{\'MENTION\': \'{"MENTIONEES":[{"M":"u98de557a46645dc6cd7583e538e1ae40","S":"0","E":"15"}]}\'}', 0)

So since everything is utf8mb4 I'm not sure why I'm getting a latin-1 codec error. I even ran the code below and it still gave me the same error.

db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="database")
db.autocommit(True)
cur = db.cursor()

tables = []
cur.execute("SHOW TABLES")
for row in cur.fetchall():
    tables.append(row[0])

for table in tables:
    cur.execute("ALTER TABLE %s CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci" % table)

So yeah no idea what to do.

doodspav
  • 35
  • 2
  • 8
  • 1
    The problem is on this special character " ’ "? Maybe this question can help you : https://stackoverflow.com/questions/6202726/writing-utf-8-string-to-mysql-with-python – Daniel E. Mar 01 '18 at 09:39
  • @DanielE. Oh shit it worked. Ty so much :) – doodspav Mar 01 '18 at 09:52

1 Answers1

0

Credit to Writing UTF-8 String to MySQL with Python and the person above who shared it (https://stackoverflow.com/users/5871602/daniel-e, idk how to actually tag you)

When I set my connection, instead of doing this:

db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="database")

I should have done this:

db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="database", charset="utf8mb4")

For some reason specifying the charset in the connection makes the difference.

doodspav
  • 35
  • 2
  • 8