How to Setup an Automatic MySQL Email Script with Python

This article shows you how to have a script query a MySQL DB and send the output to yourself via email. You can get fancier and use this to also send reports for you too through Python and attach them to the email and send it out.

How to Setup and Automatic Python Email Script:

http://naelshiab.com/tutorial-send-email-python/

https://docs.python.org/2/library/smtplib.html

Python Code:

import smtplib

server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login("YOUR EMAIL ADDRESS", "YOUR PASSWORD")

msg = "YOUR MESSAGE!"
server.sendmail("YOUR EMAIL ADDRESS", "THE EMAIL ADDRESS TO SEND TO", msg)
server.quit()

If you want to send the output of a MySQL query which would be useful if you want to monitor a table every hour. You would use the below code in a Cron Job or a Windows Scheduled Task:

Python Code to Send a MySQL via Email:

#we need to import the smtplib to be able to send mail through Python
#we also need to import the MySQL Connector to be able to query the DB and fetch and output from it
import smtplib
import mysql.connector

#db credentials that you want to use localhost or the IP of your DB SERVER that you want to use
 conn =mysql.connector.connect(host='localhost',database='SCHEMA_YOU_WANT_TO_USE',user='DBUSER',password='DBPASSWORD') 

 #this block fetches the query that you want and puts all the rows into the cursor.fetchall() command
 cursor = conn.cursor()
 query = "SELECT QUERY THAT YOU WANT TO RUN"
 cursor.execute(query)
 rows = cursor.fetchall()

 #This will print the output of your query in your Python Editor or terminal
 for row in rows:
    print(row)

  # This block of code sets up your smtp server, for this example we are using gmail. If you want to use hotmail or another email provider you will need to find out their smtp server and port number
  server = smtplib.SMTP('smtp.gmail.com', 587) 
  server.starttls()
  server.login("ACCOUNT YOU WAN TO EMAIL FROM @gmail.com", "EMAIL PASSWORD") #account that emails are coming from
  msg = "The count in the table is currently this many rows " + str(rows) # message
  server.sendmail("SOME-GMAIL-ACCOUNT-FROM @gmail.com", "SOME-GMAIL-ACCOUNT-TO @gmail.com", msg) # 
  email from and to
  server.quit()