python - 将 SQL 结果捕获到变量

Question

我希望能够查询数据库并通过电子邮件发送结果：

import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
import MySQLdb
import datetime


db = MySQLdb.connect("host","account","pass","db")
cursor = db.cursor()
cursor.execute("select report_date, ROUND(SUM(balance), 2) as 'Net', ROUND(SUM(short_mkt_value), 2) as 'Short', ROUND(SUM(int_balance ), 2) as 'Long' from intersum_recap where group_name = 'Carter' group by report_date order by report_date desc")
rows = cursor.fetchall()
for row in rows:
    data = (', '.join([str(u) for u in row]))

toaddr = 'email'
fromaddr = 'email'
msg = MIMEMultipart()
msg['To'] = toaddr
msg['From'] = fromaddr
msg['Subject'] = 'Net Debit by Account'

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

server = smtplib.SMTP('mail')
server.starttls()
server.login(fromaddr, 'pass')
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
server.quit()

db.close()

运行脚本后，电子邮件仅包含查询应返回的第一行。如何将 sql 查询的整个迭代捕获到要在“msg”中打印的变量中

感谢所有帮助

标签： pythonsql

Answer 1

data每次迭代都会覆盖您的变量。在循环外声明data变量for

rows = cursor.fetchall()
data = ''
for row in rows:
    data += (', '.join([str(u) for u in row]))+'\n'