Python:循环遍历 Dict/Dataframe 中的行以制作 CSV 并将其作为电子邮件发送给适当的收件人

Question

我有一个关于循环的问题，该循环将一个单独的文件(从同一文件位置)通过电子邮件发送给一个单独的收件人，直到数据框或字典中的行列表用完为止。我基本上有一个使用 impyla 的 hadoop 连接，我在其中使用原始输入通过 SQL 手动查找数据并向收件人发送电子邮件；但是，我想通过创建一个循环语句来进一步自动化这一点，该循环语句查看引用数据框/字典(取决于哪个更有效)以每行保存一个唯一文件并将电子邮件发送给同一行中的收件人，直到循环结束。任何建议或帮助将不胜感激!我做过简单的循环，但出于某种原因，我想不出最优化的方法。我当前的代码如下，其中包含我想要设置的变量类型:

import pandas as pd
import numpy as np
from impala.dbapi import connect
#Imports required libraries for dataframe manipulation, connection to hadoop
conn = connect(host="hostname", port=10000)
#establishes a connection to host hadoop server

ID = raw_input("Please enter the ID")
path = "C:\users\myself\desktop/The_%s_Report.csv" %lookup
#sets the save path for the new file and will serve as the attachment path for the email

#df = pd.DataFrame(data, columns = "ID", "lookup", "recipient")
#dict = {"ID", "lookup", "recipient"}
sql = "SELECT * FROM %s" %ID
#SQL expression that needs to be edited before going into cursor.execute() command
cursor = conn.cursor()
cursor.execute(sql)
results = cursor.fetchall()
#fetches the table outlined by the sql variable.

df = pd.DataFrame(data = results, columns = ["Column1", "Column2", 
"Column3", "Column4", "Column5"])
#Establishes dataframe from the hadoop fetch resutls

df.to_csv(path, sep=",", index_label= None, index = False)
#Pushes the file out to a specified filepath
cursor.close()


import win32com.client

olMailItem = 0x0
obj = win32com.client.Dispatch("Outlook.Application")
newMail = obj.CreateItem(olMailItem)
newMail.Subject = "Sample Report %s" %lookup
#need to create a variable with lookup row value in the dataframe
newMail.Body = "Filler text"
newMail.To = "%s" recipient
attachment1 = path
newMail.Attachments.Add(attachment1)
newMail.Send()

Answer 1

我会按照您指定的方式创建一个简单的字典列表:

recipients = [
     {"id": "ID1", "lookup": "lookup1", "recipient": "recipient1"},
     {"id": "ID2", "lookup": "lookup2", "recipient": "recipient2"},
     {"id": "ID3", "lookup": "lookup3", "recipient": "recipient3"}
]

使用 get_data、create_csv 和 send_email 等函数重新格式化您的脚本

然后用 for 循环迭代它们:

for recipient in recipients:
    data = get_data(recipient["id"])
    csv_file = create_csv(recipient["lookup"], data)
    send_email(csv_file, recipient["lookup"], recipient["recipient"])

此外，如果可能，尽量减少 SQL 查询并在 pandas 中进行操作。

编辑:https://pastebin.com/6ZGJMGGS 的修改版本:

reference = [
    {'id': '4', "lookup": "'Name1", "recipient": "me.com"},
    {'id': '56', "lookup": "Name2", "recipient": "you.com"},
    {'id': '76', "lookup": "Name3", "recipient": "them.com"}
]


def get_data(ID):
    sql = " SELECT %s from Main" %ID
    #SQL expression that needs to be edited before going into cursor.execute() command
    cursor = conn.cursor()
    cursor.execute(sql)
    results = cursor.fetchall()
    return results

    #Enters the path of the save file and appends the DSP ID to the title

def create_csv(company, table):
    path = "C:\Users\ME\Desktop/The_%s_Report.csv" %company

    df = pd.DataFrame(data = table, columns = ["Date", "Thangs", "Stuff", "$$$", "Employees"])
    #Creates Data Frame for the results variable
    df = df[["Date", "Stuff", "Thangs", "$$$", "Employees"]]
    #Re-orders the columns for easy readability
    df['Date'] = pd.to_datetime(df.Date)
    df.sort_values(by = 'Date', axis=0, ascending = 'True', inplace=True)
    #Converts the date to datetime variable and reorders the dates
    df["Thangs"] = df["Thangs"].astype('float')
    df["$$$"] = df["$$$"].astype('int')
    df.to_csv(path, sep=",", index_label= None, index = False)
    return path

def send_email(csv_file, company, recipient):
    olMailItem = 0x0
    obj = win32com.client.Dispatch("Outlook.Application")
    newMail = obj.CreateItem(olMailItem)
    newMail.Subject = "Sample Report - Live Test Run %s" %company
    newMail.Body = "Text Text Text"
    newMail.To = recipient
    #newMail.CC = ""
    #newMail.BCC = "address"
    attachment1 = csv_file
    newMail.Attachments.Add(attachment1)
    #newMail.display()
    newMail.Send()

for recipient in reference:
    table = get_data(recipient[['id']], recipient['lookup'])
    csv_file = create_csv(recipient['lookup'], table)
    send_email(csv_file, recipient['lookup'], recipient['recipient'])