Django实现百万级数据Excel下载：解决MemoryError与Nginx超时

# 导出Excel文件
def export_excel(request):
  city = request.POST.get('city')
  print(city)
  list_obj=place.objects.filter(city=city)
  # 设置HTTPResponse的类型
  response = HttpResponse(content_type='application/vnd.ms-excel')
  response['Content-Disposition'] = 'attachment;filename='+city+'.xls'
  """导出excel表"""
  if list_obj:
    # 创建工作簿
    ws = xlwt.Workbook(encoding='utf-8')
    # 添加第一页数据表
    w = ws.add_sheet('sheet1') # 新建sheet（sheet的名称为"sheet1"）
    # 写入表头
    w.write(0, 0, u'地名')
    w.write(0, 1, u'次数')
    w.write(0, 2, u'经度')
    w.write(0, 3, u'纬度')
    # 写入数据
    excel_row = 1
    for obj in list_obj:
      name = obj.place
      sum = obj.sum
      lng = obj.lng
      lat = obj.lat
      # 写入每一行对应的数据
      w.write(excel_row, 0, name)
      w.write(excel_row, 1, sum)
      w.write(excel_row, 2, lng)
      w.write(excel_row, 3, lat)
      excel_row += 1
    # 写出到IO
    output = BytesIO()
    ws.save(output)
    # 重新定位到开始
    output.seek(0)
    response.write(output.getvalue())
  return response

$("#export_excel").click(function () {
     var csrf=$('input[name="csrfmiddlewaretoken"]').val();
     const req = new XMLHttpRequest();
     req.open('POST', '/export_excel/', true);
     req.responseType = 'blob';
     req.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded'); //设置请求头
     req.send('city='+$('#city').val()+"&&csrfmiddlewaretoken="+csrf); //输入参数
     req.onload = function() {
       const data = req.response;
       const a = document.createElement('a');
       const blob = new Blob([data]);
       const blobUrl = window.URL.createObjectURL(blob);
       download(blobUrl) ;
     };

   });

function download(blobUrl) {
 var city = $("input[name='city']").val();
 const a = document.createElement('a');
 a.style.display = 'none';
 a.download = '<文件命名>';
 a.href = blobUrl;
 a.click();
 document.body.removeChild(a);
}

def download(request):
 file=open('path/demo.py','rb')
  response =FileResponse(file)
  response['Content-Type']='application/octet-stream'
  response['Content-Disposition']='attachment;filename="demo.py"'
  return response

# 通过pymysql取得数据
import pymysql
field_types = {
    1: 'tinyint',
    2: 'smallint',
    3: 'int'} #用于后面的字段名匹配，这里省略了大多数
conn = pymysql.connect(host='127.0.0.1',port=3306,database='demo',user='root',password='root')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(sql)
#获取所有数据
data = cursor.fetchall()
cols = {}
#获取所有字段
for i,row in enumerate(self.cursor.description):
 if row[0] in cols:
   cols[str(i)+row[0]] = field_types.get(row[1], str(row[1])) #这里的field_type是类型和数字的匹配
 cols[row[0]] = field_types.get(row[1], str(row[1]))
cursor.close()
conn.close()

#通过StreamingHttpResponse指定返回格式为csv
response = StreamingHttpResponse(get_result_fromat(data, cols))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment;filename="{0}"'.format(out_file_name)
return response

#循环所有数据，然后加到字段上返回，注意的是要用迭代器来控制
def get_result_fromat(data, cols):
 tmp_str = ""
 # 返回文件的每一列列名
  for col in cols:
    tmp_str += '"%s",' % (col)
  yield tmp_str.strip(",") + "\n"
  for row in data:
    tmp_str = ""
    for col in cols:
      tmp_str += '"%s",' % (str(row[col]))
    yield tmp_str.strip(',') + "\n"

def get_result_fromat(data, cols):
 tmp_str = ""
 # 返回文件的每一列列名
  for col in cols:
    tmp_str += '"%s",' % (col)
  yield tmp_str.strip(",") + "\n"
  for row in data:
    tmp_str = ""
    for col in cols:
      tmp_str += '"%s",' % (str(row[col]))
    yield tmp_str.strip(',') + "\n" 
    
    =====>
    
def get_result_fromat(data, cols):
 tmp_str = ""
  for col in cols:
    tmp_str += '"%s",' % (col)
  yield tmp_str.strip(",") + "\n"
  while True:
    tmp_str = ""
    for col in cols:
      tmp_str += '"%s",' % (str(row[col]))
    yield tmp_str.strip(',') + "\n"
    row = db.cursor.fetchone()
    if row is None:
      break