Database Query to Email Report

Connect to database, run a query, export to Excel, and email the report

Steps

#	Activity	What it does
1	OpenDatabaseConnection	Connect to the database
2	RunQuery	Execute report query
3	CreateTable	Create an in-memory table for the query rows
4	ForEachLoop	Push each query row into the table
5	TableUpsertRow	Append the row to the table
6	TableExport	Export the rows to Excel
7	SMTPEmail	Email the report
8	CloseDatabaseConnection	Close the connection

Workflow

dbConn = OpenDatabaseConnection(connectionString="postgresql://user:pass@localhost:5432/mydb", databaseType="postgres")
queryResults = RunQuery(dbConn, "SELECT * FROM sales WHERE date >= CURRENT_DATE - INTERVAL '7 days'")
CreateTable("salesReport", schema={"columns": [{"name": "date"}, {"name": "amount"}, {"name": "customer"}]})
for row in queryResults:
    TableUpsertRow("salesReport", {"date": "{{ row.date }}", "amount": "{{ row.amount }}", "customer": "{{ row.customer }}"}, append=True)
TableExport("salesReport", "./weekly_report.xlsx", format="xlsx", sheetName="Sales")
SMTPEmail("manager@example.com", emailBody="{{ 'Weekly report attached with ' + queryResults.length + ' records.' }}", emailSubject="Weekly Sales Report")
CloseDatabaseConnection(dbConn)