python实现报表自动化_纯野生程序员:python连接mysql实现报表自动化

- coding:utf-8 --

import sys

import string, os, sys

import smtplib

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

import pymysql as mysql

import datetime

import schedule

import time

from datetime import datetime, date, timedelta

reload(sys)

sys.setdefaultencoding('utf8')

todo presto config

presto_host_test = "localhost"

presto_port = 3306

处理日期

yesterday 表示昨天，例如：2020-03-09

yesterday = date.today() + timedelta(days = -1)

year = str(yesterday)[0:4]

def get_month(name):

if str(name)[5:6] == '0':return str(name)[6:7]

else: return str(name)[5:7]

def get_date(name):

if str(name)[9:10] == '0':return str(name)[10:11]

else: return str(name)[9:11]

todo email config

todo 表头 需要修改

email_table_head1 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"昨日涨幅",u"时间" ]

email_table_head2 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]

email_table_head3 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]

email_table_head4 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]

email_table_head5 = [u"基金代码", u"基金名称",u"基金净值",u"累计净值",u"平均涨幅" ]

todo 表title 需要修改

email_table_title1 = u"%s %s %s %s %s %s %s %s"%("一、",year,"年",get_month(yesterday),"月",

get_date(yesterday),"日","天天基金概括")

email_table_title2 = u"二、天天基金历史平均增幅最高"

email_table_title3 = u"三、过去三十天基金平均增幅最高"

email_table_title4 = u"四、过去三十天基金平均基金净值最高"

email_table_title5 = u"五、过去三十天基金平均累计净值最高"

todo 邮件名称 需要修改

email_title = u"天天基金净值概况"

todo 邮件发送列表 需要修改

email_to_list = [['flyba049@126.com']] # 接受人

def get_coums(name):

for x in email_to_list:

return

todo execute sql 需要修改

sql_1 = "select * from fund_db.flyba_yestoday_rate"

sql_2 = "select * from fund_db.flyba_history_rate"

sql_3 = "select * from fund_db.flyba_max_rate30"

sql_4 = "select * from fund_db.flyba_max_net30"

sql_5 = "select * from fund_db.flyba_max_total30"

def send_mail(mailto_list, sub, context):

# todo to_list：收件人；sub：主题；content：邮件内容

mail_host = "imap.exmail.qq.com"

mail_user = "邮箱" #选择发送邮件的邮箱

mail_pass = "密码" #选择发送邮件的邮箱

me = mail_user

msg = MIMEMultipart()

msg['Subject'] = sub

msg['From'] = me

msg['To'] = ";".join(mailto_list)

msg.attach(context)

s = smtplib.SMTP()

s.connect(mail_host)

s.login(mail_user, mail_pass)

s.sendmail(me, mailto_list, msg.as_string())

s.close()

def make_html_table(title, rows):

if title:

html_content = u"""

%s

""" %title

else:

html_content = u""""""

html_content += u"""

for row in rows:

html_content += u"""

for elem in row:

html_content += u"""

%s""" % elem

html_content += u"""

"""

html_content += u"""

"""

return html_content

def get_data_from_presto(host, port, sql, email_table_head):

rows = []

# todo 建立链接

cursor = mysql.connect(host='localhost',port = 3306,user='root', passwd='123456',db ='fund_db').cursor()

# todo 执行sql

cursor.execute(sql)

# todo 一行一行取数

for line in cursor.fetchall():

if line[0] is None:

rows.append(email_table_head)

else:

rows.append(line)

cursor.close()

# todo 返回列表

return rows

if name == 'main':

# todo 查到表内容

rows_context_1 = get_data_from_presto(presto_host_test, presto_port, sql_1,

email_table_head1)

rows_context_2 = get_data_from_presto(presto_host_test, presto_port, sql_2,

email_table_head2)

rows_context_3 = get_data_from_presto(presto_host_test, presto_port, sql_3,

email_table_head3)

rows_context_4 = get_data_from_presto(presto_host_test, presto_port, sql_4,

email_table_head4)

rows_context_5 = get_data_from_presto(presto_host_test, presto_port, sql_5,

email_table_head5)

# todo 加入表头

rows_context_1.insert(0, email_table_head1)

rows_context_2.insert(0, email_table_head2)

rows_context_3.insert(0, email_table_head3)

rows_context_4.insert(0, email_table_head4)

rows_context_5.insert(0, email_table_head5)

# todo 制作表格

html_content_1 = make_html_table( email_table_title1, rows_context_1)

html_content_2 = make_html_table( email_table_title2, rows_context_2)

html_content_3 = make_html_table( email_table_title3, rows_context_3)

html_content_4 = make_html_table( email_table_title4, rows_context_4)

html_content_5 = make_html_table( email_table_title5, rows_context_5)

# todo 发送邮件

for name_list in email_to_list:

def get_name(name):

if name == ['邮箱']:return '名字'

else: return 'Jack Yangon'

email_to_total = u"\n%s %s\n %s\n" % ("\n尊敬的",get_name(name_list),"\n：")

# 表示一个空格

mail_msg = '''

    您好，请查收如下天天基金数据，谢谢！

'''

# todo 格式化

context = MIMEText(email_to_total+mail_msg+

html_content_1 + html_content_2 + html_content_3 + html_content_4

+ html_content_5 ,

_subtype='html', _charset='utf-8')

send_mail(name_list, email_title,context)

print("send the email success")