CODEWITHSUNDEEP
pythonmysqldjangodjango-modelspython-3.9
aHardReset
aHardReset

Reputation: 31

Django Mysql string convertion utf8 and unicode?

Well, I have a django model that stores the name of a music band.

So new trouble arises when a band called '✝✝✝ (Crosses)' attempts to be stored in a TextField. This is the error: django.db.utils.OperationalError: (1366, "Incorrect string value: '\\xE2\\x9C\\x9D\\xE2\\x9C\\x9D...' for column 'album_name' at row 1")

But this becomes weird because I have another table that stores a JsonField with the band info. The same name '✝✝✝ (Crosses)' is stored correctly. the JsonField was a TextField that stored json.dumps(dict_with_band_info) ... So in the database is stored something like { "name": "✝✝✝ (Crosses)" ...}. And repeat, this was a TextField before and works as expected.

So why attempting to add "name": "✝✝✝ (Crosses)" to the db Textfield shows that error but not in the other table no? I'm using pdb.set_trace() to see what are the values before do the save().

I would like to mention again that that error doesn't appear even when the JsonField was TextField in my band info table, but the error appears in the TextField of the band_name and exactly in the instance.save(). with this, I can deduct that my text_fields are ready to receive unicode, because in the band info table, the jsonfield shows the "✝✝✝ (Crosses)". Why python is doing a utf-8 in the step of saving in the band name text field?

The only thing that I see different is the fact that:

When I save the band info I call the model like:

from bands.model import BandInfo
from apis import music_api as api
# Expected to be dict
band_info = api.get_band_info(song="This is a trick", singer="chino moreno")[0]

band = BandInfo()
band.band_info=band_info #{'name':'✝✝✝ (Crosses)'}
band.save()

and when I save the band_name:

def save_info(Table, data:dict):
    instance_ = Table(
        'name': data['name'] #'✝✝✝ (Crosses)'
    )
    instance_.save()

then in another file:

from apis import music_api as api
from bands import snippets
from bands.models import Tracks
track_info = api.get_track_info(song="This is a trick", singer="chino moreno")[0]
snippets.save_info(Tracks, data:dict)

Using: python 3.9.1 django 3.1.7 MySQL workbench 8 with the community installation

well, hope I'm doing an obvious mistake.

Upvotes: 0

Views: 1166

Answers (1)

Crash0v3rrid3
Crash0v3rrid3

Reputation: 538

MySQL's utf8 permits only the Unicode characters that can be represented with 3 bytes in UTF-8. If you have MySQL 5.5 or later you can change the column encoding from utf8 to utf8mb4. This encoding allows storage of characters that occupy 4 bytes in UTF-8.

To do this, set the charset option to utf8mb4 in the OPTIONS dict of the DATABASES setting in the Django settings file.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'my_db',
        'USER': 'my_user',
        'PASSWORD': 'my_pass',
        'HOST': 'my.host',
        'OPTIONS': {
            'charset': 'utf8mb4'  # This is the important line
        }
    }
}

Upvotes: 1

Related Questions