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 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')
- Then open terminal and go to above Django project roor directory.
- 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
- 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
- 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
.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
- 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
- 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.
- Remove the TestModel class definition in dept_emp / models.py file.
- 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
- 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
- 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.
- Go to Django project root folder in a terminal.
- 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.
- Run .tables to list all tables in current SQLite3 database.
sqlite> .tables ...... dept_emp_employee_dept ......
- Run drop command to drop above dept_emp_employee_dept table.
sqlite> drop table dept_emp_employee_dept