django student project database with sqlite

@amitmund June 26, 2026

SQLite Fundamentals with Django (`dbshell`)

This guide explains how to inspect and modify your Django application's database directly using SQLite. It uses the Student CRUD project created earlier.

What is `dbshell`?

dbshell opens the command-line interface of the database configured in settings.py.

For SQLite:

python manage.py dbshell

If your project uses SQLite (default Django database), it opens the SQLite shell.

Example:

SQLite version 3.45.1
Enter ".help" for usage hints.

sqlite>

Verify Database

Before opening dbshell, check your database configuration.

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
    }
}

This means all your data is stored in

db.sqlite3

List Tables

Inside dbshell

.tables

Example output

auth_group

auth_permission

auth_user

django_admin_log

django_content_type

django_migrations

django_session

student_student

Notice

student_student

This is your Student model table.

Django naming convention

appname_modelname

Example

student_student

View Table Structure

.schema student_student

Output

CREATE TABLE student_student (

id integer NOT NULL PRIMARY KEY AUTOINCREMENT,

name varchar(100) NOT NULL,

school varchar(200) NOT NULL,

location varchar(200) NOT NULL,

contact varchar(20) NOT NULL,

created_at datetime NOT NULL

);

View All Records

SELECT * FROM student_student;

Example

1|Amit|CTTC|Bhubaneswar|9876543210|2026-06-26

2|Rahul|DAV|Cuttack|9876541234|2026-06-26

Better Output

Turn headers on

.headers on

Column mode

.mode column

Now

SELECT * FROM student_student;

Looks like

id  name    school   location      contact      created_at

1   Amit    CTTC     Bhubaneswar   9876543210   ...

2   Rahul   DAV      Cuttack       9876541234   ...

Insert Record

INSERT INTO student_student
(name, school, location, contact, created_at)

VALUES

(
'Ajay',
'KV School',
'Puri',
'9999999999',
datetime('now')
);

Check

SELECT * FROM student_student;

Refresh browser

http://127.0.0.1:8000/students/

The new record appears immediately.

Insert Multiple Records

INSERT INTO student_student
(name, school, location, contact, created_at)

VALUES

('Rakesh','DPS','Delhi','1111111111',datetime('now')),

('Suresh','DAV','Bangalore','2222222222',datetime('now'));

Select Specific Columns

SELECT
name,
school
FROM student_student;

Output

Amit

Rahul

Ajay

Select with Condition

SELECT *

FROM student_student

WHERE location='Bhubaneswar';

Select Using ID

SELECT *

FROM student_student

WHERE id=1;

Ordering

Latest first

SELECT *

FROM student_student

ORDER BY created_at DESC;

Oldest first

SELECT *

FROM student_student

ORDER BY created_at ASC;

Update Record

UPDATE student_student

SET school='KIIT'

WHERE id=1;

Verify

SELECT *

FROM student_student

WHERE id=1;

Refresh browser.

You'll see the updated value.

Update Multiple Fields

UPDATE student_student

SET

location='Bhubaneswar',

contact='8888888888'

WHERE id=2;

Delete One Record

DELETE

FROM student_student

WHERE id=3;

Refresh browser.

The record disappears.

Delete All Records

DELETE FROM student_student;

Your table is now empty.

Count Records

SELECT COUNT(*)

FROM student_student;

Example

Search

SELECT *

FROM student_student

WHERE name LIKE '%mit%';

Returns

Amit

Starts With

SELECT *

FROM student_student

WHERE name LIKE 'A%';

Ends With

SELECT *

FROM student_student

WHERE name LIKE '%t';

Multiple Conditions

SELECT *

FROM student_student

WHERE

school='DAV'

AND

location='Cuttack';

OR Condition

SELECT *

FROM student_student

WHERE

location='Puri'

OR

location='Delhi';

IN Operator

SELECT *

FROM student_student

WHERE location IN

(

'Delhi',

'Puri',

'Cuttack'

);

DISTINCT

SELECT DISTINCT school

FROM student_student;

Output

DAV

CTTC

KV School

LIMIT

SELECT *

FROM student_student

LIMIT 5;

OFFSET

SELECT *

FROM student_student

LIMIT 5 OFFSET 5;

Equivalent to page 2.

Aggregate Functions

Total Students

SELECT COUNT(*)

FROM student_student;

Maximum ID

SELECT MAX(id)

FROM student_student;

Minimum ID

SELECT MIN(id)

FROM student_student;

Rename Data

UPDATE student_student

SET name='Amit Kumar'

WHERE id=1;

Delete Using Name

DELETE

FROM student_student

WHERE name='Rahul';

SQLite Date

Today's Date

SELECT date('now');

Current Time

SELECT time('now');

DateTime

SELECT datetime('now');

Exit SQLite

.quit

.exit

View Database File

school/

├── db.sqlite3

You can open it using

DB Browser for SQLite
SQLiteStudio
VS Code SQLite extension

These tools provide a graphical interface to inspect tables and data.

Useful SQLite Commands

List tables

.tables

Show schema

.schema

Schema of one table

.schema student_student

Headers

.headers on

Column mode

.mode column

Help

.help

Quit

.quit

Complete CRUD Using SQL

Create

INSERT INTO student_student
(name,school,location,contact,created_at)

VALUES
('John','ABC School','Mumbai','9876543210',datetime('now'));

Read

SELECT *

FROM student_student;

Update

UPDATE student_student

SET school='XYZ School'

WHERE id=1;

Delete

DELETE

FROM student_student

WHERE id=1;

Experiment with Django

A great way to understand how Django interacts with the database is to alternate between the web application and dbshell.

Start the Django server:
```
python manage.py runserver
```
In another terminal, open the database shell:
```
python manage.py dbshell
```

Insert a new student directly with SQL:

INSERT INTO student_student
(name, school, location, contact, created_at)
VALUES
('Meena', 'DAV', 'Bhubaneswar', '9000000000', datetime('now'));

Refresh the /students/ page in your browser. The new record appears without using the Django form.

Update the student's location:

UPDATE student_student
SET location = 'Cuttack'
WHERE name = 'Meena';

Refresh the browser again to see the change.

Delete the record:

DELETE FROM student_student
WHERE name = 'Meena';

Refresh once more to confirm it has been removed.

This exercise demonstrates that Django's ORM stores and retrieves data from the same SQLite database you're editing manually. Changes made through SQL are immediately visible in the Django application, helping you understand the relationship between models, the ORM, and the underlying database.

Learning Path

Django Model
        │
        ▼
makemigrations
        │
        ▼
Migration File
        │
        ▼
migrate
        │
        ▼
SQLite Database
        │
        ▼
student_student Table
        │
        ▼
CRUD using Django ORM
        │
        ▼
CRUD using Raw SQL
        │
        ▼
Observe changes in the Django application

By practicing both Django ORM operations and raw SQL, you'll build a strong understanding of how Django maps Python objects to relational database tables.

SQLite Fundamentals with Django (dbshell)

What is dbshell?

Verify Database

List Tables

View Table Structure

View All Records

Better Output

Insert Record

Insert Multiple Records

Select Specific Columns

Select with Condition

Select Using ID

Ordering

Update Record

Update Multiple Fields

Delete One Record

Delete All Records

Count Records

Search

Starts With

Ends With

Multiple Conditions

OR Condition

IN Operator

DISTINCT

LIMIT

OFFSET

Aggregate Functions

Rename Data

Delete Using Name

SQLite Date

Exit SQLite

View Database File

Useful SQLite Commands

Complete CRUD Using SQL

Experiment with Django

Learning Path

Filters

SQLite Fundamentals with Django (`dbshell`)

What is `dbshell`?