# -*- coding: utf-8 -*-
"""
Created on Sun May 5 10:13:11 2019
@author: Administrator
"""
# -*- coding: utf-8 -*-
"""
Created on Thu Mar 14 16:35:00 2019
@author: Administrator
"""
import pandas as pd
import pymysql
import time
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.writer.excel import ExcelWriter
con = pymysql.connect(host='XXX', user='XXX',
password='XXX', database='XXX', charset="XXX", port=XXX)
#信贷
zhuce=pd.read_sql_query(sql="""
SELECT
a.`时间`,ifnull(b.`人数`,0)人数
from
(
SELECT
concat(HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(a.account) 人数
from futan_login_record a
where date( a.create_time )="2019-05-01"
GROUP BY concat( date( a.create_time ), ' ', HOUR ( a.create_time ), "点", '--',HOUR ( a.create_time )+1,"点")
ORDER BY HOUR ( a.create_time )
) a
LEFT JOIN
(
SELECT
concat( HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(a.account) 人数
from futan_users a
where date( a.create_time )=CURDATE()
GROUP BY concat( date( a.create_time ), ' ', HOUR ( a.create_time ), "点", '--',HOUR ( a.create_time )+1,"点")
) b on a.`时间`=b.`时间`
;""" ,con=con)
gwq=pd.read_sql_query(sql="""
SELECT
a.`时间`,ifnull(b.`人数`,0)人数,ifnull(b.张数,0) 张数,ifnull(b.金额,0) 金额
from
(
SELECT
concat(HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(a.account) 人数
from futan_login_record a
where date( a.create_time )="2019-05-01"
GROUP BY concat( date( a.create_time ), ' ', HOUR ( a.create_time ), "点", '--',HOUR ( a.create_time )+1,"点")
ORDER BY HOUR ( a.create_time )
) a
LEFT JOIN
(
SELECT
concat( HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )时间,COUNT(DISTINCT a.uid)人数,COUNT( a.uid) 张数,SUM(a.favor_money)金额
from futan_users_coupon a
where a.is_used="USABLE"
and date( a.create_time )=CURDATE()
GROUP BY concat( HOUR ( a.create_time ),"点", '--', HOUR ( a.create_time )+1,"点" )
) b on a.`时间`=b.`时间`
;""" ,con=con)
con.close()
today = datetime.date.today().strftime('%Y-%m-%d')
writer = pd.ExcelWriter("D:\\XXX\\XXX\\XXX\\"+today+"XXX.xlsx")
zhuce.to_excel(writer,sheet_name='每小时注册')
gwq.to_excel(writer,sheet_name='购物券')
writer.save()
today1=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) #当前时间,用于excel上
wb = load_workbook("D:\\XXX\\XXX\\XXX\\"+today+"XXX.xlsx",data_only=True)
ws = wb.get_sheet_by_name('每小时注册')
shijian = []
renshu = []
for rown in range(2,30):
for coln in range(2,4):
value = ws.cell(row=rown,column=coln).value
if coln == 2:
shijian.append(value)
elif coln == 3:
renshu.append(value)
print(shijian)
print(renshu)
wb1= load_workbook("D:\\XXX\\XXX\\XXX\\"+today+"XXX.xlsx",data_only=True)
ws1 = wb1.get_sheet_by_name('XXX')
shijian1 = []
renshu1 = []
zhangshu=[]
jine=[]
for rown in range(2,30):
for coln in range(2,6):
value = ws1.cell(row=rown,column=coln).value
if coln == 2:
shijian1.append(value)
elif coln == 3:
renshu1.append(value)
elif coln == 4:
zhangshu.append(value)
elif coln == 5:
jine.append(value)
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
#from email.mime.application import MIMEApplication 附件专用
def send_mail():
# 定义变量
from_addr = "XXX"
password = "XXX"
to_addr = "XXX"
#acc = "XXX"
message = MIMEMultipart()
message['From'] = from_addr
message['To'] = to_addr
#message['Cc'] = acc
message['subject'] = Header('%s' % ('每小时注册人数'))
html = """\
<!DOCTYPE html>
<html>
<meta charset="utf-8">
<head>
<title>iOS - Bugly崩溃日报</title>
</head>
<body>
<div id="container">
<div id="content">
<p>
""" +today1+ """每小时注册人数:
<table width="800" border="2" bordercolor="black" cellspacing="2">
<tr>
<td><strong>时间</strong></td>
<td><strong>人数</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[0]+ """</strong></td>
<td><strong>""" +str(renshu[0])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[1]+ """</strong></td>
<td><strong>""" +str(renshu[1])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[2]+ """</strong></td>
<td><strong>""" +str(renshu[2])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[3]+ """</strong></td>
<td><strong>""" +str(renshu[3])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[4]+ """</strong></td>
<td><strong>""" +str(renshu[4])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[5]+ """</strong></td>
<td><strong>""" +str(renshu[5])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[6]+ """</strong></td>
<td><strong>""" +str(renshu[6])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[7]+ """</strong></td>
<td><strong>""" +str(renshu[7])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[8]+ """</strong></td>
<td><strong>""" +str(renshu[8])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[9]+ """</strong></td>
<td><strong>""" +str(renshu[9])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[10]+ """</strong></td>
<td><strong>""" +str(renshu[10])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[11]+ """</strong></td>
<td><strong>""" +str(renshu[11])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[12]+ """</strong></td>
<td><strong>""" +str(renshu[12])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[13]+ """</strong></td>
<td><strong>""" +str(renshu[13])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[14]+ """</strong></td>
<td><strong>""" +str(renshu[14])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[15]+ """</strong></td>
<td><strong>""" +str(renshu[15])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[16]+ """</strong></td>
<td><strong>""" +str(renshu[16])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[17]+ """</strong></td>
<td><strong>""" +str(renshu[17])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[18]+ """</strong></td>
<td><strong>""" +str(renshu[18])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[19]+ """</strong></td>
<td><strong>""" +str(renshu[19])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[20]+ """</strong></td>
<td><strong>""" +str(renshu[20])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[21]+ """</strong></td>
<td><strong>""" +str(renshu[21])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[22]+ """</strong></td>
<td><strong>""" +str(renshu[22])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian[23]+ """</strong></td>
<td><strong>""" +str(renshu[23])+ """</strong></td>
</tr>
</table>
</p>
<p>
""" +today1+ """购物券情况:
<table width="800" border="2" bordercolor="black" cellspacing="2">
<tr>
<td><strong>时间</strong></td>
<td><strong>人数</strong></td>
<td><strong>张数</strong></td>
<td><strong>金额</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[0]+ """</strong></td>
<td><strong>""" +str(renshu1[0])+ """</strong></td>
<td><strong>""" +str(zhangshu[0])+ """</strong></td>
<td><strong>""" +str(jine[0])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[1]+ """</strong></td>
<td><strong>""" +str(renshu1[1])+ """</strong></td>
<td><strong>""" +str(zhangshu[1])+ """</strong></td>
<td><strong>""" +str(jine[1])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[2]+ """</strong></td>
<td><strong>""" +str(renshu1[2])+ """</strong></td>
<td><strong>""" +str(zhangshu[2])+ """</strong></td>
<td><strong>""" +str(jine[2])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[3]+ """</strong></td>
<td><strong>""" +str(renshu1[3])+ """</strong></td>
<td><strong>""" +str(zhangshu[3])+ """</strong></td>
<td><strong>""" +str(jine[3])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[4]+ """</strong></td>
<td><strong>""" +str(renshu1[4])+ """</strong></td>
<td><strong>""" +str(zhangshu[4])+ """</strong></td>
<td><strong>""" +str(jine[4])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[5]+ """</strong></td>
<td><strong>""" +str(renshu1[5])+ """</strong></td>
<td><strong>""" +str(zhangshu[5])+ """</strong></td>
<td><strong>""" +str(jine[5])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[6]+ """</strong></td>
<td><strong>""" +str(renshu1[6])+ """</strong></td>
<td><strong>""" +str(zhangshu[6])+ """</strong></td>
<td><strong>""" +str(jine[6])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[7]+ """</strong></td>
<td><strong>""" +str(renshu1[7])+ """</strong></td>
<td><strong>""" +str(zhangshu[7])+ """</strong></td>
<td><strong>""" +str(jine[7])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[8]+ """</strong></td>
<td><strong>""" +str(renshu1[8])+ """</strong></td>
<td><strong>""" +str(zhangshu[8])+ """</strong></td>
<td><strong>""" +str(jine[8])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[9]+ """</strong></td>
<td><strong>""" +str(renshu1[9])+ """</strong></td>
<td><strong>""" +str(zhangshu[9])+ """</strong></td>
<td><strong>""" +str(jine[9])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[10]+ """</strong></td>
<td><strong>""" +str(renshu1[10])+ """</strong></td>
<td><strong>""" +str(zhangshu[10])+ """</strong></td>
<td><strong>""" +str(jine[10])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[11]+ """</strong></td>
<td><strong>""" +str(renshu1[11])+ """</strong></td>
<td><strong>""" +str(zhangshu[11])+ """</strong></td>
<td><strong>""" +str(jine[11])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[12]+ """</strong></td>
<td><strong>""" +str(renshu1[12])+ """</strong></td>
<td><strong>""" +str(zhangshu[12])+ """</strong></td>
<td><strong>""" +str(jine[12])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[13]+ """</strong></td>
<td><strong>""" +str(renshu1[13])+ """</strong></td>
<td><strong>""" +str(zhangshu[13])+ """</strong></td>
<td><strong>""" +str(jine[13])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[14]+ """</strong></td>
<td><strong>""" +str(renshu1[14])+ """</strong></td>
<td><strong>""" +str(zhangshu[14])+ """</strong></td>
<td><strong>""" +str(jine[14])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[15]+ """</strong></td>
<td><strong>""" +str(renshu1[15])+ """</strong></td>
<td><strong>""" +str(zhangshu[15])+ """</strong></td>
<td><strong>""" +str(jine[15])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[16]+ """</strong></td>
<td><strong>""" +str(renshu1[16])+ """</strong></td>
<td><strong>""" +str(zhangshu[16])+ """</strong></td>
<td><strong>""" +str(jine[16])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[17]+ """</strong></td>
<td><strong>""" +str(renshu1[17])+ """</strong></td>
<td><strong>""" +str(zhangshu[17])+ """</strong></td>
<td><strong>""" +str(jine[17])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[18]+ """</strong></td>
<td><strong>""" +str(renshu1[18])+ """</strong></td>
<td><strong>""" +str(zhangshu[18])+ """</strong></td>
<td><strong>""" +str(jine[18])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[19]+ """</strong></td>
<td><strong>""" +str(renshu1[19])+ """</strong></td>
<td><strong>""" +str(zhangshu[19])+ """</strong></td>
<td><strong>""" +str(jine[19])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[20]+ """</strong></td>
<td><strong>""" +str(renshu1[20])+ """</strong></td>
<td><strong>""" +str(zhangshu[20])+ """</strong></td>
<td><strong>""" +str(jine[20])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[21]+ """</strong></td>
<td><strong>""" +str(renshu1[21])+ """</strong></td>
<td><strong>""" +str(zhangshu[21])+ """</strong></td>
<td><strong>""" +str(jine[21])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[22]+ """</strong></td>
<td><strong>""" +str(renshu1[22])+ """</strong></td>
<td><strong>""" +str(zhangshu[22])+ """</strong></td>
<td><strong>""" +str(jine[22])+ """</strong></td>
</tr>
<tr>
<td><strong>""" +shijian1[23]+ """</strong></td>
<td><strong>""" +str(renshu1[23])+ """</strong></td>
<td><strong>""" +str(zhangshu[23])+ """</strong></td>
<td><strong>""" +str(jine[23])+ """</strong></td>
</tr>
</table>
</div>
</div>
</body>
</html>
"""
message.attach(MIMEText(html, 'html', 'utf-8')) # 邮件正文内容;
# 设置端口
smtpobj = smtplib.SMTP('smtp.qq.com', 25)
smtpobj.login(from_addr, password)
smtpobj.sendmail(from_addr, to_addr, message.as_string())
smtpobj.quit()
# 发送邮件
send_mail()
效果