Having problem setting 'charset=utf8' with peewee and pymysql

Question

I got some problem setting utf8 on my database table.

I followed these documents but not working

docs for pymsql: https://pymysql.readthedocs.io/en/latest/modules/connections.html

docs for peewee: http://docs.peewee-orm.com/en/latest/peewee/database.html#using-mysql

here is my code

import datetime

from peewee import *
import pymysql
from getDataFromExcel import *

mysql_db = MySQLDatabase(
    databasename,
    user='username',
    password='password',
    host='host',
    charset='utf8', # <-- what I followed from docs

    )

class BaseModel(Model):
    """A base model that will use our MySQL database """
    class Meta:
        database = mysql_db


class Food(BaseModel):
    name = CharField()
    allergy = CharField()
    special = CharField()
    createdAt = DateTimeField(default = datetime.datetime.now)
    updatedAt =DateTimeField(default = datetime.datetime.now)
    class Meta:
        table_name='Food'


def initialize():
    """Create the database and the table if they don't exist."""

    mysql_db.connect()
    print('Initialized')


def insert_food_data(food_rows):
    with mysql_db.atomic():
        Food.insert_many(food_rows,fields=[
            Food.name,
            Food.allergy,
            Food.special
        ]).execute()


if __name__ =='__main__':
    initialize()

I run this using python3 -i filename.py

after that, I put some more code interactively(drop existing table and create new table to check utf8 setting is done)

mysql_db.execute_sql("SET FOREIGN_KEY_CHECKS=0")
mysql_db.drop_tables([Food])
mysql_db.create_tables([Food])

but when I check my data base with MySQLWorkbench. the DDL tap of my table says


  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `allergy` varchar(255) NOT NULL,
  `special` varchar(255) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

that means charset has set with 'latin1' and I can know that by inserting some data in it.

why is that?

did I something wrong setting charset ase uf8 ?

It would be really appreciated if somebody can help me.

Thank you for reading this.

user_254346 · Answer

That sets the charset of the connection. To change your default charset on the table, you can modify my.cnf

[mysqld]
character-set-server  = utf8
collation-server      = utf8_general_ci
character_set_server   = utf8
collation_server       = utf8_general_ci

You can also specify Model.meta.table_settings to specify the charset for a given table explicitly.

class MyModel(Model):
    # ... fields, etc ...
    class Meta:
        database = my_db
        table_settings = ['ENGINE=InnoDB', 'DEFAULT CHARSET=utf8']

http://docs.peewee-orm.com/en/latest/peewee/models.html#model-options-and-table-metadata

user_1766831 · Answer

A table and its columns have a specified encoding for characters. (latin1, in your case)
The client has an encoding. This indicates the way accented letters, etc, are encoded in 1 byte (for latin1) or 2 bytes (for utf8), etc.

You tell MySQL what the client encoding is through 3 settings --

character_set_client
character_set_connection
character_set_results

Those are probably set indirectly from one of things you told peewee or pymysql. * When an INSERT or SELECT occurs, the client encoding is turned into the column's encoding on the fly.

latin1 is fine for Western European languages, but is inadequate for the rest of the world.

Having problem setting 'charset=utf8' with peewee and pymysql

2 Answers