def snapshot_query(self, query_id, upload=True): q = models.Query.objects.get(pk=query_id) exporter = exporters.get_exporter_class('csv')(q) safe_title = slugify(q.title) # ll: query1--dummy-query.csv daily_fname = 'query%s--%s.csv' % (q.id, safe_title) # ll: query1--dummy-query--2016-01-31-23-59-59.csv timestamped_fname = 'query%s--%s--%s.csv' % ( q.id, safe_title, date.today().strftime('%Y-%m-%d-%H-%M-%S')) res = exporter.query.execute_query_only() data = exporter._get_output(res) for fname in [daily_fname, timestamped_fname]: if upload and settings.EXPLORER_S3_BUCKET: LOG.info("uploading snapshot: %s", fname) self._upload(fname, data) #self._upload(fname, exporter.get_file_output()) LOG.info("completed upload of snapshot: %s", fname) self.echo('%s uploaded' % fname) else: LOG.warn( "the bucket to upload query result %r hasn't been defined in your app.cfg file. skipping upload" % fname) self.echo(daily_fname)
def send_email(query_id, email_address_list): logger.info("Sending email for query %s..." % query_id) q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) try: subj = 'Report "%s" is ready' % q.title if app_settings.EMAIL_SAVE_TO_S3: url = s3_upload('%s.csv' % random_part, exporter.get_file_output()) msg = 'Download results:\n%s' % url attachment_data = None else: msg = 'Results in attachment:\n' attachment_data = exporter.get_output() except DatabaseError as e: subj = 'Error running report %s' % q.title msg = 'Error: %s\nPlease contact an administrator' % e logger.warning('%s: %s' % (subj, e)) email = EmailMessage(subj, msg, app_settings.FROM_EMAIL, email_address_list) if attachment_data: email.attach( '%s.%s.csv' % (datetime.now().strftime("%Y-%d-%m.%H-%M"), q.title), attachment_data, 'text/csv') email.send()
def snapshot_query(query_id): logger.info("Starting snapshot for query %s..." % query_id) q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) k = 'query-%s.snap-%s.csv' % (q.id, date.today().strftime('%Y%m%d-%H:%M:%S')) logger.info("Uploading snapshot for query %s as %s..." % (query_id, k)) resp = _upload(k, exporter.get_file_output()) logger.info("Done uploading snapshot for query %s. URL: %s" % (query_id, resp.url))
def execute_query(query_id, email_address): q = Query.objects.get(pk=query_id) if app_settings.EMAIL_BASE_TEMPLATE: email_content = get_template(app_settings.EMAIL_BASE_TEMPLATE).render({ 'title': '[SQL Explorer] Sua consulta está rodando...', 'main_content': '%s está rodando e estará em sua caixa de entrada em breve!' % q.title }) else: email_content = '%s está rodando e estará em sua caixa de entrada em breve!' % q.title email = EmailMessage('[SQL Explorer] Sua consulta está rodando...', email_content, app_settings.FROM_EMAIL, [email_address]) email.content_subtype = "html" # O conteúdo principal agora está em text/html email.send() exporter = get_exporter_class('csv')(q) try: output_file = exporter.get_file_output() output_file.seek(0) url = s3_upload('%s.csv' % q.title.replace(' ', '_'), BytesIO(output_file.read().encode('utf-8'))) if app_settings.EMAIL_BASE_TEMPLATE: email_content = get_template( app_settings.EMAIL_BASE_TEMPLATE).render({ 'title': '[SQL Explorer] Relatório "%s" está pronto' % q.title, 'main_content': 'Baixe os resultados:\n\r%s' % url }) else: email_content = 'Baixe os resultados:\n\r%s' % url subj = '[SQL Explorer] Relatório "%s" está pronto' % q.title except DatabaseError as e: if app_settings.EMAIL_BASE_TEMPLATE: email_content = get_template( app_settings.EMAIL_BASE_TEMPLATE).render({ 'title': '[SQL Explorer] Erro ao gerar relatorio %s' % q.title, 'main_content': 'Erro: %s\n Entre em contato com um administrator' % e }) else: email_content = 'Erro: %s\n Entre em contato com um administrator' % e subj = '[SQL Explorer] Erro ao gerar relatorio %s' % q.title logger.warning('%s: %s' % (subj, e)) email = EmailMessage(subj, email_content, app_settings.FROM_EMAIL, [email_address]) email.content_subtype = "html" # O conteúdo principal agora está em text/html email.send()
def snapshot_query(query_id): logger.info("Starting snapshot for query %s..." % query_id) q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) k = 'query-%s.snap-%s.csv' % (q.id, date.today().strftime('%Y%m%d-%H:%M:%S')) logger.info("Uploading snapshot for query %s as %s..." % (query_id, k)) resp_url = _upload(k, exporter.get_file_output()) logger.info("Done uploading snapshot for query %s. URL: %s" % (query_id, resp_url))
def execute_query(query_id, email_address): q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) random_part = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(20)) resp = _upload('%s.csv' % random_part, exporter.get_file_output()) subj = '[SQL Explorer] Report "%s" is ready' % q.title msg = 'Download results:\n\r%s' % resp.url send_mail(subj, msg, app_settings.FROM_EMAIL, [email_address])
def snapshot_query(query_id): try: logger.info("Starting snapshot for query %s..." % query_id) q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) k = 'query-%s/snap-%s.csv' % (q.id, date.today().strftime('%Y%m%d-%H:%M:%S')) logger.info("Uploading snapshot for query %s as %s..." % (query_id, k)) url = s3_upload(k, exporter.get_file_output()) logger.info("Done uploading snapshot for query %s. URL: %s" % (query_id, url)) except Exception as e: logger.warning("Failed to snapshot query %s (%s). Retrying..." % (query_id, e)) snapshot_query.retry()
def snapshot_query(query_id): try: logger.info("Starting snapshot for query %s..." % query_id) q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) k = 'query-%s/snap-%s.csv' % (q.id, date.today().strftime('%Y%m%d-%H:%M:%S')) logger.info("Uploading snapshot for query %s as %s..." % (query_id, k)) url = s3_upload(k, exporter.get_file_output()) logger.info("Done uploading snapshot for query %s. URL: %s" % (query_id, url)) except Exception as e: logger.warning("Failed to snapshot query %s (%s). Retrying..." % (query_id, e.message)) snapshot_query.retry()
def _export(request, query, download=True): format = request.GET.get('format', 'csv') exporter_class = get_exporter_class(format) query.params = url_get_params(request) delim = request.GET.get('delim') exporter = exporter_class(query) output = exporter.get_output(delim=delim) response = HttpResponse(output, content_type=exporter.content_type) if download: response['Content-Disposition'] = 'attachment; filename="%s"' % ( exporter.get_filename()) return response
def execute_query(query_id, email_address): q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) random_part = ''.join( random.choice(string.ascii_uppercase + string.digits) for _ in range(20)) resp_url = _upload('%s.csv' % random_part, exporter.get_file_output()) subj = '[SQL Explorer] Report "%s" is ready' % q.title msg = 'Download results:\n\r%s' % resp_url send_mail(subj, msg, app_settings.FROM_EMAIL, [email_address])
def _export(request, query, download=True): format = request.GET.get('format', 'csv') exporter_class = get_exporter_class(format) query.params = url_get_params(request) delim = request.GET.get('delim') exporter = exporter_class(query) output = exporter.get_output(delim=delim) response = HttpResponse(output, content_type=exporter.content_type) if download: response['Content-Disposition'] = 'attachment; filename="%s"' % ( exporter.get_filename() ) return response
def _export(request, query, download=True): format = request.GET.get('format', 'csv') exporter_class = get_exporter_class(format) query.params = url_get_params(request) delim = request.GET.get('delim') exporter = exporter_class(query) try: output = exporter.get_output(delim=delim) except DatabaseError as e: msg = "Error executing query %s: %s" % (query.title, e) return HttpResponse(msg, status=500) response = HttpResponse(output, content_type=exporter.content_type) if download: response['Content-Disposition'] = 'attachment; filename="%s"' % (exporter.get_filename()) return response
def snapshot_query(query_id): try: logger.info(f"Starting snapshot for query {query_id}...") q = Query.objects.get(pk=query_id) exporter = get_exporter_class('csv')(q) k = 'query-{}/snap-{}.csv'.format( q.id, date.today().strftime('%Y%m%d-%H:%M:%S')) logger.info(f"Uploading snapshot for query {query_id} as {k}...") url = s3_upload(k, exporter.get_file_output()) logger.info( f"Done uploading snapshot for query {query_id}. URL: {url}") except Exception as e: logger.warning( f"Failed to snapshot query {query_id} ({e}). Retrying...") snapshot_query.retry()
def _export(request, query, download=True): format = request.GET.get('format', 'csv') exporter_class = get_exporter_class(format) query.params = url_get_params(request) delim = request.GET.get('delim') exporter = exporter_class(query) try: output = exporter.get_output(delim=delim) except DatabaseError as e: msg = "Error executing query %s: %s" % (query.title, e) return HttpResponse(msg, status=500) response = HttpResponse(output, content_type=exporter.content_type) if download: response['Content-Disposition'] = 'attachment; filename="%s"' % ( exporter.get_filename() ) return response
def execute_query(query_id, email_address): q = Query.objects.get(pk=query_id) send_mail('[SQL Explorer] Your query is running...', '%s is running and should be in your inbox soon!' % q.title, app_settings.FROM_EMAIL, [email_address]) exporter = get_exporter_class('csv')(q) random_part = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(20)) try: url = s3_upload('%s.csv' % random_part, exporter.get_file_output()) subj = '[SQL Explorer] Report "%s" is ready' % q.title msg = 'Download results:\n\r%s' % url except DatabaseError as e: subj = '[SQL Explorer] Error running report %s' % q.title msg = 'Error: %s\nPlease contact an administrator' % e logger.warning('%s: %s' % (subj, e)) send_mail(subj, msg, app_settings.FROM_EMAIL, [email_address])
def execute_query(query_id, email_address): q = Query.objects.get(pk=query_id) send_mail('[SQL Explorer] Your query is running...', f'{q.title} is running and should be in your inbox soon!', app_settings.FROM_EMAIL, [email_address]) exporter = get_exporter_class('csv')(q) random_part = ''.join( random.choice(string.ascii_uppercase + string.digits) for _ in range(20)) try: url = s3_upload(f'{random_part}.csv', exporter.get_file_output()) subj = f'[SQL Explorer] Report "{q.title}" is ready' msg = f'Download results:\n\r{url}' except DatabaseError as e: subj = f'[SQL Explorer] Error running report {q.title}' msg = f'Error: {e}\nPlease contact an administrator' logger.warning(f'{subj}: {e}') send_mail(subj, msg, app_settings.FROM_EMAIL, [email_address])
def snapshot_query_on_bucket(query_id=None, *args, **kwrgs): try: q = Query.objects.get(pk=query_id) q_name = q.slug if q.slug else q.id exporter = get_exporter_class('csv')(q) k = '{}-{}.csv'.format(q_name, date.today().strftime('%Y%m%d')) file_output = exporter.get_file_output(encoding=q.encoding) # sends the file of the query via all the FTP exports for ftp_export in q.ftpexport_set.all(): moni_s3_transfer_file_to_ftp( ftp_export, file_output, k, ftp_export.passive, ) time.sleep(2) if q.bucket != '': moni_s3_upload(k, file_output, q.bucket) except Exception: logger.exception("Failed to snapshot query {}.".format(query_id)) return datetime.now()
def get(self, request, query_id, *args, **kwargs): query = get_object_or_404(Query, pk=query_id) format = request.GET.get("format", "csv") exporter_class = get_exporter_class(format) file_extension = exporter_class.file_extension date = "_" + datetime.datetime.now().strftime("%Y%m%d") response = _export(request, query) response["Content-Disposition"] = (date + file_extension).join( response["Content-Disposition"].split(file_extension)) if file_extension == ".xlsx": wb = load_workbook(filename=BytesIO(response.content)) ws = wb.active dims = {} for row in ws.iter_rows(): for cell in row: dims[cell.column] = max(dims.get(cell.column, 0), len(as_text(cell.value))) for col, value in dims.items(): ws.column_dimensions[col].width = value wb.close() virtual_wb = save_virtual_workbook(wb) response.content = virtual_wb return response