import csv
import time
from io import StringIO, BytesIO
from django.http import HttpResponse
from .models import Boss, Department
# 定义枚举翻译映射
def get_translation_maps():
return {
'industry': {status: status.label for status in Boss.Industry},
'department_type': {status: status.label for status in Department.DepartmentType},
}
TRANSLATION_MAPS = get_translation_maps()
# 字段名到中文表头的映射
HEADER_TRANSLATIONS = {
'id': '编号',
'name': '名字',
'industry': '行业',
'department_name': '部门名称',
'department_type': '部门类型',
}
def translate_value(key, value):
"""翻译字段值"""
return TRANSLATION_MAPS.get(key, {}).get(value, value)
def translate_row(row):
"""翻译单行数据,包括外键字段的处理"""
translated_row = {}
for key, value in row.items():
if key == 'department__name': # 外键字段
translated_row['department_name'] = value
elif key == 'department__department_type': # 外键字段
translated_row['department_type'] = translate_value('department_type', value)
elif key in TRANSLATION_MAPS: # 枚举字段
translated_row[key] = translate_value(key, value)
else:
translated_row[key] = value
return translated_row
def row_generator(queryset):
"""生成器函数,逐行处理数据"""
for row in queryset:
yield translate_row(row)
def export_to_csv_without_pandas(request):
# 查询数据
queryset = Boss.objects.select_related('department').values('id', 'name', 'industry', 'department__name', 'department__department_type')
# 导出为 CSV 文件
start_time = time.time()
# 使用 StringIO 来处理 CSV 数据
string_io = StringIO()
writer = csv.writer(string_io)
# 处理表头
try:
first_row = next(row_generator(queryset)) # 读取一行以确定表头
# 使用 HEADER_TRANSLATIONS 进行表头翻译
translated_headers = [HEADER_TRANSLATIONS.get(header, header) for header in first_row.keys()]
writer.writerow(translated_headers)
# 写入数据
for row in row_generator(queryset):
writer.writerow(row.values())
except StopIteration:
pass # 如果查询集为空,不做任何操作
# 从 StringIO 获取 CSV 数据并转换为字节
csv_data = string_io.getvalue().encode('utf-8-sig') # UTF-8 编码并添加 BOM
string_io.close()
# 使用 BytesIO 来处理字节流
output = BytesIO(csv_data)
time_taken = time.time() - start_time
# 输出时间
print(f"Time taken without pandas: {time_taken} seconds")
# 创建 HttpResponse
response = HttpResponse(output.getvalue(), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=my_data.csv'
return response
优化
import csv
import time
from io import StringIO, BytesIO
from django.http import HttpResponse
from django.core.paginator import Paginator
# 定义枚举翻译映射
def get_translation_maps():
return {
'industry': {status: str(status.label) for status in Boss.Industry},
# 'department_type': {status: status.label for status in Department.DepartmentType},
}
TRANSLATION_MAPS = get_translation_maps()
# 字段名到中文表头的映射
HEADER_TRANSLATIONS = {
'id': '编号',
'name': '名字',
'industry': '行业',
'company':'公司',
# 'department_name': '部门名称',
# 'department_type': '部门类型',
}
def translate_values(key, value):
"""翻译字段值"""
return TRANSLATION_MAPS.get(key, {}).get(value, value)
def translate_row(row):
"""翻译单行数据,包括外键字段的处理"""
translated_row = {}
for key, value in row.items():
if key == 'department__name': # 外键字段
translated_row['department_name'] = value
elif key == 'department__department_type': # 外键字段
translated_row['department_type'] = translate_values('department_type', value)
elif key in TRANSLATION_MAPS: # 枚举字段
translated_row[key] = translate_values(key, value)
else:
translated_row[key] = value
return translated_row
def export_to_csv_without_pandas(request):
# 查询数据
queryset = Boss.objects.values('id', 'name',"company", 'industry')
# 导出为 CSV 文件
start_time = time.time()
# 使用 StringIO 来处理 CSV 数据
string_io = StringIO()
writer = csv.writer(string_io)
# 分页处理数据
paginator = Paginator(queryset, 50000) # 每页处理1000条记录
# 写入表头
first_page = paginator.page(1)
first_row = translate_row(first_page.object_list[0])
translated_headers = [HEADER_TRANSLATIONS.get(header, header) for header in first_row.keys()]
writer.writerow(translated_headers)
# 写入数据
for page_number in paginator.page_range:
page = paginator.page(page_number)
for row in page.object_list:
translated_row = translate_row(row)
writer.writerow(translated_row.values())
# 从 StringIO 获取 CSV 数据并转换为字节
csv_data = string_io.getvalue().encode('utf-8-sig') # UTF-8 编码并添加 BOM
string_io.close()
# 使用 BytesIO 来处理字节流
output = BytesIO(csv_data)
time_taken = time.time() - start_time
# 输出时间
print(f"Time taken without pandas: {time_taken} seconds")
# 创建 HttpResponse
response = HttpResponse(output.getvalue(), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=my_data.csv'
return response```