How To Drop / Change Tables From SQLite3 Database In Django

When you use SQLite3 as your Django application backend database, you may need to change or even drop tables from the SQLite3 database. This article will tell you how to do that.

1. Create Model Related Tables In SQLite3.

1. First we add below code in Django project dept_emp / models.py file to define a new model class TestModel .

   class TestModel(models.Model):
   
       field_1 = models.CharField(max_length=100, default='field_1')
   
       field_2 = models.CharField(max_length=100, default='field_2')

2. Then open terminal and go to above Django project roor directory.
3. Now execute python3 manage.py makemigrations django_app_name command in the termianl, you can get below output. It will check whether there has model changes in your Django application.

   $ python3 manage.py makemigrations dept_emp
   System check identified some issues:
   
   Migrations for 'dept_emp':
     dept_emp/migrations/0008_testmodel.py
       - Create model TestModel
   

4. Then run python3 manage.py migrate dept_emp command in terminal to apply the migrations to backend database.

   $ python3 manage.py migrate dept_emp
   System check identified some issues:
   
   Operations to perform:
     Apply all migrations: dept_emp
   Running migrations:
     Applying dept_emp.0008_testmodel... OK
   

5. Now run python3 manage.py dbshell command to go to the database shell.

   $ python3 manage.py dbshell
   SQLite version 3.22.0 2018-01-22 18:45:57
   Enter ".help" for usage hints.
   

6. Run .databases db shell command to display the SQLite database file location.

   sqlite> .databases
   main: /home/zhaosong/WorkSpace/Work/dev2qa.com-example-code/PythonPyCharmPoject/DjangoHelloWorld/sqlite3.db

7. Run .tables command to display all the tables, you can see the table dept_emp_testmodel has been added.

   sqlite> .tables
   
   dept_emp_department
   
   dept_emp_employee         
   
   dept_emp_employee_dept    
   
   dept_emp_testmodel

8. Run .schema table_name to show the table creation sql code.

   sqlite> .schema dept_emp_testmodel
   CREATE TABLE IF NOT EXISTS "dept_emp_testmodel" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "field_1" varchar(100) NOT NULL, "field_2" varchar(100) NOT NULL);

2. Insert Model Data Into Backend Tables.

Run below command to insert one model data into the table dept_emp_testmodel.

from dept_emp.models import TestModel
TestModel.objects.create(field_1='hello', field_2='haha')
<TestModel: TestModel object (1)>

3. Add New Model Field To Backend Database Table.

Please read article How To Add New Model Field To Exist Django Model.

4. Drop Test Model Table Only.

You can see that the Django dept_emp application has four tables in the backend SQLite3 database.

dept_emp_department
dept_emp_employee 
dept_emp_employee_dept 
dept_emp_testmodel

If you just want to drop the dept_emp_testmodel table, then please follow below steps.

1. Remove the TestModel class definition in dept_emp / models.py file.
2. Go to the Django project root folder in a terminal and run makemigrations command on the dept_emp application to check model class changes.

   $ python3 manage.py makemigrations dept_emp
   System check identified some issues:
   
   Migrations for 'dept_emp':
     dept_emp/migrations/0009_delete_testmodel.py
       - Delete model TestModel
   

3. Run migrate command to apply the model changes to the backend database table.

   $ python3 manage.py migrate dept_emp
   
   Operations to perform:
     Apply all migrations: dept_emp
   Running migrations:
     Applying dept_emp.0009_delete_testmodel... OK
   

4. Each migration generated python file is saved in the dept_emp / migrations folder.

5. Drop All Model Tables In One Django Application.

If you want to remove all the model related talbes in the backend SQLite3 database, you can run below command in terminal.

$ python3 manage.py makemigrations dept_emp

$ python3 manage.py migrate dept_emp zero

But during above process, you may encounter error message like django.db.utils.IntegrityError: NOT NULL constraint failed: dept_emp_employee.dept_id.

To resolve above error, you need to delete all the past migration generated python files in the dept_emp / migrations folder, but do not remove the 0001_initial.py file. Then run below command again to drop all this Django app model tables.

$ python3 manage.py makemigrations dept_emp

$ python3 manage.py migrate dept_emp zero

If there has many to many field in model class definition, you need to remove the generated many to many table manually.

dept_emp_department
dept_emp_employee 

dept_emp_employee_dept 

dept_emp_testmodel

Above dept_emp_employee_dept table is just a many to many table, because Employee model class has dept field defined as below.

class Employee(models.Model):
    ......
    # support department and employee table are many to many relationship.
    dept = models.ManyToManyField(Department)
    ......

Follow below steps to manually drop table dept_emp_employee_dept.

1. Go to Django project root folder in a terminal.
2. Run below command to go to Django dbshell.

   $ python3 manage.py dbshell
   SQLite version 3.22.0 2018-01-22 18:45:57
   Enter ".help" for usage hints.
   

3. Run .tables to list all tables in current SQLite3 database.

   sqlite> .tables
   ......
   dept_emp_employee_dept 
   ......

4. Run drop command to drop above dept_emp_employee_dept table.

   sqlite> drop table dept_emp_employee_dept