来自MySQL的Python Email表 [英] Python Email table from MYSQL

问题描述

我正在尝试将MYSQL查询的结果放入电子邮件正文中，并作为csv或xls附件。

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.

下面的代码可以正常工作并发送电子邮件的唯一问题是，如果MYSQL查询的结果超过一行，则只有第一行显示在电子邮件中。

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 = "[e-mail]"
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()

推荐答案

您需要进行一些更改。首先，您需要将 fetchone（）调用更改为 fetchall（）调用。这将从您的 SELECT 查询返回所有结果。

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.

接下来，您要将这些结果写入CSV文件。让我们使用上面查询的结果来做到这一点：

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()

此时，您在 / tmp / file_name中有一个文件包含CSV结果的.csv （或您选择的路径和名称）。最后一步是将其附加到电子邮件。

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更改了 msg.attach（）函数，并使用了代码来自另一个问题。

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

完成后，您仍然在 /tmp/file_name.csv 中有一个文件。您现在可以安全地删除它。

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

这篇关于来自MySQL的Python Email表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！