Fixing a MySQL "incorrect string value" error when save unicode string
I have recently been working with a CSV list of player names, which I needed to import into MySQL.
Using the CSV library in Python, I managed to read and slice my csv accordingly, here is an example:
import csv players = csv.reader(open('media/players.csv', 'rb'), delimiter=',', quotechar='|') i = 0; for row in players: print row if i > 0: player = Player.objects.get(id=i) player.first_name = row player.save() i = i+1
In the following example, I have already created the list of players, but some Polish characters in their name appear as "?".
The problem is that the column in that database table (the one for the name) hasn't got the right encoding charset, which should be UTF-8.
The best way to convert the column to UTF-8 is to run the following command in your mysql prompt, against the right database:
ALTER TABLE players_player MODIFY COLUMN first_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
Once you've done this, re-run your Python import/update script and the name should be saved with the right characters.