Single Table Operations

Creating Records

Method 1: Using save() on an instance

from app.models import Product

new_product = Product(name='Laptop', cost=1500, category='Electronics')
new_product.save()

Method 2: Using the create() manager method (Recommended)

Product.objects.create(name='Tablet', cost=800, category='Electronics')

Method 3: Batch insertion with bulk_create()

product_batch = []
for i in range(1000):
    product_batch.append(Product(name=f'Item_{i}', cost=i*10))
Product.objects.bulk_create(product_batch, batch_size=500)

Deleting Records

Method 1: Delete a single instance

instance = Product.objects.get(id=5)
instance.delete()

Method 2: Delete using a QuerySet filter

Product.objects.filter(category='Obsolete').delete()

Querying Records

Retrieve all records

all_products = Product.objects.all()

Filter records

filtered = Product.objects.filter(category='Electronics', cost__lt=1000)

Exclude records

excluded = Product.objects.exclude(is_active=False)

Retrieve a single object

try:
    single_product = Product.objects.get(id=1)
except Product.DoesNotExist:
    # Handle not found
except Product.MultipleObjectsReturned:
    # Handle multiple matches

Ordering results

ordered_asc = Product.objects.order_by('name')
ordered_desc = Product.objects.order_by('-cost')

Counting records

total_count = Product.objects.count()
active_count = Product.objects.filter(is_active=True).count()

First and last records

first_item = Product.objects.first()
last_item = Product.objects.last()

Check for existence

has_records = Product.objects.exists()
has_expensive = Product.objects.filter(cost__gt=5000).exists()

Select specific fields

field_values = Product.objects.values('name', 'category')
field_tuples = Product.objects.values_list('name', flat=True)

Remove duplicates

unique_categories = Product.objects.values('category').distinct()

Field lookups

# Greater than
Product.objects.filter(cost__gt=100)
# Less than or equal
Product.objects.filter(cost__lte=50)
# Contains
Product.objects.filter(name__contains='Pro')
# Case-insensitive contains
Product.objects.filter(description__icontains='wireless')
# In list
Product.objects.filter(id__in=[1, 5, 9])
# Date range
from datetime import date
Product.objects.filter(release_date__range=[date(2023,1,1), date(2023,12,31)])

Updating Records

Method 1: Update an instance

product = Product.objects.get(id=10)
product.cost = 1200
product.save()

Method 2: Bulk update with QuerySet

Product.objects.filter(category='Clearance').update(cost=0)

Multi-Table Operations

Model Definitions

class Publisher(models.Model):
    company_name = models.CharField(max_length=100)
    location = models.CharField(max_length=100)

class Author(models.Model):
    full_name = models.CharField(max_length=100)
    birth_year = models.IntegerField()
    details = models.OneToOneField('AuthorDetail', on_delete=models.CASCADE)

class AuthorDetail(models.Model):
    contact_number = models.CharField(max_length=20)
    residence = models.CharField(max_length=200)

class Book(models.Model):
    title = models.CharField(max_length=200)
    retail_price = models.DecimalField(max_digits=6, decimal_places=2)
    publication_date = models.DateField()
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    writers = models.ManyToManyField(Author)

Creating Related Records

ForeignKey relationship (One-to-Many)

# Using object instance
pub = Publisher.objects.get(id=2)
Book.objects.create(title='Advanced Python', retail_price=45.99, publisher=pub)

# Using foreign key ID
Book.objects.create(title='Django Guide', retail_price=39.99, publisher_id=2)

ManyToMany relationship

book_instance = Book.objects.get(title='Python Fundamentals')
author1 = Author.objects.get(full_name='Jane Smith')
author2 = Author.objects.get(full_name='John Doe')
book_instance.writers.add(author1, author2)

# Using author IDs
book_instance.writers.add(3, 7)

Querying Across Relationships

Forward relationships

# Book to Publisher
book = Book.objects.get(id=5)
publisher_city = book.publisher.location

# Author to AuthorDetail
author = Author.objects.get(id=3)
phone = author.details.contact_number

Reverse relationships

# Publisher to Books (One-to-Many reverse)
publisher = Publisher.objects.get(company_name='Tech Press')
books_by_publisher = publisher.book_set.all()

# AuthorDetail to Author (One-to-One reverse)
detail = AuthorDetail.objects.get(id=4)
associated_author = detail.author

# Author to Books (Many-to-Many reverse)
author = Author.objects.get(full_name='Alex Johnson')
books_by_author = author.book_set.all()

Field lookups across relationships

# Books published by a specific publisher
Book.objects.filter(publisher__company_name='Science House')

# Authors living in a specific city
Author.objects.filter(details__residence='New York')

# Publishers with books priced over $50
Publisher.objects.filter(book__retail_price__gt=50).distinct()

Aggregation and Grouping

from django.db.models import Avg, Max, Min, Sum, Count

# Average price of all books
Book.objects.aggregate(average_price=Avg('retail_price'))

# Maximum and minimum prices
stats = Book.objects.aggregate(
    highest=Max('retail_price'),
    lowest=Min('retail_price'),
    total_items=Count('id')
)

# Group authors by birth year with book count
author_stats = Author.objects.values('birth_year').annotate(
    book_count=Count('book')
).order_by('birth_year')

# Total value of books by publisher
publisher_totals = Publisher.objects.annotate(
    total_value=Sum('book__retail_price')
).values('company_name', 'total_value')

Advanced Query Operations

F() Expressions for Field Comparisons

from django.db.models import F

# Books where inventory is less than sold copies
Book.objects.filter(current_stock__lt=F('units_sold'))

# Increase all prices by 10%
Book.objects.update(retail_price=F('retail_price') * 1.1)

Q() Objects for Complex Queries

from django.db.models import Q

# Books priced under $20 OR published this year
current_year = date.today().year
Book.objects.filter(
    Q(retail_price__lt=20) | 
    Q(publication_date__year=current_year)
)

# Books not in specific categories
Book.objects.filter(
    ~Q(category='Fiction') & 
    ~Q(category='Biography')
)

Optimized Relationship Queries

select_related() for ForeignKey and OneToOne

Reduces database queries by performing SQL JOINs.

# Single query to fetch book with publisher details
books = Book.objects.select_related('publisher').filter(title__contains='Python')
for book in books:
    print(book.title, book.publisher.company_name)  # No extra query

prefetch_related() for ManyToMany and reverse ForeignKey

Performs separate lookups and joins in Python.

# Efficiently fetch books with all their authors
books = Book.objects.prefetch_related('writers').all()
for book in books:
    author_names = [author.full_name for author in book.writers.all()]
    print(book.title, author_names)

Complex Join Operations

# Annotate publishers with aggregated book data
from django.db.models import Avg, Count

publisher_data = Publisher.objects.annotate(
    book_count=Count('book'),
    avg_price=Avg('book__retail_price'),
    latest_pub=Max('book__publication_date')
).filter(book_count__gt=0).order_by('-avg_price')