This article is the 5th day of Django Advent Calendar 2019.

Introduction

Django's ORM allows you to issue more than enough queries to develop by making full use of select_related, prefetch_related, Preftch, Q objects, and so on.

However, there are some things you can't do, and in some cases you have to write raw SQL. For example, if you have multiple indexes, specify the index you want to use, ʻUSE_IDNEX, or specify the index you do not want to use, ʻIGNORE_INDEX. In that case, you need to write raw SQL.

Django-MySQL

Django-MySQL

However, you can use Django-MySQL here to extend Django's ORM without writing raw SQL and issue queries like ʻUSE_IDNEX and ʻIGNORE_INDEX.

setup

Installation

pip install django-mysql

Edit settings.py

Add django-mysql to INSTALLED_APPS


INSTALLED_APPS = (
    "django.contrib.admin",
    "django.contrib.auth",
    ...
    ...
    "django_mysql",
)

Enable DJANGO_MYSQL_REWRITE_QUERIES


DJANGO_MYSQL_REWRITE_QUERIES = True

Setting sql_mode and innodb_strict_mode

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "HOST": "127.0.0.1",
        "NAME": "hogehoge",
        "USER": "fugafuga",
        "OPTIONS": {
            "charset": "utf8mb4",
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1",
        },
    }
}

As a caveat with this change, when STRICT mode is enabled, an error will occur if you try to insert / update data that exceeds max_length. If STRICT mode is not enabled, if you insert / update data that exceeds max_length, the excess will be truncated and processed without error, so if you have not properly validated it. Need to be careful.

Note that this change is a setting when connecting to MySQL from Django, so there is no need to remigrate or something like that.

Let's use

model settings

Let's create a model called hoge in the users application. Put an index on both fuga and piyo of the field.

`users/hoge.py`


from django.db import models
from django_mysql.models import QuerySet


class Hoge(models.Model):
    fuga = models.IntegerField(db_index=True)
    piyo = models.IntegerField(db_index=True)

    objects = QuerySet.as_manager()

The unusual point is that you can do from django_mysql.models import QuerySet and use it as an ORM manager. By doing this, you can issue extended queries in Django-MySQL.

Information on the created table

mysql> show index from users_hoge;
+------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_hoge |          0 | PRIMARY                  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users_hoge |          1 | users_hoge_fuga_8a4fba19 |            1 | fuga        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users_hoge |          1 | users_hoge_piyo_4539c423 |            1 | piyo        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

It is like this.

Try IGNORE INDEX

In the case of issuing a query without using the fuga index, proceed as follows.

`python`


Hoge.objects.all().ignore_index("users_hoge_fuga_8a4fba19")

The SQL issued by this is as follows

SELECT
  `users_hoge`.`id`,
  `users_hoge`.`fuga`,
  `users_hoge`.`piyo`
FROM
  `users_hoge` IGNORE INDEX(`users_hoge_fuga_8a4fba19`);

The ignore index has been issued properly.

If you try to do this without using Django-MySQL

AttributeError: 'QuerySet' object has no attribute 'ignore_index'

I get an error like this.

end

So far, I've only used IGNORE INDEX, but I can use force index and other useful functions, so please see Documentation for details. Please read /index.html). You may want to consider introducing Djnago-MySQL once when you encounter a case where you have to write SQL with a painstaking measure that cannot be realized with a normal ORM.

Digression

Is it a logo whenever you see it? Or rather, the character is bad.

Use: Django-MySQL