如何在Toad Edge试用版中定时自动执行查询并邮件发送结果？

Hey there! Let's tackle your problem of scheduling a daily 9 AM query in Toad Edge and sending the results to an email, especially since you're using the trial version. Here's what you need to know:

Toad Edge's Automation Designer—the tool that handles scheduled tasks and email alerts—is almost always a paid feature. The trial version usually locks out advanced automation tools to encourage upgrading to a full license. You can confirm this quickly:

• Open Toad Edge and look for the Automation tab in the top navigation bar.
• Try creating a new automation task. If you hit paywalls or grayed-out options, the trial doesn't support this functionality.

If you get lucky and the Automation Designer is accessible, here's how to set up your workflow:

1. Build the Query Task:
   • Open the Automation Designer and start a new workflow.
   • Add a Run SQL step, paste your query, and connect it to your database.
2. Set the Daily Schedule:
   • Add a Schedule trigger to the workflow. Configure it to run every day at 9:00 AM, making sure to select your correct time zone.
3. Add Email Notification:
   • Insert an Email step right after the query runs. Input your SMTP server details, add the recipient's email, and set up the query results to attach as a CSV or Excel file (the tool lets you export results directly here).
4. Activate the Workflow:
   • Double-check all settings, save the workflow, and toggle the schedule to active.

If the trial blocks you from using Automation Designer, don't stress—there are free, reliable alternatives to get the job done:

Suboption A: OS Task Scheduler + Scripting #

You can automate the query and email process with a simple script, then schedule it using your operating system's built-in scheduler:

1. Write a Script to Run the Query and Send Results:
   • Use Python (with database libraries like psycopg2 for PostgreSQL or pyodbc for SQL Server) or PowerShell to handle the workflow. Here's a simplified Python example to get you started:

     import psycopg2
     import pandas as pd
     import smtplib
     from email.mime.multipart import MIMEMultipart
     from email.mime.base import MIMEBase
     from email import encoders
     
     # Connect to your database and run the query
     conn = psycopg2.connect("dbname=your_database user=your_username password=your_password host=your_host")
     query_results = pd.read_sql("YOUR_QUERY_GOES_HERE", conn)
     query_results.to_csv("daily_query_results.csv", index=False)
     conn.close()
     
     # Send the results via email
     email_msg = MIMEMultipart()
     email_msg['From'] = "your_email@example.com"
     email_msg['To'] = "recipient_email@example.com"
     email_msg['Subject'] = "Daily 9 AM Query Results"
     
     # Attach the CSV file
     attachment = MIMEBase('application', 'octet-stream')
     attachment.set_payload(open("daily_query_results.csv", 'rb').read())
     encoders.encode_base64(attachment)
     attachment.add_header('Content-Disposition', 'attachment; filename="daily_query_results.csv"')
     email_msg.attach(attachment)
     
     # Send the email via SMTP
     smtp_server = smtplib.SMTP('smtp.yourprovider.com', 587)
     smtp_server.starttls()
     smtp_server.login("your_email@example.com", "your_email_password")
     smtp_server.sendmail(email_msg['From'], email_msg['To'], email_msg.as_string())
     smtp_server.quit()
     

2. Schedule the Script:
   • Windows: Open Task Scheduler → Create Basic Task. Set the trigger to daily at 9 AM, choose "Start a program" as the action, and point it to your Python/PowerShell script.
   • macOS/Linux: Use cron—run crontab -e in terminal and add a line like 0 9 * * * /usr/bin/python3 /path/to/your/script.py to schedule it daily at 9 AM.

Suboption B: Database Native Scheduling + Email #

Most databases have built-in tools to handle scheduling and email:

• PostgreSQL: Install the pg_cron extension to schedule your query, then use pg_sendmail (or a custom function) to send the results via email.
• MySQL: Enable the Event Scheduler to run your query, export results to a file, then use SENDMAIL or an external script to email the file.
• SQL Server: Set up a SQL Server Agent Job to run the query, then add a step to send the results via Database Mail.

内容的提问来源于stack exchange，提问作者justinme