Python 脚本mysql中读取数据完成报表发送

IT男也玩文艺 2020-05-19

424

#!/usr/bin/env python3

#coding: utf-8

import smtplib

import pymysql

import pandas as pd

import time

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.mime.image import MIMEImage

from email.header import Header

sender = 'changguohe@risingstarinfo.com' //发送者

receiver = 'xxx.com,xxx.com,xxx.com,xxx.com' //接受者

cpto = 'lijunpeng@risingstarinfo.com,changguohe@risingstarinfo.com' 抄送者

date=time.strftime("%Y-%m-%d", time.localtime()) /定义时间取服务器时间

subject = 'xxxx项目' +str(date)+'运营情况' //邮件标题

smtpserver = 'smtp.risingstarinfo.com'

username = 'changguohe@risingstarinfo.com' // 登录邮件服务器的用户名

password = 'xxxx' //登录邮件服务器密码

msgRoot = MIMEMultipart('related')

msgRoot['Subject'] = Header(subject, 'utf-8') //标题格式化

strTo = ('xxxxx.com','xxxxx.com') //格式化发件人信息

msgRoot['From'] = sender # 发件人也是被格式化过的

msgRoot['to'] = ','.join(strTo) # 这个一定要是一个str,不然会报错“AttributeError: 'list' object has no attribute 'lstrip'”

conn = pymysql.connect(host='xxxxxxx', user='xxxxx', passwd='xxxxxxxx', port=xxxxx, db='xxxxxx') // 数据库连接信息

sql="""select * from django_app_yuying where date_id = date_sub(curdate(),interval 1 day)"""

cur = conn.cursor()

cur.execute(sql)

rows = cur.fetchall()

for row in rows:

print(row[1])

row1=row[1]

row2=row[2]

row3=row[3]

row4=row[4]

row5=row[5]

row6=row[6]

row7=row[7]

row8=row[8]

row9=row[9] // 定义一个数据数组，从数据中查询出的元组进行元组遍历并赋值

sql2="""select * from brpt_yunying_tj where tj_date = date_sub(curdate(),interval 1 day) and channel_name = '手厅参与结算' and work_type_name = '装机' """

cur = conn.cursor()

cur.execute(sql2)

rows2 = cur.fetchall()

for row_n in rows2:

row_n1=row_n[3]

row_n2=row_n[4]

row_n3=row_n[5]

row_n4=row_n[6]

row_n5=row_n[7]

row_n6=row_n[8]

row_n7=row_n[9]

row_n8=row_n[10]

row_n9=row_n[11]

row_n10=row_n[12]

row_n11=row_n[13] // 定义一个数据数组，从数据中查询出的元组进行元组遍历并赋值

sql3="""select * from brpt_yunying_tj where tj_date = date_sub(curdate(),interval 1 day) and channel_name = '手厅不参与结算' and work_type_name = '装机' """

cur = conn.cursor()

cur.execute(sql3)

rows3 = cur.fetchall()

for row_b in rows3:

row_b1=row_b[3]

row_b2=row_b[4]

row_b3=row_b[5]

row_b4=row_b[6]

row_b5=row_b[7]

row_b6=row_b[8]

row_b7=row_b[9]

row_b8=row_b[10]

row_b9=row_b[11]

row_b10=row_b[12]

row_b11=row_b[13] // 定义一个数据数组，从数据中查询出的元组进行元组遍历并赋值

sql4="""select * from brpt_yunying_tj where tj_date = date_sub(curdate(),interval 1 day) and channel_name = '运营平台参与结算' and work_type_name = '装机' """

cur = conn.cursor()

cur.execute(sql4)

rows4 = cur.fetchall()

for row_c in rows4:

row_c1=row_c[3]

row_c2=row_c[4]

row_c3=row_c[5]

row_c4=row_c[6]

row_c5=row_c[7]

row_c6=row_c[8]

row_c7=row_c[9]

row_c8=row_c[10]

row_c9=row_c[11]

row_c10=row_c[12]

row_c11=row_c[13] // 定义一个数据数组，从数据中查询出的元组进行元组遍历并赋值

sql5="""select * from brpt_yunying_tj where tj_date = date_sub(curdate(),interval 1 day) and channel_name = '运营平台参与结算' and work_type_name = '移机' """

cur = conn.cursor()

cur.execute(sql5)

rows5 = cur.fetchall()

for row_d in rows5:

row_d1=row_d[3]

row_d2=row_d[4]

row_d3=row_d[5]

row_d4=row_d[6]

row_d5=row_d[7]

row_d6=row_d[8]

row_d7=row_d[9]

row_d8=row_d[10]

row_d9=row_d[11]

row_d10=row_d[12]

row_d11=row_d[13]

msgText = MIMEText("""

<html>

<head>

</head>

<tr>

<td align="center">月注册用户数</td>

<td align="center">月绑定宽带用户数</td>

<td align="center">月活跃用户数月活跃用户数</td>

<td align="center">当日用户注册数</td>

<td align="center">当日用户绑定数</td>

</tr>

<tr>

</tr>

</table>

<body>

<tr>

<td align="center">日商城订单数</td>

<td align="center">日商城订单金额</td>

<td align="center">月商城订单数</td>

<td align="center">月商城订单金额</td>

</tr>

<tr>

</tr>

</table>

<h3 align="center">结算单统计</h3>

<tr>

</tr>

<tr>

<td align="center">手厅参与结算</td>

</tr>

<tr>

<td align="center">手厅不参与结算</td>

</tr>

<tr>

<td align="center">运营平台参与结算</td>

</tr>

<tr>

<td align="center">运营平台参与结算</td>

</tr>

</table>

<body>

</body>

</html>""" % (row1,row2,row3,row4,row5,row6,row7,row8,row9,row_n1,row_n2,row_n3,row_n4,row_n5,row_n6,row_n7,row_n8,row_n9,row_n10,row_n11,row_b1,row_b2,row_b3,row_b4,row_b5,row_b6,row_b7,row_b8,row_b9,row_b10,row_b11,row_c1,row_c2,row_c3,row_c4,row_c5,row_c6,row_c7,row_c8,row_c9,row_c10,row_c11,row_d1,row_d2,row_d3,row_d4,row_d5,row_d6,row_d7,row_d8,row_d9,row_d10,row_d11),"HTML","uft-8")

// 给html 中的变量赋值

msgRoot.attach(msgText)

smtp = smtplib.SMTP()

smtp.connect('smtp.risingstarinfo.com')

smtp.login(username, password)

smtp.sendmail(sender, receiver.split(',') + cpto.split(','), msgRoot.as_string())

smtp.quit()

mysql python

文章转载自IT男也玩文艺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Python 脚本mysql中读取数据完成报表发送

评论