Prototype python script generate and email a report: query a database, generate a CSV file, and email the CSV file

email-query-results-as-csv.py

# Prototype generate a CSV report file and send an eml file using python
# Prerequisites
# 
# Pyodbc
# https://github.com/mkleehammer/pyodbc
# pip install pyodbc
#
# This example uses Microsoft SQL Server's pubs sample database 
# https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs


import csv
import os
import pyodbc
import smtplib
import uuid
from email import encoders
from email import generator
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from datetime import datetime



# smtp server
smtpServer = # enter smtp server name

# smtp username
smtpUsername = # enter smtp username

# smtp password
smtpPassword = # enter smtp password

# smtp port number
smtpPortNumber = # enter smtp port 

# email body
body = "See attached report."

# email subject
subject = "CSV Report"

# send report to
toAddress = # enter send to email address

# send report from 
fromAddress =  # enter send from email address

# where to write the output file
directory = "C:\\Users\\Jeremy\\Documents\\python\\email-csv-prototype\\temp"

# will write files using this value plus a date/time to create unique file names
filenameBase = "report"

# ODBC connection string
connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=.;Database=pubs;Trusted_Connection=yes;"

# SQL select statement
# will include field names in first row of csv file so friendly column name aliases are specified in the SQL statement
sqlStatement = """
SELECT
	emp_id AS [Employee ID]
	, lname AS [Last Name]
	, fname AS [First Name]
FROM dbo.employee
ORDER BY emp_id;
"""

def create_filename(extension):
    now = datetime.now()
    fname = filenameBase + "_" + now.strftime("%Y-%m-%d_%H%M%S%f") + "." + extension
    return fname



def get_records(addHeaderRow=True):
    conn = pyodbc.connect(connectionString)
    cursor = conn.cursor()
    cursor.execute(sqlStatement)
    records = cursor.fetchall()

    if(addHeaderRow):
        columns = [column[0] for column in cursor.description]
        records.insert(0, columns)

    return records

 

def create_directory_structure():
    # create .\temp if not exists
 
    if not os.path.exists(directory):
        os.mkdir(directory)



def write_to_csv(records, filename):
    os.chdir(directory)

    with open(filename, 'w', newline='') as file:
        writer = csv.writer(file)
        writer.writerows(records)

    return os.path.abspath(filename)



def create_message(fileToAttach):
    msg = MIMEMultipart()
    msg["To"] = toAddress
    msg["From"] = fromAddress
    msg["Subject"] = subject
    msg.attach(MIMEText(body, "plain"))

    # attach file
    part = MIMEBase("text", "csv")
    with open(fileToAttach, 'rb') as file:
            part.set_payload(file.read())
    encoders.encode_base64(part)
    part.add_header("Content-Disposition", 'attachment; filename="{}"'.format(os.path.basename(fileToAttach)))
    msg.attach(part)


    return msg


 

def sendEmail(msg):
    smtpConnection = smtplib.SMTP(host=smtpServer, port=smtpPortNumber)
    smtpConnection.login(smtpUsername, smtpPassword)
    smtpConnection.send_message(msg)
    smtpConnection.quit()



def main():
    create_directory_structure()    
    localFilename = create_filename("csv")
    records = get_records(addHeaderRow=True)
    fileToAttach = write_to_csv(records, localFilename)

    msg = create_message(fileToAttach)
    sendEmail(msg)

if __name__ == "__main__":
    main()