Emailing a Scheduled Excel Report From a Database Job

The report that runs every Monday at seven and lands in a manager's inbox is one of the oldest uses of a database, and it still goes wrong in the same ways. It is sent twice. It is sent empty. It stops one day and nobody notices for a month. The credentials are in the script. This post builds the job with those failures in mind: query, file, checks, delivery, schedule and monitoring. It is a spoke of sending database results to Excel, and it assumes you have a function that writes the workbook, such as the ones in exporting a query to Excel from PostgreSQL with Python.

The shape of the job

A good report job does four things in order, and stops at the first that fails:

1. Query the database with the period computed from the date.

2. Write the workbook to a file with a predictable name.

3. Check that the result is plausible.

4. Deliver by email or to a shared location, and record that it did.

Keep these as separate functions so each can be tested, and see making Excel exports open cleanly every time and building a multi-sheet Excel report from SQL queries for the workbook half. The period should be computed, not typed: for a weekly report, last Monday through Sunday; for a monthly one, the previous calendar month. The same code run on any date then reproduces the same report, which makes reruns safe.

from datetime import date, timedelta

def last_week(today=None):
    today = today or date.today()
    monday = today - timedelta(days=today.weekday())
    return monday - timedelta(days=7), monday       # start inclusive, end exclusive

With today set to Wednesday 2024-03-13, the function returns 2024-03-04 and 2024-03-11, a half-open interval that matches a >= start AND < end filter and avoids the off-by-one that comes from using BETWEEN on timestamps. Pass today explicitly in tests.

Check before you send

An automated report has no human looking at it before it goes out, so the script must. Define what plausible means for this report and test it:

def check(df, previous_rows=None):
    if len(df) == 0:
        raise RuntimeError("report is empty, not sending")
    if previous_rows and len(df) < 0.5 * previous_rows:
        raise RuntimeError(f"{len(df)} rows is under half of last time ({previous_rows})")

Two checks catch most disasters: the result is not empty, and its size is within a sane factor of the previous run. An upstream failure that truncated a table produces a small report, and a small report with a recipient's name on it is how trust is lost. Store the previous count in a table or a small file. On failure, raise, which ends the script with a non-zero exit status, and let the scheduler alert you. Do not send a report that says "no data" unless that is a normal and expected outcome, in which case send a plain message saying so.

Write the file

Name the file by period, so reruns overwrite the same name and recipients can find it later: sales_2024-03-04_to_2024-03-10.xlsx. Write it into a temporary or dedicated folder, not the script's working directory. Use the workbook code from the other articles, with typed cells and formats, and close the writer before attaching the file. A workbook that is still open in a writer is truncated.

Send it

The standard library is enough:

import os
import smtplib
from email.message import EmailMessage

def send_report(path, recipients, subject, body):
    msg = EmailMessage()
    msg["From"] = "[email protected]"
    msg["To"] = ", ".join(recipients)
    msg["Subject"] = subject
    msg.set_content(body)

    with open(path, "rb") as f:
        msg.add_attachment(
            f.read(),
            maintype="application",
            subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename=os.path.basename(path),
        )

    with smtplib.SMTP(os.environ["SMTP_HOST"], 587, timeout=30) as smtp:
        smtp.starttls()
        smtp.login(os.environ["SMTP_USER"], os.environ["SMTP_PASSWORD"])
        smtp.send_message(msg)

EmailMessage builds a correct MIME message, with the body as plain text and the workbook as an attachment whose type is the registered one for XLSX. starttls() upgrades the connection before the password is sent; port 587 is the submission port that expects it, and port 465 expects TLS from the start (use SMTP_SSL). The credentials come from environment variables, which the scheduler sets, so they are not in the script or in version control. Use an account dedicated to sending, with the least privileges the mail provider allows, and prefer your organization's mail relay or a transactional email service over a personal mailbox.

Write a useful body: what the report covers (the period and the zone), how many rows, who owns it, and who to ask. Recipients forward these emails, and the body is the only documentation that travels with the file. Do not put the data itself into the body.

Keep the recipient list in a configuration file or a table, not hard-coded, and do not let a form field flow into it without validation. A report that quietly adds someone to the list is a data leak.

Size, and when to send a link

Mail systems limit message size, commonly between 10 and 25 megabytes, and the attachment is encoded into a larger message (base64 adds about a third). A report that grows past a few megabytes will start bouncing one day. Prevent it with a check in the script:

size = os.path.getsize(path)
if size > 8 * 1024 * 1024:
    raise RuntimeError(f"report is {size} bytes, too large to attach; use the link route")

For large reports, upload the file to a controlled location (a bucket with a time-limited link, an internal file server) and email the link. That also lets you revoke access, which an attachment does not allow. A link means the recipients must be allowed to reach the location, so test it with a real recipient account.

A second reason to avoid attachments: they get forwarded. If the data is sensitive, a link behind authentication is the safer delivery. If an attachment is unavoidable, remember that mail is not a secure channel, and consider whether the file belongs in email at all.

Schedule it

On Linux, cron runs the script at the chosen time. Edit the crontab with crontab -e:

# minute hour day-of-month month day-of-week command
0 7 * * 1  /opt/reports/venv/bin/python /opt/reports/weekly_sales.py >> /var/log/weekly_sales.log 2>&1

That runs every Monday at 07:00 in the server's time zone. Use the full path to the interpreter, since cron has a minimal environment, and redirect output to a log. Cron has no memory of missed runs; if the machine was off at 07:00, the job does not run later. systemd timers can catch up missed runs with Persistent=true. Be careful with time zones and daylight saving: a job at 02:30 may not run, or may run twice, on the nights the clocks change. Pick a time that avoids them.

On Windows, Task Scheduler runs a script under a service account; on SQL Server, SQL Agent can run a job step that calls the script or a PowerShell command; in a data platform, the orchestrator's scheduler does the same with retries and alerts built in. The mechanism matters less than the discipline: the job runs under a dedicated account, with its environment defined in one place, and its output is captured.

Make a rerun safe

Retries and manual reruns will happen. Decide what a second run does. If it sends the same email again, recipients get duplicates. Record each delivery in a table keyed by report and period:

CREATE TABLE report_runs (
    report     text        NOT NULL,
    period     text        NOT NULL,
    rows_sent  integer     NOT NULL,
    sent_at    timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (report, period)
);

Insert the row after a successful send and check for it at the start. A rerun for a period already sent exits quietly, unless a --force flag says otherwise. The same table is the source of the "previous row count" used in the checks, and the history for the question "did the report go out in March?".

Monitor the monitor

The hard failure is the silent one: the job did not run at all. A failing script that emails an alert is fine; a script that never started sends nothing. Two defenses help. First, a heartbeat: a check (the scheduler's own, or a dead man's switch service) that expects a ping after each successful run and alerts when it does not arrive. Second, recipients as monitors: add an owner to the recipient list, and tell the recipients to say if the report is late. Log enough to debug without leaking data: the period, the row count, the file size, the duration and the outcome, and not the contents.

Alert on failure with a short message to a channel or an email address that is not the report's own recipients: what failed, when, and the first lines of the error.

When emailing an Excel report is the wrong delivery

The report is sensitive. An attachment leaves your control. Use an authenticated link, or a portal.

The audience wants current numbers. A dashboard or a live query beats a weekly snapshot, and ends the question of which version is right.

The recipients are many. Mass mailing needs a mail service with unsubscribe and deliverability handling. A list of 500 employees is a distribution list, and a list of 50,000 customers is a different problem.

The data is big. Beyond a few megabytes, send a link or reduce the report to an aggregate.

Someone needs to act on it. If the report triggers work, a ticket or a task is better than an attachment that someone must remember to open.

The mail calls are documented in the Python pages for email.message and smtplib, and the schedule syntax in the crontab(5) manual page. To check a small sample of the report as JSON, use the CSV to JSON converter.