How to Automate Sending SQL Query Results to Email

TL;DR

This blog discusses three methods to automate SQL query results to email:

Method #1: Manually through Custom Python Script:

1. Establish a database connection using pyodbc.
2. Execute the SQL query and send email using smtplib.
3. Schedule the script with Cron Jobs for automated execution.

Method #2: Using SQL Server Database Mail:

1. Configure Database Mail and SMTP account in SQL Server Management Studio.
2. Prepare query-ready data in SQL Server.
3. Utilize sp_send_dbmail stored procedure to send results via email.
4. Automate with SQL Agent job for scheduled execution.

Method #3: Automating SQL Query results through Locale:

1. Connect your database to Locale.
2. Create and run SQL queries to monitor data.
3. Set up schedules which will send email.

While the manual Python script and SQL Server Database Mail methods offer solutions, they come with complexities and technical dependencies. However, Locale.ai provides a streamlined approach, users can easily automate sending SQL query results to email, along with options for integration with other platforms for further flexibility.

Real-time notifications and updates are crucial. Timely access to SQL query results can empower teams to make informed decisions swiftly. SQL query triggers provide a way to initiate actions when specific changes occur in a database. There can be a variety of use cases that can be solved by automating sending SQL query results to email such as:

• Real-time alerts for critical database events such as sudden spikes in traffic or potential security breaches
• Automate sending daily/weekly sales reports to your sales team
• Automated inventory level report to biz ops teams

…and many more. However, Setting up the automation to send results can be a daunting task. In this detailed guide, we’ll walk you through three methods to automate sending SQL query results to email.

• Method #1: Manually through Custom Python Script
• Method #2: Using SQL Server Database Mail
• Method #3: Automating SQL Query results through Locale‍

Method #1: Manually through Custom Python Script

Python offers libraries like smtplib and pandas to execute SQL queries and send the results via email. Here’s a step-by-step guide:‍

Database Connection

import pyodbc
import panda as pd

#connect your database

connection_string = "Driver={SQL Server}; Server=your_server; Database=your_db; UID=your_username; PWD=your_password;"
cnxn = pyodbc.connect(connection_string)

Execute Query

Execute the desired SQL query and save the results in a DataFrame.

query = "SELECT * FROM your_table;"
df = pd.read_sql(query, cnxn)

Format and Save Results

Convert the DataFrame to a CSV file or any other preferred format.

csv_content = df.to_csv(index=False)

Send Email

Use Python’s smtplib to send the email with the attached query results.

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

# Setup the MIME
message = MIMEMultipart()
message["Subject"] = "SQL Query Results"
message["From"] = "you@example.com"
message["To"] = "recipient@example.com"

# Attach the CSV content
part = MIMEText(csv_content, "csv")
message.attach(part)

# Send the email
server = smtplib.SMTP('smtp.example.com', 587)
server.starttls()
server.login("you@example.com", "your_password")
server.sendmail("you@example.com", "recipient@example.com", message.as_string())
server.quit()

Save your Python code (including the database connection, query execution, result formatting, and email sending parts) as a standalone script, e.g., send_sql_results.py.

Test the Script Manually

Before scheduling, ensure your script works correctly when run manually.

Schedule the Script in Crontab

Open a terminal or command line interface. Enter crontab -e to edit the crontab file. This opens the crontab file in a text editor. In the crontab file, add a line that specifies when and how often you want the script to run. The format is:Here, each asterisk represents a time unit: minute, hour, day of the month, month, and day of the week, respectively. For example, to run the script every day at 9 AM, you would use:

0 9 * * * /path/to/python /path/to/send_sql_results.py

Ensure /path/to/python is the full path to the Python interpreter and /path/to/send_sql_results.py is the full path to your Python script. Run crontab -l to list all cron jobs and verify that your new job is correctly listed.

Note: The cron utility is typically available on Linux and Unix-like systems. If you are using a Windows server, you would use the Task Scheduler instead. Also, remember that the environment in which cron jobs run is usually different from your regular user environment, so ensure that all necessary environment variables and paths are correctly set in the script or within the crontab.‍

Limitations

• Maintenance: Scripts require regular updates, especially when database schemas change.
• Complexity: The code becomes unwieldy with increased error handling and multiple query formats.
• Dependency: The solution depends on Python runtime and library availability.‍

Method #2: Using SQL Server Database Mail

SQL Server users can utilize Database Mail to send emails. The procedure involves:‍

Configure Database Mail

Set up a profile and an SMTP account using SQL Server Management Studio.

‍

Prepare Data

Ensure the data you intend to email is query-ready within SQL Server.‍

Use Stored Procedure

Execute the sp_send_dbmail stored procedure to send the results.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourMailProfile',
@recipients = 'recipient@example.com',
@query = 'SELECT * FROM your_table',
@subject = 'Daily Sales Report',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'SalesReport.txt',
@body = 'The daily sales report is attached.',
@body_format = 'HTML';  -- or 'TEXT'

Automate with SQL Agent

Create an SQL Agent job to send emails at scheduled intervals.‍

Limitations

• Platform-Specific: This method is exclusive to SQL Server and doesn’t apply to other database systems.
• Initial Setup: The initial configuration of Database Mail and SQL Agent can be complex.
• Inflexibility: Adapting to changes in data or email format requires additional SQL coding.‍

Method #3: Automating SQL Query results through Locale

Locale, no-code platform handles the heavy lifting, providing a maintenance-free, reliable, and user-friendly experience. With just a few clicks, you can automate sending SQL query results to email.‍

Step 1: Log in to Locale, Go to Org Setting, and Connect your database from the data source.‍

Step 2: Now go to Alerts → Create New Alerts. Choose your database → Click on Setup data to be monitored.

‍Step 3: Write your own SQL Query and click Run to verify the data. Once you are satisfied, click Done.

‍Step 4: Now Set Alert Frequency, this will automatically run the query and send an email based on the frequency you set.

‍Step 5: Final Step, you can set up whom the email should be sent. You can click on Add Notification/action from the Setup Notification/Action section. Now click on Email Subscribers and add all the email addresses to whom you want to send emails.

💡 You can also dynamically send emails based on query results by selecting from { } icon

Save the alert and it will automatically run itself and send emails.

Conclusion

This blog has outlined three distinct methods to automate the process of sending SQL query results to email.

The first method can help you set up regular email reports form SQL query through manually setting up custom Python script then automating it through Cron Jobs. Though this is a workable solution it can be super complex to set up and manage. It can also be prone to error and error handling and resolution in a complex script can be very tedious. The second Method which alerts through SQL Server agent is depended & platform-specific to SQL Server and doesn’t apply to other database systems.

If you want to say away from complex set up and mangement, Locale got you covered. Locale streamlines the process of sending SQL query results to email with minimal effort. By seamlessly connecting to the database, running queries, and setting up automated alerts, Locale eliminates the need for extensive coding and ongoing maintenance. Moreover, if you want to expand sending reports/data to other destinations like Slack, WhatsApp, MS Teams etc, you get to do that with 100+ integration options.

‍

Excited to get started? It takes less than 10 minutes to get all things set up: Try for free