gpt4 book ai didi

python-3.x - 如何从 Google BigQuery 行迭代器对象返回字符串?

转载 作者:行者123 更新时间:2023-12-05 02:11:18 25 4
gpt4 key购买 nike

我的任务是编写一个 Python 脚本,该脚本可以从 BigQuery 中获取结果并将其通过电子邮件发送出去。我已经编写了可以成功发送电子邮件的代码,但我在实际电子邮件中包含 BigQuery 脚本的结果时遇到了问题。查询结果是正确的,但我从查询(结果)返回的实际对象总是返回为 Nonetype。

例如,电子邮件应如下所示:

你好,

您有以下问题“未解决”超过 7 天:

-在此处列出来自 bigquery 代码的问题

谢谢。

代码从 contacts.txt 文件中读取联系人,并从 message.txt 文件中读取电子邮件模板。我试图将bigquery对象做成一个字符串,但仍然报错。

from google.cloud import bigquery
import warnings
warnings.filterwarnings("ignore", "Your application has authenticated using end user credentials")
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from string import Template


def query_emailtest():
client = bigquery.Client(project=("analytics-merch-svcs-thd"))
query_job = client.query("""
select dept, project_name, reset, tier, project_status, IssueStatus, division, store_number, top_category,
DATE_DIFF(CURRENT_DATE(), in_review, DAY) as days_in_review
from `analytics-merch-svcs-thd.MPC.RESET_DETAILS`
where in_review IS NOT NULL
AND IssueStatus = "In Review"
AND DATE_DIFF(CURRENT_DATE(), in_review, DAY) > 7
AND ready_for_execution IS NULL
AND project_status = "Active"
AND program_name <> "Capital"
AND program_name <> "SSI - Capital"
LIMIT 50
""")
results = query_job.result() # Waits for job to complete.
return results #THIS IS A NONETYPE


def get_queryresults(results): #created new method to put query results into a for loop and store it in a variable
for i,row in enumerate(results,1):
bq_data = (i , '. ' + str(row.dept) + " " + row.project_name + ", Reset #: " + str(row.reset) + ", Store #: " + str(row.store_number) + ", " + row.IssueStatus + " for " + str(row.days_in_review)+ " days")
print (bq_data)

def get_contacts(filename):
names = []
emails = []
with open(filename, mode='r', encoding='utf-8') as contacts_file:
for a_contact in contacts_file:
names.append(a_contact.split()[0])
emails.append(a_contact.split()[1])
return names, emails

def read_template(filename):
with open(filename, 'r', encoding='utf-8') as template_file:
template_file_content = template_file.read()
return Template(template_file_content)


names, emails = get_contacts('mycontacts.txt') # read contacts
message_template = read_template('message.txt')
results = query_emailtest()

bq_results = get_queryresults(query_emailtest())


import smtplib
# set up the SMTP server
s = smtplib.SMTP(host='smtp-mail.outlook.com', port=587)
s.starttls()
s.login('email', 'password')


# For each contact, send the email:
for name, email in zip(names, emails):
msg = MIMEMultipart() # create a message

# bq_data = get_queryresults(query_emailtest())

# add in the actual person name to the message template
message = message_template.substitute(PERSON_NAME=name.title())
message = message_template.substitute(QUERY_RESULTS=bq_results) #SUBSTITUTE QUERY RESULTS IN MESSAGE TEMPLATE. This is where I am having trouble because the Row Iterator object results in Nonetype.


# setup the parameters of the message
msg['From']='email'
msg['To']='email'
msg['Subject']="This is TEST"
# body = str(get_queryresults(query_emailtest())) #get query results from method to put into message body


# add in the message body
# body = MIMEText(body)
#msg.attach(body)
msg.attach(MIMEText(message, 'plain'))


# query_emailtest()
# get_queryresults(query_emailtest())


# send the message via the server set up earlier.
s.send_message(msg)

del msg

消息模板:

亲爱的${PERSON_NAME},

希望你一切顺利。请找到以下针对已“审查中”超过 7 天的问题的警报。

${QUERY_RESULTS}

如果您想了解更多信息,请访问此链接,其中包含警报的完整仪表板 View 。

ISE 服务

最佳答案

BQ result() 函数返回一个生成器,所以我认为您需要将 return 更改为 yield from

我远不是 Python 专家,但以下精简代码对我有用。

from google.cloud import bigquery
import warnings
warnings.filterwarnings("ignore", "Your application has authenticated using end user credentials")

def query_emailtest():
client = bigquery.Client(project=("my_project"))
query_job = client.query("""
select field1, field2 from `my_dataset.my_table` limit 5
""")
results = query_job.result()
yield from results # NOTE THE CHANGE HERE

results = query_emailtest()

for row in results:
print(row.field1, row.field2)

关于python-3.x - 如何从 Google BigQuery 行迭代器对象返回字符串?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57694683/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com