Skip to content

Instantly share code, notes, and snippets.

@jamescalam
Last active May 30, 2021 00:52
Show Gist options
  • Save jamescalam/08fcc1f05640d046d084d1c0e8605666 to your computer and use it in GitHub Desktop.
Save jamescalam/08fcc1f05640d046d084d1c0e8605666 to your computer and use it in GitHub Desktop.
Extracting data example from SQL Server using Python's pyodbc module.
# imports for SQL data part
import pyodbc
from datetime import datetime, timedelta
import pandas as pd
# imports for sending email
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import smtplib
date = datetime.today() - timedelta(days=7) # get the date 7 days ago
date = date.strftime("%Y-%m-%d") # convert to format yyyy-mm-dd
cnxn = pyodbc.connect(cnxn_str) # initialise connection (assume we have already defined cnxn_str)
# build up our query string
query = ("SELECT * FROM customers "
f"WHERE joinDate > '{date}'")
# execute the query and read to a dataframe in Python
data = pd.read_sql(query, cnxn)
del cnxn # close the connection
# make a few calculations
mean_payment = data['payment'].mean()
std_payment = data['payment'].std()
# get max payment and product details
max_vals = data[['product', 'payment']].sort_values(by=['payment'], ascending=False).iloc[0]
# write an email message
txt = (f"Customer reporting for period {date} - {datetime.today().strftime('%Y-%m-%d')}.\n\n"
f"Mean payment amounts received: {mean_payment}\n"
f"Standard deviation of payment amounts: {std_payments}\n"
f"Highest payment amount of {max_vals['payment']} "
f"received from {max_vals['product']} product.")
# we will built the message using the email library and send using smtplib
msg = MIMEMultipart()
msg['Subject'] = "Automated customer report" # set email subject
msg.attach(MIMEText(txt)) # add text contents
# we will send via outlook, first we initialise connection to mail server
smtp = smtplib.SMTP('smtp-mail.outlook.com', '587')
smtp.ehlo() # say hello to the server
smtp.starttls() # we will communicate using TLS encryption
# login to outlook server, using generic email and password
smtp.login('joebloggs@outlook.com', 'Password123')
# send email to our boss
smtp.sendmail('joebloggs@outlook.com', 'joebloggsboss@outlook.com', msg.as_string())
# finally, disconnect from the mail server
smtp.quit()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment