Python Automated Reports + Email

1Project Setup

# Create project folder and virtual environment
mkdir automated-reports && cd automated-reports
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install pandas python-dotenv schedule

Create a .env file for your credentials:

EMAIL_ADDRESS=your@gmail.com
EMAIL_PASSWORD=your_app_password
RECIPIENT_EMAIL=recipient@example.com

2Create Sample Database

# setup_db.py
import sqlite3
import random
from datetime import datetime, timedelta

conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS sales (
        id INTEGER PRIMARY KEY,
        date TEXT,
        product TEXT,
        amount REAL,
        region TEXT
    )
''')

# Insert sample data
products = ['Widget A', 'Widget B', 'Service X', 'Service Y']
regions = ['North', 'South', 'East', 'West']

for i in range(100):
    date = (datetime.now() - timedelta(days=random.randint(0, 30))).strftime('%Y-%m-%d')
    cursor.execute('''
        INSERT INTO sales (date, product, amount, region)
        VALUES (?, ?, ?, ?)
    ''', (date, random.choice(products), round(random.uniform(100, 1000), 2), random.choice(regions)))

conn.commit()
conn.close()
print("Database created with sample data!")

3Query Data & Generate Report

# report_generator.py
import sqlite3
import pandas as pd
from datetime import datetime

def generate_report():
    conn = sqlite3.connect('sales.db')

    # Query data
    query = '''
        SELECT
            product,
            region,
            COUNT(*) as transactions,
            SUM(amount) as total_sales,
            AVG(amount) as avg_sale
        FROM sales
        WHERE date >= date('now', '-7 days')
        GROUP BY product, region
        ORDER BY total_sales DESC
    '''

    df = pd.read_sql_query(query, conn)
    conn.close()

    # Calculate summary stats
    total_revenue = df['total_sales'].sum()
    top_product = df.groupby('product')['total_sales'].sum().idxmax()
    top_region = df.groupby('region')['total_sales'].sum().idxmax()

    # Format report
    report = f"""
WEEKLY SALES REPORT
Generated: {datetime.now().strftime('%Y-%m-%d %H:%M')}
{'='*50}

SUMMARY
-------
Total Revenue: ${total_revenue:,.2f}
Top Product: {top_product}
Top Region: {top_region}

DETAILED BREAKDOWN
------------------
{df.to_string(index=False)}

{'='*50}
Report generated automatically by Python script.
    """

    return report, df

if __name__ == "__main__":
    report, _ = generate_report()
    print(report)

4Send Email

# email_sender.py
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
import os
from dotenv import load_dotenv

load_dotenv()

def send_report_email(report_text, attachment_df=None):
    sender = os.getenv('EMAIL_ADDRESS')
    password = os.getenv('EMAIL_PASSWORD')
    recipient = os.getenv('RECIPIENT_EMAIL')

    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = recipient
    msg['Subject'] = f'Weekly Sales Report - {datetime.now().strftime("%Y-%m-%d")}'

    # Add text body
    msg.attach(MIMEText(report_text, 'plain'))

    # Optionally attach CSV
    if attachment_df is not None:
        csv_data = attachment_df.to_csv(index=False)
        attachment = MIMEBase('application', 'octet-stream')
        attachment.set_payload(csv_data.encode())
        encoders.encode_base64(attachment)
        attachment.add_header('Content-Disposition', 'attachment', filename='sales_report.csv')
        msg.attach(attachment)

    # Send email
    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
        server.login(sender, password)
        server.send_message(msg)

    print(f"Report sent to {recipient}!")

5Schedule Automation

# main.py
import schedule
import time
from report_generator import generate_report
from email_sender import send_report_email

def job():
    print("Generating report...")
    report, df = generate_report()
    print("Sending email...")
    send_report_email(report, df)
    print("Done!")

# Run every Monday at 9:00 AM
schedule.every().monday.at("09:00").do(job)

# Or run daily at a specific time
# schedule.every().day.at("09:00").do(job)

print("Scheduler started. Press Ctrl+C to exit.")
while True:
    schedule.run_pending()
    time.sleep(60)

6Deploy with Cron

# Open crontab
crontab -e

# Add this line (runs every Monday at 9 AM)
0 9 * * 1 cd /path/to/project && /path/to/venv/bin/python main.py

# Or use a one-shot script without the scheduler
0 9 * * 1 cd /path/to/project && /path/to/venv/bin/python -c "from main import job; job()"