Django ORM Practical Guide: CRUD Operations with SQLite Database

In web development, database operations are one of the core components. As a mainstream Python web framework, Django provides a powerful ORM (Object-Relational Mapping) tool, allowing us to operate on databases directly using Python code without writing complex SQL statements. SQLite, Django’s default database, is lightweight and easy to use, making it ideal for development and learning stages (it does not require a separate server installation and stores data in file form).

I. Preparation: Configuring Django with SQLite¶

1. Creating a Django Project¶

First, ensure Django is installed. If not, install it using:

pip install django

Then create a project and an app:

django-admin startproject myproject
cd myproject
python manage.py startapp myapp

2. Configuring the SQLite Database¶

Django is pre-configured with SQLite by default. Open myproject/settings.py and confirm the DATABASES configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',  # Path to the SQLite database file
    }
}

3. Creating Data Models¶

Define a simple model (corresponding to a database table) in myapp/models.py, e.g., a user information table:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)  # String type, length must be specified
    age = models.IntegerField()  # Integer type
    email = models.EmailField(unique=True)  # Email type, unique=True ensures unique values

    def __str__(self):
        return self.name  # Display username when printing the object

4. Migrating Models to the Database¶

Execute the following commands to map models to SQLite tables:

python manage.py makemigrations  # Generate migration files
python manage.py migrate  # Apply migrations to create database tables

II. Django ORM CRUD (Create, Read, Update, Delete) Practical Operations¶

Create: Adding Data to the Database¶

Method 1: Direct Creation and Saving¶

Use the model class’s objects.create() method to create and return a new object directly:

# Execute in Django shell (python manage.py shell)
from myapp.models import User

# Create user 1
user1 = User.objects.create(
    name="张三", 
    age=20, 
    email="zhangsan@example.com"
)
print(user1.id)  # Output the new user's ID (e.g., 1)

Method 2: Instantiation First, Then Saving¶

Create a model object first, then call the save() method to save it:

# Create user 2
user2 = User()
user2.name = "李四"
user2.age = 22
user2.email = "lisi@example.com"
user2.save()  # Required to save to the database

Note: create() is suitable for quickly adding single records, while save() is better for scenarios where partial fields are set first and then modified.

Read: Querying Data from the Database¶

1. Retrieve All Records: `all()`¶

# Get all users
all_users = User.objects.all()
for user in all_users:
    print(user.name, user.age, user.email)

2. Conditional Queries: `filter()` and `get()`¶

filter(): Returns all records matching the condition (returns a QuerySet object, similar to a list)
get(): Returns a single record matching the condition (raises an error if no results or multiple results)

# 1. Get users aged 20
users = User.objects.filter(age=20)
for user in users:
    print(user.name)  # Output: 张三

# 2. Get user with email zhangsan@example.com
user = User.objects.get(email="zhangsan@example.com")
print(user.name)  # Output: 张三

# 3. With condition modifiers (double underscore __)
# Age > 18
users = User.objects.filter(age__gt=18)
# Age < 25
users = User.objects.filter(age__lt=25)
# Name contains "张"
users = User.objects.filter(name__contains="张")
# Unique email (assuming unique=True is set)
user = User.objects.filter(email__exact="zhangsan@example.com")  # Equivalent to get()

3. Sorting and Limiting¶

order_by(): Sort (ascending by default; prefix with - for descending)
first()/last(): Get the first/last record
count(): Count the number of records

# Sort by age ascending
users = User.objects.order_by("age")
# Sort by age descending
users = User.objects.order_by("-age")
# Get the oldest user
oldest = User.objects.order_by("-age").first()
# Count total users
total = User.objects.count()

Update: Modifying Database Records¶

Method 1: Query First, Then Modify (Single Update)¶

# Get a user and modify
user = User.objects.get(id=1)
user.age = 21  # Modify age
user.save()  # Save changes

Method 2: Batch Update (`update()`)¶

Suitable for modifying multiple records, returns the number of modified rows:

# Update all users aged 20 to 21
updated_rows = User.objects.filter(age=20).update(age=21)
print(f"Updated {updated_rows} records")  # Output: 1

Note: update() directly executes the SQL UPDATE statement and does not trigger the model’s save() method or signals.

Delete: Removing Database Records¶

Method 1: Query First, Then Delete (Single Delete)¶

# Delete user with ID 1
user = User.objects.get(id=1)
user.delete()

Method 2: Batch Delete (`delete()`)¶

# Delete all users aged 21
deleted_rows = User.objects.filter(age=21).delete()
print(f"Deleted {deleted_rows[0]} records")  # Output: 1 (if 1 record was deleted)

Note: delete() returns a tuple (number of deleted records, {model_type: count}); typically, only the first value is needed.

III. Common Issues and Precautions¶

QuerySet is “Lazy”: Queries are executed only when data is needed (e.g., iteration, count()). For example:

   users = User.objects.filter(age=20)  # No SQL executed yet
   print(users)  # SQL executed here to return results

Avoid Duplicate Data: If a field is set to unique=True (e.g., email in the example), inserting duplicates will raise IntegrityError. Check first:

   if User.objects.filter(email="zhangsan@example.com").exists():
       print("Email already exists")
   else:
       User.objects.create(...)

Exception Handling for get(): get() raises DoesNotExist if no records are found or MultipleObjectsReturned if multiple records match. Use try-except to handle:

   try:
       user = User.objects.get(id=999)
   except User.DoesNotExist:
       print("User does not exist")

IV. Summary¶

With Django ORM, we can operate on SQLite databases using Python objects without writing complex SQL. The core steps are:
1. Define a model in models.py → maps to a database table
2. Run makemigrations and migrate to generate the table structure
3. Use the model’s objects manager to perform CRUD operations

This approach makes code cleaner and easier to maintain, ideal for beginners to quickly master database operations. Later, combine with Django Views and Templates to render ORM query results into web pages, completing the full web development process.