Introduction

~~ You said "Django has a lot of experts in the company" !!!! ~~

Future Advent Calendar 2019 This is the article on the 10th day. This year's Advent calendar is scary because there are too many stuff ...

This time, I wrote golang and rails, It will be the first time to build a system with Python ~~ rushing ~~, and I will publish what I was addicted to.

Some of the sql that appears in this article is formatted.

In one line

Django's model doesn't reflect settings such as default in the DB!

Prerequisite environment

The environment assumes that you have completed Introduction to Python Django (1) up to the article (6) in order. It's not an official reference, but it was a good article. I'm glad that the finished product is on https://github.com/kakky/mybook20. (The following verification is performed with this commit. Https://github.com/kakky/mybook20/commit/82e741652bfd7f82f5c0bc601e04b7585632d266)

If you have cloned the above, do the following as a preliminary preparation.

$ python manage.py migrate
$ python manage.py createsuperuser
$ python manage.py runserver

If you stumble here, please refer to the prerequisite article. The above command is written in this article. Introduction to Python Django (3)

Main subject

Django's model doesn't reflect settings such as default in the DB

Preparation

Let's change the class Book of cms / models.py as follows.

`cms/models.py`


class Book(models.Model):
    """Books"""
    name = models.CharField('Book title', max_length=255, unique=True)
    publisher = models.CharField('the publisher', max_length=255)
    page = models.IntegerField('number of pages', blank=True, default=0)
    on_sale = models.BooleanField('Sale', default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.name

Add created_at and ʻupdated_at, fill the current time at insert and update respectively, add ʻon_sale and leave the default value set to True.

In addition, ʻunique = True is added to name. By default, all columns have null = False`, which is the behavior of not null constraint.

Since we changed the model, create a migration file as follows. I'm angry to fill in the initial value, so set it appropriately as timezone.now.

$ python manage.py makemigrations
You are trying to add the field 'created_at' with 'auto_now_add=True' to book without a default; the database needs something to populate existing rows.

 1) Provide a one-off default now (will be set on all existing rows)
 2) Quit, and let me add a default in models.py
Select an option: 1
Please enter the default value now, as valid Python
You can accept the default 'timezone.now' by pressing 'Enter' or you can provide another value.
The datetime and django.utils.timezone modules are available, so you can do e.g. timezone.now
Type 'exit' to exit this prompt
[default: timezone.now] >>> timezone.now

Apply migration.

python manage.py migrate

Operation check

Go to http://127.0.0.1:8000/admin/ and add your Book. (Details omitted)

Let's take a look at the contents of the DB.

$ sqlite> select * from cms_book;
id|name|page|created_at|on_sale|updated_at|publisher
1|Devil's darling|100|2019-12-09 13:03:29.102973|1|2019-12-09 13:03:29.132151|Kadokawa Bunko

It contains created_at updated_at and looks good. * ʻOn_sale` is 1 and represents True. Now let's insert directly into the DB.

sqlite> insert into cms_book(name, publisher, page) values('Akuma no Temariuta', 'Shueisha', 200);
Error: NOT NULL constraint failed: cms_book.created_at

For some reason, I get angry that created_at, which should automatically enter the current time, violates the not null constraint. So, fill created_at, ʻupdated_at` with CURRENT_TIMESTAMP.

sqlite> insert into cms_book(
    name,
    publisher,
    page,
    created_at,
    updated_at
)
values(
    'Akuma no Temariuta',
    'Shueisha',
    200,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
)
;
Error: NOT NULL constraint failed: cms_book.on_sale

This time, I get angry that on_sale, which should have specified the default value, is null. Since it can't be helped, ʻon_sale` also explicitly specifies the value.

sqlite> insert into cms_book(
    name,
    publisher,
    page,
    on_sale,
    created_at,
    updated_at
)
values(
    'Akuma no Temariuta',
    'Shueisha',
    200,
    0,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
)
;
sqlite> select * from cms_book;
id|name|page|created_at|on_sale|updated_at|publisher
1|Devil's darling|100|2019-12-09 13:03:29.102973|1|2019-12-09 13:03:29.132151|Kadokawa Bunko
2|Akuma no Temariuta|200|2019-12-09 13:12:17|0|2019-12-09 13:12:17|Shueisha

This time I was able to insert successfully. __ Why did NOT NULL constraint occur in a column with default, ʻauto_now_add, ʻauto_now? __ I will look at them in order.

sqlite> .schema cms_book
CREATE TABLE IF NOT EXISTS "cms_book"(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" varchar(255) NOT NULL UNIQUE,
    "page" integer NOT NULL,
    "created_at" datetime NOT NULL,
    "on_sale" bool NOT NULL,
    "updated_at" datetime NOT NULL,
    "publisher" varchar(255) NOT NULL
)
;

At the time of table definition, the default and ʻauto_now` systems have already dropped. Check the migration file and the generated sql.

`cms/migrations/0002_auto_20191209_2202.py`


    operations = [
        migrations.AddField(
            model_name='book',
            name='created_at',
            field=models.DateTimeField(auto_now_add=True, default=django.utils.timezone.now),
            preserve_default=False,
        ),
        migrations.AddField(
            model_name='book',
            name='on_sale',
            field=models.BooleanField(default=True, verbose_name='Sale'),
        ),
        migrations.AddField(
            model_name='book',
            name='updated_at',
            field=models.DateTimeField(auto_now=True),
        ),
        migrations.AlterField(
            model_name='book',
            name='name',
            field=models.CharField(max_length=255, unique=True, verbose_name='Book title'),
        ),
        migrations.AlterField(
            model_name='book',
            name='publisher',
            field=models.CharField(max_length=255, verbose_name='the publisher'),
        ),
    ]

$ python manage.py showmigrations
admin
 [X] 0001_initial
(abridgement)
cms
 [X] 0001_initial
 [X] 0002_auto_20191209_2202
(abridgement)
$ python manage.py sqlmigrate cms 0002_auto_20191209_2202
BEGIN;
(abridgement)
--
-- Alter field publisher on book
--
CREATE TABLE "new__cms_book"(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" varchar(255) NOT NULL UNIQUE,
    "page" integer NOT NULL,
    "created_at" datetime NOT NULL,
    "on_sale" bool NOT NULL,
    "updated_at" datetime NOT NULL,
    "publisher" varchar(255) NOT NULL
)
;
INSERT INTO "new__cms_book"(
    "id",
    "name",
    "page",
    "created_at",
    "on_sale",
    "updated_at",
    "publisher"
)
SELECT
    "id",
    "name",
    "page",
    "created_at",
    "on_sale",
    "updated_at",
    "publisher"
FROM
    "cms_book"
;
DROP TABLE "cms_book"
;
ALTER TABLE "new__cms_book" RENAME TO "cms_book"
;
COMMIT
;

In the migration file, you can see that the notation remains like default = True. I omitted the generated sql because it is longer (I don't know why you bother to create, drop, alter) Please pay attention to the following parts.

CREATE TABLE "new__cms_book"(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" varchar(255) NOT NULL UNIQUE,
    "page" integer NOT NULL,
    "created_at" datetime NOT NULL,
    "on_sale" bool NOT NULL,
    "updated_at" datetime NOT NULL,
    "publisher" varchar(255) NOT NULL
)

The default value and ʻauto_now` have fallen brilliantly. By the way, this behavior was the same not only in sqlite but also in postgresql etc.

solution

At first, I couldn't find it easily even if I googled, but this phenomenon was mentioned in this stackoverflow. https://stackoverflow.com/questions/53706125/django-default-at-database

In other words, "model and DB schema are different mons. Not null and unique will be applied, but default is impossible!" ...

I think that there are many cases where one DB is referenced / updated from multiple applications or patched manually, but Django users will not be in trouble ... As a workaround, it seems better to steadily generate DDL (raw SQL) from ERD instead of DDL generated from model.

Finally

I was surprised at how popular FW allows such behavior with problems in practicality. I reaffirmed that it is more important to suppress low-layer parts such as reading raw SQL to solve problems.

What Python beginners got hooked on with Django