How To Show SQL Query Command In Django Migration

Django model class is defined in 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 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 tasks to create the mapped table in db.

  1. Open a terminal and go to Django project root folder.
  2. Then run python makemigrations django_app_name command to check current Django application’s model changes.
    $ python makemigrations dept_emp
  3. Then you can find a python file like 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

    from django.conf import settings
    from django.db import migrations, models
    import django.db.models.deletion
    class Migration(migrations.Migration):
        initial = True
        dependencies = [
        operations = [
                    ('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)),
                    ('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)),
  5. Above .py file do not contain the low level sql command that will be executed to the table.
  6. Run command python sqlmigrate app_name migration_file_number in terminal to show the sql command generated with this migration .py file.
    $ python sqlmigrate dept_emp 0001
    System check identified some issues:
    ?: (urls.W001) Your URL pattern '^$' uses include with a route ending with a '$'. Remove the dollar from the route to avoid problems including URLs.
    -- 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");
  7. When you are sure the sql command is correct, you can run python migrate app_name command to run above sql command to create table in backend database.
    python 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)


  1. How To Manage Models In Django Admin Site

Leave a Comment

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.