Django批量查询优化的多种实现方案

# 获取所有电影对象（避免内存溢出）
films = film.objects.all()

# 使用迭代器处理大规模数据 
for film in films.iterator(chunk_size=1000):
    # 处理每个电影对象
    print(film.title)

# 获取2023年上映的电影
films_2023 = film.objects.filter(year=2023)

# 获取评分大于4.0的电影
high_rated_films = film.objects.filter(rating__gt=4.0)

# 只获取需要的字段
film_titles = film.objects.values_list('title', flat=true)

# 获取特定字段组合
film_data = film.objects.values('id', 'title', 'year')

# 使用select_related优化外键查询
films = film.objects.select_related('studio').all()

# 使用prefetch_related优化多对多关系
films = film.objects.prefetch_related('actors').all()

# 获取特定id集合的电影
film_ids = [101, 205, 307, 409]
films = film.objects.in_bulk(film_ids, field_name='id')

# 使用结果
film_205 = films[205]

from django.core.paginator import paginator

# 每页100条记录
paginator = paginator(film.objects.all(), 100)

for page_num in range(1, paginator.num_pages + 1):
    page = paginator.page(page_num)
    for film in page.object_list:
        # 处理每部电影
        process_film(film)

# 创建多个电影对象
films_to_create = [
    film(title="电影a", year=2023),
    film(title="电影b", year=2022),
    film(title="电影c", year=2021)
]

# 批量创建（返回创建的对象列表）
created_films = film.objects.bulk_create(films_to_create)

# 批量更新评分
films_to_update = film.objects.filter(year=2023)
for film in films_to_update:
    film.rating = 4.5  # 更新评分

# 批量更新到数据库
film.objects.bulk_update(films_to_update, ['rating'])

# 删除所有2020年之前的电影
film.objects.filter(year__lt=2020).delete()

# 只加载必要字段
films = film.objects.only('title', 'year')

# 排除大字段
films = film.objects.defer('plot')

# 批量添加演员
film = film.objects.get(id=101)
actors_to_add = actor.objects.filter(id__in=[1, 2, 3])
film.actors.add(*actors_to_add)

# 批量移除演员
film.actors.remove(*actors_to_add)

from django.db import connection

def bulk_update_ratings():
    with connection.cursor() as cursor:
        cursor.execute("""
            update film_information
            set rating = rating * 1.1
            where year >= 2020
        """)

def update_film_ratings():
    # 获取需要更新的电影
    films = film.objects.filter(year__gte=2020)
    
    # 批量更新评分
    for film in films:
        film.rating = calculate_new_rating(film)
    
    # 批量提交到数据库
    film.objects.bulk_update(films, ['rating'], batch_size=500)

def import_films_from_csv(csv_path):
    import csv
    films_to_create = []
    
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.dictreader(f)
        for row in reader:
            films_to_create.append(film(
                title=row['title'],
                year=int(row['year']),
                # ...其他字段
            ))
    
    # 分批次创建（每批1000条）
    batch_size = 1000
    for i in range(0, len(films_to_create), batch_size):
        batch = films_to_create[i:i+batch_size]
        film.objects.bulk_create(batch)

def process_film_tags():
    # 获取所有电影及其标签
    films = film.objects.prefetch_related('tags')
    
    for film in films:
        # 处理每个电影的标签
        tags_to_add = calculate_new_tags(film)
        film.tags.add(*tags_to_add)
        
        # 移除不需要的标签
        tags_to_remove = get_obsolete_tags(film)
        film.tags.remove(*tags_to_remove)

from django.db import transaction

with transaction.atomic():
    # 批量操作代码
    film.objects.bulk_create(films)

try:
    film.objects.bulk_update(films, ['rating'])
except exception as e:
    logger.error(f"批量更新失败: {str(e)}")
    # 回退或重试逻辑

from django.db import connection

print(connection.queries)  # 查看执行的sql查询