BigQuery for循环结果到Python smtplib消息体

I want to grab data from a BigQuery database at a set interval and have the results emailed automatically using smtplib.

我想以设定的间隔从BigQuery数据库中获取数据,并使用smtplib自动通过电子邮件发送结果。

I have an example below. I can pull data from BigQuery just fine. I can send email via smtplib just fine. What I need to do is combine. I want to store the results of a for loop into the email body of the message. I believe I do that by calling the function. However when I do that. I receive the error.

我在下面有一个例子。我可以从BigQuery中提取数据就好了。我可以通过smtplib发送电子邮件就好了。我需要做的就是结合起来。我想将for循环的结果存储到邮件的电子邮件正文中。我相信我通过调用函数来做到这一点。但是,当我这样做。我收到错误。

File "bqtest5.py", line 52, in server.sendmail(login_email, recipients, query_named_params('corpus', 'min_word_count')) File "/usr/lib/python2.7/smtplib.py", line 729, in sendmail esmtp_opts.append("size=%d" % len(msg)) TypeError: object of type 'NoneType' has no len()

在server.sendmail中输入文件“bqtest5.py”,第52行(login_email,recipients,query_named_params('corpus','min_word_count'))文件“/usr/lib/python2.7/smtplib.py”,第729行, sendmail esmtp_opts.append(“size =%d”%len(msg))TypeError:“NoneType”类型的对象没有len()

from google.cloud import bigquery
import smtplib

#Variables
login_email = 'MYEMAIL'
login_pwd = 'MYPASSWORD'
recipients ='EMAILSENDINGTO'


#Create a function
#specifies we are going to add two paramaters
def query_named_params(corpus, min_word_count):
    #Create a Client
    client = bigquery.Client()
    #Define the query
    query = """
        SELECT word, word_count
        FROM `bigquery-public-data.samples.shakespeare`
        WHERE corpus = @corpus
        AND word_count >= @min_word_count
        ORDER BY word_count DESC;
        """
    #Define the paramaters
    query_params = [
        bigquery.ScalarQueryParameter('corpus', 'STRING', 'sonnets'),
        bigquery.ScalarQueryParameter(
            'min_word_count', 'INT64', 10)
    ]
    #Create job configuration
    job_config = bigquery.QueryJobConfig()
    #Add Query paramaters
    job_config.query_parameters = query_params
    #P
    query_job = client.query(query, job_config=job_config)

    # Print the results.
    destination_table_ref = query_job.destination
    table = client.get_table(destination_table_ref)
    resulters = client.list_rows(table)
    for row in resulters:
        print("{} : {} views".format(row.word, row.word_count))

# --------------------EMAIL PORTION -------------#
#)smtplib connection    print('messenger()')
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(login_email, login_pwd)

msg = """

"""
server.sendmail(login_email, recipients, query_named_params('corpus', 
'min_word_count'))
server.quit()


if __name__ == '__main__':
    query_named_params('corpus','min_word_count')

1 个解决方案

#1

Your function is not returning any value which causes an empty message to be sent to server.sendmail.

您的函数未返回任何导致将空消息发送到server.sendmail的值。

Try this instead:

试试这个:

def query_named_params(corpus, min_word_count):

    (...)

    query_params = [
        bigquery.ScalarQueryParameter('corpus', 'STRING', corpus),
        bigquery.ScalarQueryParameter(
            'min_word_count', 'INT64', min_word_count)
    ]

    (...)
    s = ""
    for row in resulters:
        s+= "{} : {} views\n".format(row.word, row.word_count))
    return s

(...)
server.sendmail(login_email, recipients, query_named_params('sonnets', 10))

This probably will not send a very readable message though. Depending on the complexity of the results from your BQ table I'd recommend using Jinja2 to create a HTML template and then rendering it to be sent as the mail body.

这可能不会发送非常可读的消息。根据BQ表结果的复杂程度,我建议使用Jinja2创建HTML模板,然后将其呈现为邮件正文。

#1