How To Show SQL Query Command In Django Migration

Django model class is defined in models.py file in Django application folder, each model class will be mapped to a database table in the project backend database server. After you define the model class, you can create the related table use Django manage.py tasks command, all the low level sql command ( such as table create, update etc ) will be generated automatically by Django, you do not need to care about that. But some times you really want to see the sql command generated by Django, this example will show you how to get it.

django project app dept_emp source files structure

1. How To Display Django Application Migration SQL Command.

After you define the model classes, you can use below Django manage.py tasks to create the mapped table in db.

  1. Open a terminal and go to Django project root folder.
  2. Then run python manage.py makemigrations django_app_name command to check current Django application’s model changes.
    $ python manage.py makemigrations dept_emp
  3. Then you can find a python file like 0001_initial.py has been added in the django_application (dept_emp) / migrations folder if there has any model changes. All the model changes migration files will be saved in this folder.
  4. Below is the source code of file 0001_initial.py.

    from django.conf import settings
    from django.db import migrations, models
    import django.db.models.deletion
    
    
    class Migration(migrations.Migration):
    
        initial = True
    
        dependencies = [
            migrations.swappable_dependency(settings.AUTH_USER_MODEL),
        ]
    
        operations = [
            migrations.CreateModel(
                name='Department',
                fields=[
                    ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                    ('dept_name', models.CharField(max_length=1000)),
                    ('dept_desc', models.CharField(max_length=1000)),
                ],
            ),
            migrations.CreateModel(
                name='Employee',
                fields=[
                    ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                    ('emp_mobile', models.CharField(max_length=100)),
                    ('emp_salary', models.IntegerField()),
                    ('emp_onboard_date', models.DateTimeField(auto_now=True)),
                    ('dept', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='dept_emp.Department')),
                    ('user', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to=settings.AUTH_USER_MODEL)),
                ],
            ),
            migrations.AlterUniqueTogether(
                name='department',
                unique_together={('dept_name',)},
            ),
            migrations.AlterUniqueTogether(
                name='employee',
                unique_together={('emp_mobile',)},
            ),
        ]
    
  5. Above .py file do not contain the low level sql command that will be executed to the table.
  6. Run command python manage.py sqlmigrate app_name migration_file_number in terminal to show the sql command generated with this migration .py file.
    $ python manage.py sqlmigrate dept_emp 0001
    System check identified some issues:
    
    WARNINGS:
    ?: (urls.W001) Your URL pattern '^$' uses include with a route ending with a '$'. Remove the dollar from the route to avoid problems including URLs.
    BEGIN;
    --
    -- Create model Department
    --
    CREATE TABLE "dept_emp_department" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "dept_name" varchar(1000) NOT NULL, "dept_desc" varchar(1000) NOT NULL);
    --
    -- Create model Employee
    --
    CREATE TABLE "dept_emp_employee" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "emp_mobile" varchar(100) NOT NULL, "emp_salary" integer NOT NULL, "emp_onboard_date" datetime NOT NULL, "dept_id" integer NOT NULL REFERENCES "dept_emp_department" ("id") DEFERRABLE INITIALLY DEFERRED, "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED);
    --
    -- Alter unique_together for department (1 constraint(s))
    --
    CREATE UNIQUE INDEX dept_emp_department_dept_name_f1b58519_uniq ON "dept_emp_department" ("dept_name");
    --
    -- Alter unique_together for employee (1 constraint(s))
    --
    CREATE UNIQUE INDEX dept_emp_employee_emp_mobile_e3bcd06e_uniq ON "dept_emp_employee" ("emp_mobile");
    CREATE INDEX "dept_emp_employee_dept_id_9eccbf29" ON "dept_emp_employee" ("dept_id");
    CREATE INDEX "dept_emp_employee_user_id_2895129d" ON "dept_emp_employee" ("user_id");
    COMMIT;
    
  7. When you are sure the sql command is correct, you can run python manage.py migrate app_name command to run above sql command to create table in backend database.
    python manage.py migrate dept_emp
  8. As you can see in the sql command, all the table name is combined by app-name_model-class-name. And each table has automatically create an auto-increment id primary key field by default. If you do not want to use the default primary key and want to specify one of the model class filed as primary key, you can set primary_key=True in the model class field definition like below.
    class Employee(models.Model):
        emp_name = models.CharField(max_length=100, primary_key=True)

Reference

  1. How To Manage Models In Django Admin Site
READ :   How To Create Django View Template Files Structure Correctly Use Block & Include Tag

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.