Build an Automated Daily Report System with DuckDB

In the data analysis industry, a valuable skill is turning repetitive labor into automated pipelines. Today we’ll break down a complete implementation: building an automated daily report system with DuckDB + Python that queries business databases, generates charts, and pushes reports on a schedule.

The Business Opportunity

Many small and medium businesses need to track daily sales, traffic, and conversion metrics. Currently, either the boss blindly reads Excel spreadsheets, or they hire an assistant to manually export data. Your opportunity: build them a fully automated system that produces reports daily and pushes them to WeChat, email, or Telegram.

Traditional Approach vs. DuckDB Approach

Traditional Approach:

• Export data to local Excel
• Process with Excel or hand-written VBA
• Takes hours, prone to errors
• Requires manual intervention every time

DuckDB Approach:

• Connect directly to databases, compute in place
• Python script handles scheduling automatically
• Aggregate millions of rows in 1-2 seconds
• Zero manual intervention, runs 24/7

The core advantage is in-place computation — DuckDB can connect directly to MySQL, PostgreSQL, SQLite and other data sources without moving data locally. Millions of rows aggregate in 1-2 seconds in memory.

System Architecture

┌─────────────┐ ┌──────────────┐ ┌─────────────┐
│ MySQL/PG │────▶│ DuckDB │────▶│ Python │
│ Data Source│ │ Compute │ │ Schedule & │
└─────────────┘ └──────────────┘ │ Push │
 └──────┬──────┘
 │
 ┌───────▼───────┐
 │ Email/Telegram│
 └───────────────┘

Core architecture:

• Data Source: MySQL / PostgreSQL / CSV files
• Compute Engine: DuckDB (seconds for millions of rows)
• Scheduler: Python schedule library
• Output: Markdown + charts, pushed to Telegram groups or email

Step 1: Connect to Data Source and Aggregate with DuckDB

Assume you’ve connected to an e-commerce order table with about 500,000 rows. Using DuckDB for daily aggregation requires just a few lines of code:

import duckdb
from datetime import datetime, timedelta

# Connect directly to MySQL, no data export needed
con = duckdb.connect('mysql://user:***@host:3306/shop', read_only=True)

# 7-day summary analysis
daily_report = con.execute("""
 SELECT 
 DATE(order_time) AS dt,
 COUNT(*) AS orders,
 SUM(amount) AS revenue,
 AVG(amount) AS avg_order_value,
 COUNT(DISTINCT user_id) AS active_users
 FROM orders
 WHERE order_time >= CURRENT_DATE - INTERVAL '7 days'
 GROUP BY DATE(order_time)
 ORDER BY dt DESC
""").fetchdf()

print(daily_report)

Key Tip: DuckDB natively supports connections to multiple databases with a unified connection string format. MySQL requires the duckdb-mysql extension, PostgreSQL similarly. For smaller datasets, you can also read CSV files directly: duckdb.read_csv('orders.csv').

Sample output:

 dt orders revenue avg_order_value active_users
0 2026-08-01 1250 89500.00 71.60 890
1 2026-07-31 1180 82300.00 69.75 845
2 2026-07-30 1320 95600.00 72.42 920
...

Step 2: Generate Visual Charts

With the data ready, use Matplotlib to generate charts:

import matplotlib.pyplot as plt
import matplotlib
matplotlib.use('Agg') # Headless mode, server-friendly

fig, axes = plt.subplots(1, 2, figsize=(12, 5))

# Left: 7-day revenue trend
axes[0].plot(daily_report['dt'], daily_report['revenue'], 
 marker='o', color='#2E86AB', linewidth=2)
axes[0].fill_between(daily_report['dt'], daily_report['revenue'], 
 alpha=0.2, color='#2E86AB')
axes[0].set_title('7-Day Revenue Trend')
axes[0].set_xlabel('Date')
axes[0].set_ylabel('Revenue (¥)')
axes[0].tick_params(axis='x', rotation=45)

# Right: Orders vs Avg Order Value scatter (bubble size = active users)
scatter = axes[1].scatter(
 daily_report['orders'], 
 daily_report['avg_order_value'], 
 s=daily_report['active_users'] * 2, 
 alpha=0.6, 
 c=daily_report['revenue'],
 cmap='viridis'
)
axes[1].set_title('Orders vs Avg Order Value')
axes[1].set_xlabel('Order Count')
axes[1].set_ylabel('Avg Order Value')
plt.colorbar(scatter, ax=axes[1], label='Revenue')

plt.tight_layout()
plt.savefig('/tmp/daily_report.png', dpi=120, bbox_inches='tight')

Step 3: Format the Report and Push

from datetime import datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

def format_report(df):
 """Format DataFrame into a readable daily report"""
 today = df.iloc[0]
 yesterday = df.iloc[1] if len(df) > 1 else None
 
 report = f"""📊 Daily Sales Report · {today['dt'].strftime('%Y-%m-%d')}

Revenue: ¥{today['revenue']:,.2f}
Orders: {int(today['orders'])}
Avg Order Value: ¥{today['avg_order_value']:.2f}
Active Users: {int(today['active_users'])}

"""
 if yesterday is not None:
 rev_change = (today['revenue'] - yesterday['revenue']) / yesterday['revenue'] * 100
 sign = '📈' if rev_change > 0 else '📉'
 report += f"⚡ vs Yesterday: {sign} {abs(rev_change):.1f}%"
 
 return report

def send_report(report_text, image_path):
 """Send email (can be replaced with Telegram Bot API)"""
 msg = MIMEMultipart()
 msg['Subject'] = f"Daily Report · {datetime.now().strftime('%m-%d')}"
 msg.attach(MIMEText(report_text, 'plain', 'utf-8'))
 
 with open(image_path, 'rb') as f:
 from email.mime.image import MIMEImage
 img = MIMEImage(f.read())
 img.add_header('Content-ID', '<report>')
 msg.attach(img)
 
 # SMTP sending logic omitted, replace with your own config
 print(f"Report ready → {image_path}")
 print(report_text)

# Execute
report_text = format_report(daily_report)
send_report(report_text, '/tmp/daily_report.png')

Replace with Telegram Bot Push

If email feels too slow, use the Telegram Bot API instead:

import requests

def send_telegram(chat_id, text, photo_path=None):
 bot_token = "YOUR_BOT_TOKEN"
 url = f"https://api.telegram.org/bot{bot_token}"
 
 # Send text
 requests.post(f"{url}/sendMessage", json={
 "chat_id": chat_id,
 "text": text,
 "parse_mode": "HTML"
 })
 
 # Send photo
 if photo_path:
 with open(photo_path, 'rb') as f:
 requests.post(f"{url}/sendPhoto", json={"chat_id": chat_id}, files={"photo": f})

Step 4: Scheduled Execution + Multi-Client Isolation

import schedule
import time

def generate_daily_report(client_id):
 """Generate daily report for a single client"""
 con = duckdb.connect(f'mysql://user:***@host:3306/{client_id}', read_only=True)
 # ... execute query, generate charts, send report ...
 con.close()

# Auto-generate at 9:00 AM daily
schedule.every().day.at("09:00").do(generate_daily_report, client_id="client_001")
schedule.every().day.at("09:05").do(generate_daily_report, client_id="client_002")
schedule.every().day.at("09:10").do(generate_daily_report, client_id="client_003")

# Poll every 60 seconds, low overhead
while True:
 schedule.run_pending()
 time.sleep(60)

Each client gets an independent DuckDB connection config. Report content is filtered by client ID. Code reusability is extremely high.

DuckDB vs Traditional Approaches

Technical Notes: Design Trade-offs

Customer Acquisition Channels

If you plan to offer this as a service, typical channels include Xianyu/Taobao listings, tech communities (V2EX, Jike), free trial projects, and industry groups. This section is descriptive, not a business guarantee.

Cost Analysis

• Server: A 1-core 1GB cloud instance is enough, or run it on a local machine
• Development Time: 2-3 days for the initial build, 1-2 hours per new configuration
• Maintenance Time: < 30 minutes per month

Note: This article describes a technical implementation. Any prices or cost figures mentioned are illustrative examples for teaching purposes, not statements of actual market rates, income levels, or commercial commitments. Actual outcomes depend on your specific situation.

Advanced Directions

• Multi-source Fusion: Use DuckDB’s mysql_scanner() and postgres_scanner() to pull ERP and CRM data simultaneously for customer LTV analysis
• Auto Anomaly Detection: Use DuckDB’s built-in statistical functions for threshold alerts — notify the team automatically when a metric drops unexpectedly
• PDF Export: Use reportlab to convert reports to formal PDFs for archiving and distribution
• Web Dashboard: Use Streamlit to build a visual dashboard where clients can query data themselves
• Docker Deployment: Package the entire system into a container for one-click deployment to client servers

Complete Code Repository

This project is open source, including:

• Multi-data source adapters (MySQL/PostgreSQL/CSV)
• Daily/weekly/monthly report templates
• Telegram Bot push integration
• Docker deployment configuration
• Multi-client isolation patterns

Visit duckdblab.org for the complete code and technical documentation.

💡 ** Summary**: The core idea of this project is “trade technology for time.” The key is finding teams that have data but lack the tooling to use it — they’re willing to pay for “peace of mind.” Any pricing you set should reflect your own costs and the actual value delivered.

📖 The full version of this article (including Docker deployment, Telegram Bot push, multi-client templates) is published at duckdblab.org. Ready to use straight away.

🔍 Want to systematically learn DuckDB? Complete tutorial series available at duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 admin@duckdblab.org 反馈。