# encoding=utf-8
import pymysql
import openpyxl
import datetime
import pandas as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import smtplib
import pandas as pd
#从数据库中读取数据
conn =pymysql.connect(host='106.75.62.200',port=3306,user='UserSelect',passwd='1q2w3e4r5t',db='bingofzd',charset="utf8")
sql1='\
SELECT c.serverid, b.channelname, a.roleid ,a.rolename ,a.registertime ,COUNT(c.roleid) as paytimes ,SUM(c.money) as pay ,a.userid \
,a.device ,a.registerip,MAX(paytime) as lastpaytime \
FROM createrole as a , recharge as c,allchannel as b \
WHERE a.roleid=c.roleid AND a.serverid=c.serverid AND a.gamechannel=b.channelid \
AND c.paytime >="2018-09-14 00:00:00" \
AND a.registertime >= "2018-09-14 00:00:00" \
AND a.registertime <now() \
AND a.roleid NOT in (SELECT roleid FROM createrole_optimize) \
GROUP BY a.gamechannel, a.roleid ,a.rolename,a.registertime \
HAVING SUM(c.money)>500 \
ORDER BY SUM(c.money) DESC \
'
sql2='\
SELECT a.roleid,MAX(b.logintime) as lasttime \
FROM recharge as a ,rolelogin as b \
WHERE a.paytime> "2018-09-14 00:00:00" \
AND b.logintime> "2018-09-14 00:00:00" \
AND a.roleid=b.roleid \
GROUP BY a.roleid \
'
df1=pd.read_sql(sql1,conn)
df2=pd.read_sql(sql2,conn)
#print(df1.head(10))
#print(df2.head(10))
dfu=pd.merge(df1,df2,on="roleid",how="left")
print(dfu.head(10))
#生成excel文件
def get_excel(df,file):
#新建一个工作薄对象
new = openpyxl.Workbook()
sheet = new.active
sheet.title = '数据展示' #数据标题
field=list(df);data=df.values #获取列表和数据
print(field)
#将字段名称循环写入excel第一行，因为字段格式列表里包含列表，每个列表的第一元素才是字段名称
for col in range(len(field)):
#row代表行数，column代表列数，value代表单元格输入的值，行数和列数都是从1开始，这点于python不同要注意
_ = sheet.cell(row=1, column=col+1, value=u'%s' % field[col])
#将数据循环写入excel的每个单元格中
for row in range(len(data)):
for col in range(len(field)):
#因为第一行写了字段名称，所以要从第二行开始写入
_ = sheet.cell(row=row+2, column=col + 1, value=u'%s' % data[row][col])
#将生成的excel保存，这步是必不可少的
newworkbook = new.save(file)#返回生成的excel
return newworkbook
get_excel(dfu,r'C:\Users\mei-huang\Desktop\明日决胜玩家付费数据(500以上).xlsx') #保存文件到指定位置
#获取昨天日期
def getYesterday():
# 获取昨天日期的字符串格式的函数
#获取今天的日期
today = datetime.date.today()
#获取一天的日期格式数据
oneday = datetime.timedelta(days=1)
#昨天等于今天减去一天
yesterday = today - oneday
#获取昨天日期的格式化字符串
yesterdaystr = yesterday.strftime('%Y-%m-%d')
#返回昨天的字符串
return yesterdaystr
#生成邮件的函数
def create_email(email_from, email_to, email_Subject, email_text, annex_path, annex_name):
#生成一个空的带附件的邮件实例
message = MIMEMultipart()
#将正文以text的形式插入邮件中
message.attach(MIMEText(email_text, 'plain', 'utf-8'))
#生成发件人名称（这个跟发送的邮件没有关系）
message['From'] = Header(email_from, 'utf-8')
#生成收件人名称（这个跟接收的邮件也没有关系）
message['To'] = Header(email_to, 'utf-8')
#生成邮件主题
message['Subject'] = Header(email_Subject, 'utf-8')
#读取附件的内容
att1 = MIMEText(open(annex_path, 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
#生成附件的名称
att1["Content-Disposition"] = 'attachment; filename=' + annex_name
#将附件内容插入邮件中
message.attach(att1)
#返回邮件
return message
# 输入发件人昵称、收件人昵称、主题，正文，附件地址,附件名称生成一封邮件
z=create_email("szl", "黑川", "明日决胜付费玩家数据","来自小姐姐的礼物", r'C:\Users\mei-huang\Desktop\明日决胜玩家付费数据(500以上).xlsx', "zhang.xlsx")
print(type(z))
#定义发送邮件的函数
def send_email(sender, password, receiver, msg):
# 一个输入邮箱、密码、收件人、邮件内容发送邮件的函数
try:
#找到你的发送邮箱的服务器地址，已加密的形式发送
server = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465) # 发件人邮箱中的SMTP服务器
print(1)
server.ehlo()
#登录你的账号
print(2)
server.login(sender, password) # 括号中对应的是发件人邮箱账号、邮箱密码
#发送邮件
server.sendmail(sender, receiver, msg.as_string()) # 括号中对应的是发件人邮箱账号、收件人邮箱账号（是一个列表）、邮件内容
print("邮件发送成功")
server.quit() # 关闭连接
except Exception:
#print(traceback.print_exc())
print("邮件发送失败")
send_email("zhilong.shi@yingxiong.com", "147258369stST",["zhilong.shi@yingxiong.com","512415688@qq.com"],z)
网友评论