Use Unicode 6.0 emoji with django / MySQL

Purpose

From django [Emoji added in Unicode 6.0](http://ja.wikipedia.org/wiki/Unicode6.0%E3%81%AE%E6% 90% BA% E5% B8% AF% E9% 9B% BB% E8% A9% B1% E3% 81% AE% E7% B5% B5% E6% 96% 87% E5% AD% 97% E3% 81% I want to use AE% E4% B8% 80% E8% A6% A7).

Operating environment

Python

Python 3.2.0 or higher is required to use Unicode 6.0 emoji.

django

I haven't checked out the supported versions of django. It works on 1.7.0 and 1.8.1, so it's good.

mysqlclient

Use mysqlclient for the Python 3.x series.

MySQL

Use MySQL 5.5.14 or higher, which can extend the key prefix limit (discussed below) to 3072 bytes.

Setting

MySQL

To store pictograms in MySQL, set the character code when creating the database to utf8mb4.

CREATE DATABASE cmtestdb CHARACTER SET utf8mb4;

In MySQL (InnoDB), the maximum value of the key prefix of one column is 767 bytes. When utf8mb4 is specified, 767 bytes ÷ 4 can only use up to 191 characters. The following error occurs because the table generated by django middleware contains columns larger than 191 characters.

Specified key was too long; max key length is 767 bytes

To avoid this error, make the following settings in my.cnf.

my.cnf


innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

You can extend the maximum key prefix to 3072 bytes by specifying innodb_large_prefix.

mysqlclient

Install normally.

pip install mysqlclient

django

Database definition

When connecting to MySQL from django, specify the character code of utf8mb4.

settings.py


DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'DBNAME',
        'USER': 'DBUSER',
        'PASSWORD': 'DBPASSWORD',
        'HOST': 'DBHOST',
        'PORT': '3306',
        'OPTIONS': {
            'charset': 'utf8mb4',
        },
    },
}

Extension of "CREATE TABLE" statement

To enable the innodb_large_prefix specification, specify `ROW_FORMAT = DYNAMIC``` or `ROW_FORMAT = COMPRESSED``` in the "CREATE TABLE" SQL statement.

python manage.When you run py migrade"CREATE TABLE"Issue an SQL statement.


 The django 1.7 implementation couldn't find an extension point for the "CREATE TABLE" SQL statement.
 So add ``` ROW_FORMAT = DYNAMIC``` to dynamically patch the django 1.7 original "CREATE TABLE" SQL statement generation part.


#### **`manage.py`**
```python3

import os
import sys

def patch_mysql_sql_create_model(original):
    """
    :param :class:`django.db.backends.creation.BaseDatabaseCreation` original: BaseDatabaseCreation
    :return: BaseDatabaseCreation
    :rtype: :class:`django.db.backends.creation.BaseDatabaseCreation`
    """

    def revised(self, model, style, known_models=set()):
        """
        :class:`django.db.backends.creation.BaseDatabaseCreation` #sql_create_Add the following processing to the processing of model.
        *At the end of the SQL statement'ROW_FORMAT=DYNAMIC;'To add.

The additional conditions are as follows.
        *Database is MySQL
        *The beginning of the SQL statement is'CREATE TABLE'
        """
        fullname = self.__module__ + "." + self.__class__.__name__
        if fullname == 'django.db.backends.mysql.creation.DatabaseCreation':
            original_output, pending_references = original(self, model, style, known_models)

            final_output = []
            for sql in original_output:
                if sql.startswith('CREATE TABLE') is False:
                    continue

                if sql.endswith(';'):
                    sql = sql[:-1]

                sql += 'ROW_FORMAT=DYNAMIC'
                final_output.append(sql)

            return final_output, pending_references
        else:
            return original(self, model, style, known_models)

    return revised

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "config.local")

    if len(sys.argv) > 1 and sys.argv[1] == 'migrate':
        import django

        if django.VERSION >= (1, 8):
            from django.db.backends.base.creation import BaseDatabaseCreation
        else:
            from django.db.backends.creation import BaseDatabaseCreation
        BaseDatabaseCreation.sql_create_model = patch_mysql_sql_create_model(BaseDatabaseCreation.sql_create_model)

        from django.db.backends.mysql.schema import DatabaseSchemaEditor
        DatabaseSchemaEditor.sql_create_table += ' ROW_FORMAT=DYNAMIC'

    from django.core.management import execute_from_command_line

    execute_from_command_line(sys.argv)

With the above settings, ROW_FORMAT = DYNAMIC will be added to the end of the" CREATE TABLE "SQL statement when python manage.py migrate``` is executed.

Reference material

Recommended Posts

Use Unicode 6.0 emoji with django / MySQL
Use MySQL with Django
Use Gentelella with django
Use prefetch_related conveniently with Django
[Django] Use MessagePack with Django REST framework
Internationalization with django
CRUD with Django
Django + MySQL settings
Azure External Storage MySQL Challenges with Django (PTVS)
Use Python / Django with Windows Azure Cloud Service!
Django 1.11 started with Python3.6
Use mecab-ipadic-neologd with igo-python
Development digest with Django
Use RTX 3090 with PyTorch
Use ansible with cygwin
Use pipdeptree with virtualenv
Output PDF with Django
[Python] Use JSON with Python
Use Mock with pytest
Markdown output with Django
Use indicator with pd.merge
Twitter OAuth with Django
Use MySQL from Python
Use mecab with Python3
Use tensorboard with Chainer
Use DynamoDB with Python
Use pip with MSYS2
Getting Started with Django 1
Send email with Django
Use Python 3.8 with Anaconda
Use pyright with Spacemacs
Use TypeScript with django-compressor
Use MySQL from Python
Pooling mechanize with Django
Use GPS with Edison
Start today with Django
Getting Started with Django 2
Use nim with Jupyter
It's too easy to use an existing database with Django
Do Django with CodeStar (Python3.6.8, Django2.2.9)
Use shared memory with shared libraries
Use "$ in" operator with mongo-go-driver
Use custom tags with PyYAML
Use directional graphs with networkx
Get started with Django! ~ Tutorial ⑤ ~
Use TensorFlow with Intellij IDEA
Minimal website environment with django
Create an API with Django
Use Twitter API with Python
Use pip with Jupyter Notebook
Do Django with CodeStar (Python3.8, Django2.1.15)
Deploy Django serverless with Lambda
Python3 + Django ~ Mac ~ with Apache
Use DATE_FORMAT with SQLAlchemy filter
Use TUN / TAP with Python
Django Getting Started: 4_MySQL Integration
Use sqlite3 with NAO (Pepper)
Use MySQL from Anaconda (python)
Create a homepage with django
Use sqlite load_extensions with Pyramid
Get started with Django! ~ Tutorial ④ ~