Hello guys,
I'm working on my Django project and I need to integrate Celery to do
something.
I'm using Django 1.11.20.
*My context :*
I have a template page with a search form. Once you did a search, it
returns a table with search results.
It's possible to export to .xls format with/without search.
There are 2 possibilities :
- If user wants to export *less than 70.000 objects*, the export creates
an HttpResponse with download link.
- If user wants to export *more than 70.000 objects*, a Celery task has
to be initiate, it creates the file, save it into the MEDIA folder and user
gets an email with expirable download link.
I'm trying to implement this second part.
The check is done on template page with number of results according to the
search part.
If < 70.000 objects, export button calls *export_xls()* function, else it
calls the Celery task
*get_xls_export()HTML Template file :*
{% if item_count > 0 and item_count < 70000 %}
{% if search_info_str %}
<a title="Print" href="{% url print_link model='finalproduct'
search_info=search_info_str %}" class="button btn btn-default
print_items"><span class="glyphicon glyphicon-print"></span></a>
<a title="Export to Excel" class="button btn btn-default" href="{%
url 'ocabr:export-xls' model=model search_info=search_info_str %}"><span
class="glyphicon glyphicon-export"></span></a>
{% else %}
<a title="Print" href="{% url print_link model='finalproduct' %}"
class="button btn btn-default print_items"><span class="glyphicon
glyphicon-print"></span></a>
<a title="Export to Excel" class="button btn btn-default" href="{%
url 'ocabr:export-xls' model=model %}"><span class="glyphicon
glyphicon-export"></span></a>
{% endif %}
{% else %}
{% if search_info_str %}
<a title="Print" href="{% url print_link model='finalproduct'
search_info=search_info_str %}" class="button btn btn-default
print_items"><span class="glyphicon glyphicon-print"></span></a>
<a title="Export to Excel" name="export" value="export" class="button
btn btn-default" href="{% url 'ocabr:cron_export' model=model
search_info=search_info_str %}"><span class="glyphicon
glyphicon-export"></span></a>
{% else %}
<a title="Print" href="{% url print_link model='finalproduct' %}"
class="button btn btn-default print_items"><span class="glyphicon
glyphicon-print"></span></a>
<a title="Export to Excel" name="export" value="export" class="button
btn btn-default" href="{% url 'ocabr:cron_export' model=model %}"><span
class="glyphicon glyphicon-export"></span></a>
{% endif %}
{% endif %}
This part calls the right function depending on search result.
*Case 1 : Less than 70.000 objects*
The view associated to the previous HTML template is :
class FinalProductListView(PermissionRequiredMixin, EdqmListSearchView):
model = FinalProduct
page_title = _('Final Products')
default_sort_params = ('manufacturer_name', 'asc')
sort_params = ['pk']
create_link = 'ocabr:finalproduct-add'
update_link = 'ocabr:finalproduct-edit'
delete_link = 'ocabr:finalproduct-delete'
view_link = 'ocabr:finalproduct-view'
print_link = 'ocabr:print-object'
permission_required = 'ocabr.view_finalproduct'
template_name = 'ocabr/final_product/final_product_search.html'
paginate_by = getattr(PaginationMixin, 'paginate_by', None)
headers = {
'releasing_body__short_name': 'Releasing Body/Institution',
'is_withdrawn': 'Is nullified',
}
# Configuration of fields
fields = ['manufacturer_name', 'releasing_body__short_name',
'product_trade_name', 'pheur_name', 'name', 'final_bulk',
'decision', 'date_decision', 'is_withdrawn', 'member_state']
def get_queryset(self):
queryset = super(FinalProductListView, self).get_queryset()
date_format = settings.USER_DATE_FORMAT
# Add filter from search form.
if 'mah' in self.request.GET:
if self.request.GET['mah'] != '':
queryset =
queryset.filter(mah__icontains=self.request.GET['mah'].strip())
if 'releasing_body' in self.request.GET:
if self.request.GET['releasing_body']:
queryset =
queryset.filter(releasing_body__id=int(self.request.GET['releasing_body']))
if 'trade_name' in self.request.GET:
if self.request.GET['trade_name'] != '':
queryset =
queryset.filter(product_trade_name__icontains=self.request.GET['trade_name'].strip())
if 'member_state' in self.request.GET:
if self.request.GET['member_state']:
queryset =
queryset.filter(member_state__id=int(self.request.GET['member_state']))
if 'pheur_name' in self.request.GET:
if self.request.GET['pheur_name'] != '':
queryset =
queryset.filter(pheur_name__id=self.request.GET['pheur_name'])
if 'decision' in self.request.GET:
if self.request.GET['decision']:
queryset =
queryset.filter(decision__id=int(self.request.GET['decision']))
if 'final_bulk_num' in self.request.GET:
if self.request.GET['final_bulk_num'] != '':
queryset =
queryset.filter(final_bulk__icontains=self.request.GET['final_bulk_num'].strip())
if 'domain' in self.request.GET:
if self.request.GET['domain']:
queryset =
queryset.filter(domain__id=int(self.request.GET['domain']))
if 'manufacturer_name' in self.request.GET:
if self.request.GET['manufacturer_name'] != '':
queryset =
queryset.filter(manufacturer_name__id=self.request.GET['manufacturer_name'])
if 'date_from' in self.request.GET and 'date_to' in
self.request.GET:
if self.request.GET['date_from'] and not
self.request.GET['date_to']:
try:
queryset =
queryset.filter(date_decision__gte=Utils.filter_date_format(self.request.GET['date_from'],
date_format))
except:
pass
if not self.request.GET['date_from'] and
self.request.GET['date_to']:
try:
queryset =
queryset.filter(date_decision__lte=Utils.filter_date_format(self.request.GET['date_to'],
date_format))
except:
pass
if self.request.GET['date_from'] and
self.request.GET['date_to']:
try:
queryset =
queryset.filter(date_decision__range=(Utils.filter_date_format(self.request.GET['date_from'],
date_format), Utils.filter_date_format(self.request.GET['date_to'],
date_format)))
except:
pass
if 'name' in self.request.GET:
if self.request.GET['name'] != '':
queryset =
queryset.filter(name__icontains=self.request.GET['name'].strip())
if 'eu_cert_n' in self.request.GET:
if self.request.GET['eu_cert_n'] != '':
queryset =
queryset.filter(eu_cert_n__icontains=self.request.GET['eu_cert_n'].strip())
if 'status' in self.request.GET:
if self.request.GET['status'] != '':
queryset =
queryset.filter(status_final_product__icontains=self.request.GET['status'].strip())
if 'upstream_code_product' in self.request.GET:
if self.request.GET['upstream_code_product'] != '':
queryset =
queryset.filter(upstream_products__name__icontains=self.request.GET['upstream_code_product'].strip())
if 'certificate_nullified' in self.request.GET:
if self.request.GET['certificate_nullified'] != '' and
self.request.GET['certificate_nullified'] != 'both':
queryset = queryset.filter(
is_withdrawn__icontains='t' if
self.request.GET['certificate_nullified'] == 'True' else 'f')
return queryset
def get_context_data(self, **kwargs):
""" Update the context data with query_string."""
context = super(EdqmListSearchView, self).get_context_data(**kwargs)
context['total_records'] = self.get_total_count()
# Create or Update
if 'certificate_nullified' in self.request.GET:
form = FinalProductSearchForm(data=self.request.GET,
user=self.request.user)
else:
form = FinalProductSearchForm(user=self.request.user,
initial={'certificate_nullified': 'both'})
if not self.page_title:
self.page_title = _("List of ") +
str(self.model._meta.verbose_name_plural)
# Check perms
if 'add' in self.model._meta.default_permissions and not
self.request.user.has_perm(self.model._meta.app_label + '.add_' +
self.model._meta.model_name):
self.create_link = None
if 'change' in self.model._meta.default_permissions and not
self.request.user.has_perm(self.model._meta.app_label + '.change_' +
self.model._meta.model_name):
self.update_link = None
if 'delete' in self.model._meta.default_permissions and not
self.request.user.has_perm(self.model._meta.app_label + '.delete_' +
self.model._meta.model_name):
self.delete_link = None
# Send id to print info or excel.
search_text = ""
already_sorted = False
if 'csrfmiddlewaretoken' in self.request.GET:
# Log search form
Utils.log_search_form(self, 'Final product')
search_form_info = [str(info) + '=' + self.request.GET[info]
for info in self.request.GET if self.request.GET[info] != '' and info !=
'csrfmiddlewaretoken' and self.request.GET[info] != 'both' and info !=
'page']
is_date_to = False
is_date_from = False
for date_info in search_form_info:
if 'date_from' in date_info:
is_date_from = True
if 'date_to' in date_info:
is_date_to = True
is_range_date = True if is_date_to and is_date_from else False
d1 = ''
d2 = ''
for compute_txt in search_form_info:
# Change filter name.
if 'mah' in compute_txt:
compute_txt = compute_txt.replace('mah',
'mah__contains')
elif 'sorting' in compute_txt:
already_sorted = True
elif 'releasing_body' in compute_txt:
compute_txt = compute_txt.replace('releasing_body',
'releasing_body__id')
elif 'trade_name' in compute_txt:
compute_txt = compute_txt.replace('trade_name',
'product_trade_name__icontains')
elif 'member_state' in compute_txt:
compute_txt = compute_txt.replace('member_state',
'member_state__id')
elif 'pheur_name' in compute_txt:
compute_txt = compute_txt.replace('pheur_name',
'pheur_name__id')
elif 'decision' in compute_txt:
compute_txt = compute_txt.replace('decision',
'decision__id')
elif 'final_bulk_num' in compute_txt:
compute_txt = compute_txt.replace('final_bulk_num',
'final_bulk__icontains')
elif 'domain' in compute_txt:
compute_txt = compute_txt.replace('domain',
'domain__id')
elif 'manufacturer_name' in compute_txt:
compute_txt = compute_txt.replace('manufacturer_name',
'manufacturer_name__id')
elif 'name' in compute_txt:
compute_txt = compute_txt.replace('name',
'name__icontains')
elif 'eu_cert_n' in compute_txt:
compute_txt = compute_txt.replace('eu_cert_n',
'eu_cert_n__icontains')
elif 'status' in compute_txt:
compute_txt = compute_txt.replace('status',
'status_final_product__icontains')
elif 'upstream_code_product' in compute_txt:
compute_txt =
compute_txt.replace('upstream_code_product',
'upstream_products__name__icontains')
elif 'certificate_nullified' in compute_txt:
compute_txt =
compute_txt.replace('certificate_nullified', 'is_withdrawn__icontains')
if 'True' in compute_txt:
compute_txt = compute_txt.replace('True', 't')
else:
compute_txt = compute_txt.replace('False', 'f')
date_format = settings.USER_DATE_FORMAT
if not is_range_date:
if 'date_from' in compute_txt and is_date_from and not
is_date_to:
text_to_replace = compute_txt.split('=')[1]
try:
text_to_replace = Utils.change_date_format(
datetime.datetime.strptime(text_to_replace,
date_format),
date_format).replace('/', '-')
except ValueError:
pass
compute_txt =
compute_txt.replace(compute_txt.split('=')[1], text_to_replace)
compute_txt = compute_txt.replace('date_from',
'date_decision__gte')
if 'date_to' in compute_txt and not is_date_from and
is_date_to:
text_to_replace = compute_txt.split('=')[1]
try:
text_to_replace =
Utils.change_date_format(datetime.datetime.strptime(text_to_replace,
date_format), date_format).replace('/','-')
except ValueError:
pass
compute_txt =
compute_txt.replace(compute_txt.split('=')[1], text_to_replace)
compute_txt = compute_txt.replace('date_to',
'date_decision__lte')
else:
if 'date_from' in compute_txt:
text_to_replace = compute_txt.split('=')[1]
try:
text_to_replace = Utils.change_date_format(
datetime.datetime.strptime(text_to_replace,
date_format),
date_format).replace('/', '-')
except ValueError:
pass
d1 =
text_to_replace.replace(compute_txt.split('=')[1], text_to_replace)
continue
if 'date_to' in compute_txt:
text_to_replace = compute_txt.split('=')[1]
try:
text_to_replace =
Utils.change_date_format(datetime.datetime.strptime(text_to_replace,
date_format), date_format).replace('/','-')
except ValueError:
pass
d2 =
text_to_replace.replace(compute_txt.split('=')[1], text_to_replace)
continue
if d1 and d2:
search_text += 'date_decision__gte=' + d1 +
';date_decision__lte=' + d2 + ';'
d1 = ''
d2 = ''
search_text += compute_txt + ';'
if search_text:
search_text = search_text[:-1]
# Sorting patch
if not already_sorted:
if 'sorting' in self.request.GET and search_text == '':
search_text = 'sorting=' + self.request.GET['sorting']
context.update({
'title': self.page_title,
'fields': self.fields,
'labels': self.get_labels(),
'view_link': self.view_link,
'create_link': self.create_link,
'update_link': self.update_link,
'delete_link': self.delete_link,
'search_link': self.search_link,
'export_links': self.export_links,
'model': str(self.model._meta.model_name),
'search_form': form,
'item_count': self.object_list.count(),
'print_link': self.print_link,
'search_info_str': search_text,
'limit_print_search': settings.LIMIT_PRINT_SEARCH
})
if not context.get('is_paginated', False):
return context
# Update paginator range
paginator = context.get('paginator')
num_pages = paginator.num_pages
current_page = context.get('page_obj')
page_no = current_page.number
if num_pages <= 10 or page_no <= 5: # case 1 and 2
pages = [x for x in range(1, min(num_pages + 1, 11))]
elif page_no > num_pages - 4: # case 4
pages = [x for x in range(num_pages - 9, num_pages + 1)]
else: # case 3
pages = [x for x in range(page_no - 5, page_no + 5)]
context.update({'pages': pages})
return context
In my template, I have a function export_xls() called when user press
"Export" button :
class ExportOCABR(View):
def export_xls(self, model="", search_info=""):
app_label = 'ocabr'
its_fp_or_up_product = False
obsolete = False
if self.GET.get('obsolete', '') == 'True':
obsolete = True
default_sorting_key = ''
show_date_format = settings.USER_DATE_FORMAT
if model == "finalproduct" or model == "upstreamproduct":
its_fp_or_up_product = True
default_sorting_key = 'manufacturer_name' if model ==
"finalproduct" else 'releasing_body__short_name'
if model == "releasebodyinstitution":
default_sorting_key = 'name'
model = apps.get_model(app_label=app_label, model_name=model)
# create a workbook in memory
output = io.BytesIO()
book = Workbook(output, {'constant_memory': True})
sheet = book.add_worksheet('Page 1')
# Sheet header, first row
row_num = 0
columns = model.export_field_excel()
rows_width_max = {}
bold_format = book.add_format({'bold': True})
max_col_width = []
for col_num in range(len(columns)):
rows_width_max[col_num] = columns[col_num].__len__()
sheet.write(row_num, col_num, columns[col_num], bold_format)
max_col_width.append(len(columns[col_num]) if
len(columns[col_num]) > 10 else 10)
default_sorting = True
sorting_key = ''
# Define search get all object or sorted value.
if search_info != '':
create_filters = search_info.split(';')
if create_filters.__len__() == 1:
if 'sorting' in create_filters[0]:
default_sorting = False
sorting_key =
create_filters[0].split('=')[1].replace('~', '-')
search_info = ''
else:
for criter in create_filters:
if 'sorting' in criter:
default_sorting = False
sorting_key = criter.split('=')[1].replace('~', '-')
search_info = search_info.replace(criter, "")
search_info = search_info[:-1]
objects = model.objects.all()
if not its_fp_or_up_product:
if obsolete:
objects = objects.obsolete()
else:
objects = objects.active()
if sorting_key:
objects = objects.order_by(sorting_key, 'pk')
if default_sorting:
objects = objects.order_by(default_sorting_key, 'pk')
if search_info != '':
create_filters = search_info.split(';')
for search_filter in create_filters:
search_filter = search_filter.split('=')
try:
if search_filter[1]:
objects = objects.filter(**{search_filter[0]:
search_filter[1]})
except:
# Crud patch search
if search_filter[0] == 'q':
search_info = search_info.replace('q=', '')
objects = objects.filter(get_query(search_info,
model.get_xls_values_list()))
rows = objects.values_list(*model.get_xls_values_list())
# Part which export file directly if less than 70.000 entries else
create file with download link
for row in rows:
row_num += 1
for col_num in range(len(row)):
# Patch True False for boolean field
is_bool = False
if type(row[col_num]) is bool:
is_bool = True
if col_num in model.get_date_field_number():
if row[col_num]:
sheet.write(row_num, col_num,
row[col_num].strftime(show_date_format))
else:
if is_bool:
sheet.write(row_num, col_num, 'True' if
row[col_num] else 'False')
else:
sheet.write(row_num, col_num, row[col_num])
if len(str(row[col_num])) > max_col_width[col_num]:
max_col_width[col_num] = len(str(row[col_num]))
# AutoFit col
for col_num in range(len(columns)):
sheet.set_column(col_num, col_num, max_col_width[col_num] + 1)
book.close()
# construct response
output.seek(0)
name = 'Obsolete' if obsolete else ''
name += str(model._meta.verbose_name_plural)
response = HttpResponse(output.read(),
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = 'attachment; filename="' + name +
'.xlsx"'
return response
The url file looks like :
app_name = 'ocabr'
urlpatterns = [
# Final Product
url(r'^finalproduct/list/$', FinalProductListView.as_view(), name=
'finalproduct-list'),
# Excel export list
url(r'export/xls/(?P<model>[-\w]+)/$', ExportOCABR.export_xls, name=
'export-xls'),
url(r'export/xls/(?P<model>[-\w]+)/(?P<search_info>.*)/$', ExportOCABR.
export_xls, name='export-xls'),
]
*Case 2 : More than 70.000 objects*
I have a tasks.py file :
@shared_task(bind=True, time_limit=3600, soft_time_limit=3600)
def get_xls_export(self, model="", search_info=""):
app_label = 'ocabr'
its_fp_or_up_product = False
obsolete = False
if self.GET.get('obsolete', '') == 'True':
obsolete = True
default_sorting_key = ''
show_date_format = settings.USER_DATE_FORMAT
if model == "finalproduct" or model == "upstreamproduct":
its_fp_or_up_product = True
default_sorting_key = 'manufacturer_name' if model ==
"finalproduct" else 'releasing_body__short_name'
if model == "releasebodyinstitution":
default_sorting_key = 'name'
model = apps.get_model(app_label=app_label, model_name=model)
# create a workbook in memory
output = io.BytesIO()
book = Workbook(output, {'constant_memory': True})
sheet = book.add_worksheet('Page 1')
# Sheet header, first row
row_num = 0
columns = model.export_field_excel()
rows_width_max = {}
bold_format = book.add_format({'bold': True})
max_col_width = []
for col_num in range(len(columns)):
rows_width_max[col_num] = columns[col_num].__len__()
sheet.write(row_num, col_num, columns[col_num], bold_format)
max_col_width.append(len(columns[col_num]) if len(columns[col_num])
> 10 else 10)
default_sorting = True
sorting_key = ''
# Define search get all object or sorted value.
if search_info != '':
create_filters = search_info.split(';')
if create_filters.__len__() == 1:
if 'sorting' in create_filters[0]:
default_sorting = False
sorting_key = create_filters[0].split('=')[1].replace('~',
'-')
search_info = ''
else:
for criter in create_filters:
if 'sorting' in criter:
default_sorting = False
sorting_key = criter.split('=')[1].replace('~', '-')
search_info = search_info.replace(criter, "")
search_info = search_info[:-1]
objects = model.objects.all()
if not its_fp_or_up_product:
if obsolete:
objects = objects.obsolete()
else:
objects = objects.active()
if sorting_key:
objects = objects.order_by(sorting_key, 'pk')
if default_sorting:
objects = objects.order_by(default_sorting_key, 'pk')
if search_info != '':
create_filters = search_info.split(';')
for search_filter in create_filters:
search_filter = search_filter.split('=')
try:
if search_filter[1]:
objects = objects.filter(**{search_filter[0]:
search_filter[1]})
except:
# Crud patch search
if search_filter[0] == 'q':
search_info = search_info.replace('q=', '')
objects = objects.filter(get_query(search_info,
model.get_xls_values_list()))
rows = objects.values_list(*model.get_xls_values_list())
# Part which export file directly if less than 70.000 entries else
create file with download link
for row in rows:
row_num += 1
for col_num in range(len(row)):
# Patch True False for boolean field
is_bool = False
if type(row[col_num]) is bool:
is_bool = True
if col_num in model.get_date_field_number():
if row[col_num]:
sheet.write(row_num, col_num,
row[col_num].strftime(show_date_format))
else:
if is_bool:
sheet.write(row_num, col_num, 'True' if row[col_num]
else 'False')
else:
sheet.write(row_num, col_num, row[col_num])
if len(str(row[col_num])) > max_col_width[col_num]:
max_col_width[col_num] = len(str(row[col_num]))
# AutoFit col
for col_num in range(len(columns)):
sheet.set_column(col_num, col_num, max_col_width[col_num] + 1)
book.close()
output.seek(0)
name = 'Obsolete' if obsolete else ''
name += str(model._meta.verbose_name_plural)
name = name + "_" + str(datetime.now().strftime("%Y_%m_%d_%H_%M_%s")) +
'.xlsx'
default_storage.save(name, output)
# Send mail to django admin
receiver = settings.CRON_RECEIVER_MESSAGE
message = "Hello, \n \n" \
"This is an automatically generated e-mail. \n \n " \
"On " + str(
datetime.now()) + " with export file " + name + " available there :
" + settings.MEDIA_ROOT + "/" + name
try:
send_mail(
'[TEST database] Export file generated by OCABR',
message, 'nore...@test.fr',
receiver)
except ConnectionRefusedError as e:
return ['error_message', _('Error for sending email')]
return ['success_message', _('Generation of export file is done')]
It's similar than *export_xls()* function, but the end is a bit different.
Because I don't create an HttpResponse, but I save the file in MEDIA folder
and send an email to user.
*My question :*
How and where I can create a *get_xls_export.delay()* when user click on
the export button for more than 70.000 objects ? How I can call it from
urls.py file ?
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/da79f356-6164-4c27-bfa2-a918d474a22e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
