Python Email table from MYSQL

Question

I am trying to get the results of a MYSQL query into the body of an email and also as a csv or xls attachment.

My code below works and sends the email only problem is if the results from the MYSQL query are more than a row only the first row shows up in the email.

import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email import Encoders
import os
import MySQLdb
import string
import datetime
import time

today = (time.strftime("%m/%d/%Y"))

#print today

db = MySQLdb.connect(host="-----.com", # your host, usually localhost
                     user="----", # your username
                      passwd="-------", # your password
                      db="dailies") # name of the data base


cursor49=db.cursor()

cursor49.execute("SELECT PLACEMENT_NAME FROM dailies.pub_cpm join placement ON placement.PLACEMENT_id = pub_cpm.PLACEMENT_ID where date(pub_cpm.created) = date(now())")

results49 = cursor49.fetchone()

# Commit your changes in the database
db.commit()

# disconnect from server
db.close()

results50 = "Latest Pub CPM Name(s): %s" % (results49)

gmail_user = "------@gmail.com"
gmail_pwd = "g---a"

mailServer = smtplib.SMTP("smtp.gmail.com", 587)



to = ['-----@g-----']

msg = MIMEMultipart()
msg['From'] = gmail_user
msg['To'] = ", ".join(to)
msg['Subject'] = "Database Alerts: %s" % (today)

body =  results50

msg.attach(MIMEText(body, 'plain'))


mailServer.ehlo()
mailServer.starttls()
mailServer.ehlo()

text = msg.as_string()

mailServer.login(gmail_user, gmail_pwd)
mailServer.sendmail(gmail_user, to, text)

# Should be mailServer.quit(), but that crashes...
mailServer.close()

Of course only one shows up. That's all you are asking for: `.fetchone()` — Andy, Sep 25 '14 at 21:59

score 1 · Accepted Answer · edited May 23 '17 at 12:13

There are a couple changes you need to make. First, you need to change your fetchone() call to a fetchall() call. This will return all results from your SELECT query.

Next, you want to write these to a CSV file. Let's do that using the results from our query above:

results49 = cursor.fetchall()
fp = open('/tmp/file_name.csv', 'w')    # You pick a name, it's temporary
attach_file = csv.writer(fp)
attach_file.writerows(results49)
fp.close()

At this point, you have a file in /tmp/file_name.csv (or what ever path and name you picked) that contains your CSV results. The final step is to attach this to an email.

msg = MIMEMultipart()
msg['From'] = gmail_user
msg['To'] = ", ".join(to)
msg['Subject'] = "Database Alerts: %s" % (today)

body =  results50

part = MIMEBase('application', "octet-stream")
part.set_payload(open("/tmp/file_name.csv", "rb").read())    # This is the same file name from above
Encoders.encode_base64(part)

part.add_header('Content-Disposition', 'attachment; filename="/tmp/file_name.csv"')

msg.attach(part)

I changed your msg.attach() function and utilized code from another question.

Once this is done, you still have a file in /tmp/file_name.csv. You can delete this safely at this point.

Only change I hade to made was to `attach_file.writerows(rows)` which should be `attach_file.writerows(results49)` Other than that it worked like a charm. — cartoom02, Sep 26 '14 at 12:31

Python Email table from MYSQL

1 Answers1