Python Scheduled Reports: Automate Data Generation and Email Delivery

Every Monday morning, someone on your team opens a spreadsheet, copies numbers into a template, formats a few charts, and emails the result to stakeholders. It takes forty-five minutes. It happens every week. The task is boring, error-prone, and perfectly suited for automation.

Python has all the pieces you need to eliminate this kind of repetitive work. A script can pull data from a database or API, process it into a readable report, and send it via email on a schedule. The entire workflow runs without human involvement once it is set up. Here is how to build it, step by step, with code you can adapt to your own data sources and reporting needs.

The four components

A scheduled report system has four parts: data extraction, report generation, email delivery, and scheduling. Each part has well-established Python libraries, and none of them are complicated on their own. The value comes from connecting them into a reliable pipeline.

Data extraction uses whatever source your data lives in. For SQL databases, use sqlite3 for local databases or psycopg2 for PostgreSQL. For APIs, use requests. For spreadsheets, use openpyxl or pandas with read_excel. The extraction step should return clean, structured data that the report generator can work with directly. Keep this function isolated from the rest of the system so you can test it independently and swap data sources without rewriting the report logic.

Report generation converts raw data into a readable format. For HTML emails, use Jinja2 templates. For PDF reports, use WeasyPrint or ReportLab. For CSV files, the built-in csv module is sufficient. The key decision is the output format, which depends on your audience. HTML works best for email delivery because most email clients render it reliably. PDF works best for archival and printing. CSV works best when the report feeds into another system for further processing.

Email delivery uses Python’s smtplib for direct SMTP sending, or a service API like SendGrid or Mailgun for higher deliverability. For internal reports, your company’s SMTP server is usually sufficient. For external distribution, a dedicated email service reduces the chance of messages landing in spam folders. The tradeoff is complexity versus reliability, and for most internal use cases, direct SMTP is the simpler choice.

Scheduling triggers the pipeline at the right time. On Linux, use cron. On Windows, use Task Scheduler. For cross-platform solutions or cloud deployment, use APScheduler or the schedule library. For containerized environments, Kubernetes CronJobs or cloud scheduler services work well. The scheduling layer should be the thinnest part of the system, doing nothing more than calling your main function at the specified interval.

Building the data extractor

The data extractor should be a standalone function that returns a dictionary or list of dictionaries. Keeping it separate from the report generator means you can test and reuse it independently. If you later need to generate a different report from the same data, you call the same extractor without modification.

For database sources, the pattern is straightforward. Connect to the database, run a query that aggregates the data you need, and return the results as a Python dictionary. The function should handle connection errors gracefully and close the connection in all cases, whether the query succeeds or fails.

For API-based data extraction, use requests with proper timeout handling and error checking. APIs can be slow, rate-limited, or temporarily unavailable. Your extractor should retry on transient failures and raise clear exceptions on permanent ones. The tenacity library makes retry logic straightforward without cluttering your code with nested try-except blocks.

The important design principle is that the extractor returns data in a format the report generator expects. Define that contract clearly, document it, and do not change it without updating both sides. This is where most report automation projects fail, not in the individual components but in the interface between them.

Building the report generator

Jinja2 templates are the most flexible option for HTML reports. They let you write the report layout in HTML with placeholders for data, the same way you build a web page. The template handles structure and formatting. The Python code handles data preparation and rendering.

Create a template file with HTML markup and Jinja2 variables for the data fields. Use filters for formatting numbers, dates, and conditional content. Keep the template simple. If your report needs complex logic, compute it in Python before passing data to the template, not in the template itself.

For PDF output, render the HTML first and then convert it with WeasyPrint. This two-step approach gives you a single template that works for both HTML emails and PDF attachments. The HTML template is the source of truth for the report layout. The PDF conversion is just a different rendering target.

Charts and graphs add visual value to reports. matplotlib can generate chart images that you embed in HTML emails or include in PDFs. For interactive reports, plotly generates HTML files with interactive charts. The choice depends on whether your audience needs static images or interactive exploration.

Building the email sender

Python’s built-in smtplib handles SMTP connections directly. For HTML emails, construct a MIMEText message with the HTML content type. Add attachments using MIMEBase for PDF reports or CSV files. The email should have a clear subject line that includes the report date, so recipients can find it later.

Store credentials in environment variables or a secrets manager, not in the script. The python-dotenv library can load .env files during development, but production deployments should use proper secret management. Hardcoded passwords in automation scripts are a security risk that grows as the script gets copied and shared.

For higher deliverability, consider using a transactional email service instead of direct SMTP. SendGrid, Mailgun, and Postmark provide APIs that handle deliverability, bounce management, and open tracking. The tradeoff is an additional dependency and usually a monthly cost, but for reports that stakeholders depend on, the reliability is worth it.

Test email delivery thoroughly before relying on it in production. Send test messages to yourself, to colleagues, and to external addresses. Check that HTML renders correctly in different email clients. Verify that attachments open properly. Email rendering is inconsistent across clients, and a report that looks perfect in Gmail might look broken in Outlook.

Putting it together

The main script ties all four components into a single pipeline. The function should be idempotent, meaning running it twice produces the same result as running it once. This matters because schedulers sometimes trigger jobs twice, and you do not want duplicate emails.

Add logging throughout the pipeline. Log when extraction starts and finishes, when the report is generated, and when the email is sent. Use Python’s logging module with timestamps and severity levels. The logs are your debugging tool when something goes wrong at three in the morning and you are checking why the report did not arrive.

Handle failures at each stage. If data extraction fails, do not generate a report from stale data. If report generation fails, do not send an empty email. If email delivery fails, log the error and optionally retry. The pipeline should fail loudly enough that you notice, but not so loudly that it crashes the scheduler.

Scheduling the pipeline

On Linux, add a cron entry to run the script at the desired interval. The cron syntax is compact but confusing at first. The line “0 8 * * 1” means “at minute 0 of hour 8, every day of the month, every month, on day 1 of the week,” which is Monday at 8am. Test your cron entries by running the script manually first and verifying the output before relying on the scheduler.

For cross-platform scheduling without system-level cron, the schedule library works well for simple cases. It runs as a persistent Python process and checks for due jobs every minute. The downside is that you need to keep the process running, which means using a process manager like systemd or supervisord to restart it if it crashes.

For cloud deployments, use the scheduler service provided by your platform. AWS has Event Scheduler, Google Cloud has Cloud Scheduler, and Azure has Logic Apps. These services handle process management, retry logic, and logging automatically, at the cost of vendor lock-in and usually a small monthly fee.

Error handling and reliability

A report pipeline that runs unattended needs to handle failures gracefully. The most common failure points are database connections timing out, API rate limits being hit, and SMTP servers rejecting connections. Each of these is transient and usually resolves itself if you wait and retry.

Add retry logic with exponential backoff. Start with a four-second wait, double it on each retry, and cap it at sixty seconds. Three retries is usually enough for transient failures. If the third attempt fails, log the error and send yourself an alert so you can investigate manually.

The alert mechanism is important. Without it, a failed pipeline stays silent until someone notices the report is missing, which might be days later. Send a brief alert email or Slack message when the pipeline fails. It does not need to be detailed. “Report pipeline failed at 8:03am: database connection timed out” is enough to trigger investigation.

Monitor the pipeline over time. Track how often it runs successfully, how often it fails, and what the common failure modes are. Most report pipelines fail for the same three or four reasons repeatedly. Once you identify the patterns, you can add specific handling for each one.

Testing the pipeline

Test each component independently before integrating them. Write unit tests for the data extractor with mock database connections or test fixtures. Test the report generator with sample data and verify the output matches expected content. Test the email sender with a test SMTP server or a service like Mailtrap that captures emails without delivering them.

Integration tests should run the full pipeline against a staging database and send test emails to a controlled inbox. Run these tests before deploying changes to the production pipeline. The most common source of pipeline failures is not individual component bugs but integration issues, like a data field name changing in the database schema without the extractor being updated.

Keep your test data current. As your database grows and your report requirements change, update the test fixtures to match. A test that passes with six-month-old sample data does not guarantee the pipeline works with current data.

What to automate next

Once the basic pipeline works reliably, extend it with conditional logic. Send different report formats based on the data volume. Add charts using matplotlib and embed them in the HTML. Include trend analysis that compares this week to the previous week. Add a preview mode that sends the report to you first so you can review it before the scheduled delivery to stakeholders.

The goal is not to build a perfect reporting system on the first attempt. It is to eliminate the forty-five minutes of manual work that happens every week. Start with the simplest version that works, run it for a few weeks, and iterate based on what you actually need rather than what you imagine you might need someday.