query_mysql_to_email.py

import pymysql
import csv
import codecs
import datetime

from email_list import email

yesterday = (datetime.date.today() + datetime.timedelta(days=-1)).strftime("%Y%m%d")
yesterday1 = (datetime.date.today() + datetime.timedelta(days=-1)).strftime("%m-%d")
today = (datetime.date.today() + datetime.timedelta(days=0)).strftime("%Y%m%d")
def get_conn():
  conn = pymysql.connect(host='rm-uf66pyw2mf161x49988350.mysql.rds.aliyuncs.com', port=3306, user='betamysql', passwd='xxxxxx', db='cust_activity')
  return conn

def get_act_games():
  conn = pymysql.connect(host='rm-uf66pyw2mf161x49988350.mysql.rds.aliyuncs.com', port=3306, user='iyunuser', passwd='xxxxxx', db='act_games')
  return conn

def query_all(cur, sql, args):
  cur.execute(sql, args)
  return cur.fetchall()
def read_mysql_to_csv(filename):
  with codecs.open(filename=filename, mode='w', encoding='utf-8') as f:
    write = csv.writer(f, dialect='excel')
    conn = get_conn()
    cur = conn.cursor()
    sql = f'select u.city,u.`name`,u.nick_name,u.mobile,u.id, p.create_time,p.prize_level,p.prize_text from prize_record  p INNER JOIN user_ms u on p.user_id=u.id where p.create_time>= {yesterday} AND  p.create_time< {today} order by create_time;'
    results = list(query_all(cur=cur, sql=sql, args=None))
    for result in results:
      print(result)
      write.writerow(result)
  email('yansongwel@163.com', filename)

def read_mysql_to_csv1(filename):
  with codecs.open(filename=filename, mode='w', encoding='utf-8') as f:
    write = csv.writer(f, dialect='excel')
    write.writerow(('总访问次数（PV）', f'{yesterday1}日访问次数', '总访问人数（UV)', f'{yesterday1}日访问人数（UV)'))
    conn = get_conn()
    cur = conn.cursor()
    sql1 = f'''SELECT a.totalVisitCount '总访问次数（PV）' ,b.dailyVisitCount '{yesterday1}日访问次数' ,uc.totalUserVisitCount '总访问人数（UV)',d.dailyUserVisitCount '{yesterday1}日访问人数（UV)' from
(SELECT count(1)  totalVisitCount from activity_record where page_mark='msAnswer' and visit_mode='访问首页') a,
(SELECT count(1) dailyVisitCount  from activity_record where page_mark='msAnswer' and visit_mode='访问首页' and create_time>={yesterday} AND  create_time<{today})b,
(SELECT count(1) totalUserVisitCount from (select DISTINCT internet_id  from activity_record where page_mark='msAnswer' and visit_mode='访问首页') c)uc,
(SELECT count(1) dailyUserVisitCount from  (select DISTINCT internet_id from activity_record where page_mark='msAnswer' and visit_mode='访问首页'  and create_time>={yesterday} AND  create_time<{today} ) a)d;'''
    results = query_all(cur=cur, sql=sql1, args=None)
    for result in results:
      print(result)
      write.writerow(result)
  email('yansongwel@163.com', filename)

def read_mysql_to_csv2(filename):
  with codecs.open(filename=filename, mode='w', encoding='utf-8') as f:
    write = csv.writer(f, dialect='excel')
    write.writerow(('游戏次数', '中奖人数'))
    conn = get_act_games()
    cur = conn.cursor()
    sql1 = '''SELECT
        COUNT( 1 ) AS "游戏次数",
        COUNT( DISTINCT user_id ) AS "中奖人数" 
FROM
        act_games.act_answer_account_reward 
WHERE
        game_id = 66 
        AND TO_DAYS(create_time) = TO_DAYS(NOW());'''
    results = list(query_all(cur=cur, sql=sql1, args=None))
    sql2 = '''
    SELECT
        COUNT( 1 ) AS "游戏次数",
        COUNT( DISTINCT user_id ) AS "中奖人数" 
FROM
        act_games.act_answer_account_reward 
WHERE
        game_id = 66;
    '''
    results2 = query_all(cur=cur, sql=sql2, args=None)
    results.append(results2[0])
    for result in results :
      print(result)
      write.writerow(result)

  email('yansongwel@163.com', filename)

if __name__ == '__main__':
  read_mysql_to_csv('{}.mengshang.csv'.format(yesterday))
  read_mysql_to_csv1('{}.mengshang.csv'.format(yesterday1))
  read_mysql_to_csv2(f'{today}taofucheng.csv')

import pymysql
import csv
import codecs
import datetime

import smtplib
from email.header import Header
from email.utils import formataddr
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart


def email(receiver, textFile_path):
    host = 'smtp.exmail.qq.com'  # 发件人邮箱的SMTP服务器
    sender = 'XXXXXX'  # 发件人邮箱账号
    password = 'XXXXXX'  # 发件人邮箱密码（不是qq密码，通过设置--》账户--》开启--》授权码）
    receivers = [receiver]  # 接收人邮箱账号（可以多个，逗号隔开）

    # 创建多形式组合邮件
    msg = MIMEMultipart('mixed')
    # msg['From'] = formataddr(['admin', sender])  # 发件人邮箱昵称、发件人邮箱账号
    # msg['To'] = formataddr(['wwww', ','.join(receivers)])  # 收件人邮箱昵称、收件人邮箱账号
    # msg['To'] = ','.join(receivers)
    msg['Subject'] = "❤ 每日sql查询 ❤"  # 邮件主题

    att_excel = MIMEText(open(textFile_path, 'rb').read(),'base64', 'utf-8')
    att_excel["Content-Type"] = 'application/octet-stream'
    # fileName = textFile_path.split('/')[1]
    # print(fileName)
    # fileName = '20210707.csv'
    att_excel["Content-Disposition"] = 'attachment; filename=%s'%textFile_path
    msg.attach(att_excel)

    try:
        # 创建并登录SMTP服务器
        server = smtplib.SMTP_SSL(host, 465)  # 创建一个STMP对象，SSL加密(也可选择明文发送server = smtplib.SMTP(host, 25))
        server.login(sender, password)  # 登陆需要认证的SMTP服务器（发件人邮箱账号、密码）
        # 发送邮件
        server.sendmail(sender, receivers,
                        msg.as_string())  # 发件人邮箱账号、收件人邮箱账号、发送邮件内容，as_string()是将msg(MIMEText对象或者MIMEMultipart对象)变为str
        server.quit()  # 断开STMP服务器链接
        print('邮件发送成功！')
    except smtplib.SMTPException as e:
        print('Error: 邮件发送失败！', e)

#
# if __name__ == '__main__':
#     email('yansongwel@163.com', './20210705.result.csv')