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.

First, we add the below code in the 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')

Then open a terminal and go to the above Django project root directory.
Now execute python3 manage.py makemigrations django_app_name command in the terminal, you can get below output. It will check whether there are 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
```

Then run python3 manage.py migrate dept_emp command in the terminal to apply the migrations to the 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

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.

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

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

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

sqlite> .tables

dept_emp_department

dept_emp_employee         

dept_emp_employee_dept    

dept_emp_testmodel

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 the 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 the 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 table dept_emp_testmodel, then please follow the below steps.
Remove the TestModel class definition in dept_emp / models.py file.

Go to the Django project root folder in a terminal and run the command makemigrations on the application dept_emp 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

Run the command migrate 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

All the migration process generated python files are 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 tables in the backend SQLite3 database, you can run the below command in the terminal.
```
$ python3 manage.py makemigrations dept_emp

$ python3 manage.py migrate dept_emp zero
```
But during the above process, you may encounter error message like django.db.utils.IntegrityError: NOT NULL constraint failed: dept_emp_employee.dept_id.
To resolve the 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 the below command again to drop all these Django app model tables.

$ python3 manage.py makemigrations dept_emp

$ python3 manage.py migrate dept_emp zero

If there has many to many fields in the model class definition, you need to remove the generated many to many tables 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 tables, 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 the below steps to manually drop the table dept_emp_employee_dept.
Go to the Django project root folder in a terminal.

Run the 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.

Run the .tables to list all tables in the current SQLite3 database.
```
sqlite> .tables
......
dept_emp_employee_dept 
......
```
Run the drop command to drop above table dept_emp_employee_dept.
```
sqlite> drop table dept_emp_employee_dept
```