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...',
'%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
