黑马程序员技术交流社区

标题: 【上海校区】将数据库数据入excel中并发送邮件 [打印本页]

作者: 梦缠绕的时候 时间: 2018-10-17 09:19
标题: 【上海校区】将数据库数据入excel中并发送邮件
我们有个政府项目，代码和数据库都在政府那边，我就开了一个后门，每天定时将数据库数据发到我们这边来，然后定时把这个文件再删除掉，在中秋这一天项目经理给我打电话问我说数据库数据的情况，并且让我们这边一个开发人员帮忙统计增量是多少，我就萌生了一个想发，过节都不让人过好，还想着公司的事情，于是自己就打算写一个功能，将数据统计出来，然后调用邮件服务器发送给项目经理
我是运维出身，python都是自学的，好多东西写的不是很规范，但是功能已经实现了，脚本内容如下：

#!/usr/bin/env python
#-*- coding:utf-8 -*-

import pymysql,time
import xlwt
import smtplib
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.multipart import MIMEMultipart
from email.header import Header

src="/home/email/data"

class writeExcel(object):
'''将数据库数据写入到excel中'''
def __init__(self):
      self.workbook = xlwt.Workbook()
def writefile(self,data,field):  #data为查查询结果,field为工作簿名
      sheet = self.workbook.add_sheet(field, cell_overwrite_ok=True)
      for field in range(0, len(fields)):
         sheet.write(0, field, fields[field][0])

      # 获取并写入数据段信息
      row = 1
      col = 0
      for row in range(1, len(data) + 1):
         for col in range(0, len(fields)):
            sheet.write(row, col, u'%s' % data[row - 1][col])

def savaDate(self):
      self.workbook.save(r'%s/%s.xlsx' %(src,time.strftime('%Y%m%d')))

def email(email_list,content,subject="企业数据%s" %time.strftime('%Y%m%d')):
'''邮件服务器'''
msg = MIMEMultipart() #创建一个带有附件的实例
msg['From'] = formataddr(["冠通新创", 'mail@gateon.cn']) #发件人显示的名称和地址
msg['Subject'] = Header(subject, 'utf-8')
msg.attach(MIMEText(content,'plain','utf-8')) #发送主体

# 构造附件
att1 = MIMEText(open('%s/%s.xlsx' % (src,time.strftime('%Y%m%d')), 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
att1["Content-Disposition"] = 'attachment; filename="%s.xlsx"' %time.strftime('%Y%m%d')
msg.attach(att1)

#SMTP服务区
server = smtplib.SMTP("smtp.qiye.163.com",25) #网易云的smtp服务器
server.login('mail@gateon.cn','1234567890') #登陆网易云的企业邮箱和密码
server.sendmail('mail@gateon.cn',email_list,msg.as_string())
server.quit()

#数据库连接信息
PY_MYSQL_CONN_DICT = {
"host": '127.0.0.1',
"port": 3306,
"user": 'root',
"passwd": '1234567890',
"db": 'food-user',
"charset": 'utf8'
}
conn = pymysql.connect(**PY_MYSQL_CONN_DICT) #连接数据库
cursor = conn.cursor()                      #创建游标

sqlOne="""
select DISTINCT a.companyname,c.username from `food-user`.companyinfo a
  INNER JOIN `product-company`.supplier b on a.id = b.company_id
  INNER JOIN `food-user`.`food_user` c on a.id = c.company_id
  where a.id not in (select companyid from `food-user`.testCompanyId)
  ORDER BY c.username asc
"""

sqlTwo="""
select DISTINCT a.companyname,c.username from `food-user`.companyinfo a
  INNER JOIN `currency-company`.supplier b on a.id=b.company_id
  INNER JOIN `food-user`.`food_user` c on a.id = c.company_id
  where a.id not in (select companyid from `food-user`.testCompanyId)
  ORDER BY c.username asc;
"""

sqlThree="""
select DISTINCT a.companyname,c.username from `food-user`.companyinfo a
  INNER JOIN `restaurant_company`.supplier b on a.id=b.companyid
  INNER JOIN `food-user`.`food_user` c on a.id = c.company_id
  where a.id not in (select companyid from `food-user`.testCompanyId)
  ORDER BY c.username asc;
"""
cursor.execute(sqlOne)
results = cursor.fetchall()

cursor.execute(sqlTwo)
results2=cursor.fetchall()

cursor.execute(sqlThree)
results3=cursor.fetchall()

# 获取MYSQL里面的数据字段名称
fields = cursor.description

#将数据写入到excel中
obj = writeExcel()
obj.writefile(results,'product')
obj.writefile(results2,'currency')
obj.writefile(results3,'restaurant')
obj.savaDate()

conn.commit()
cursor.close()
conn.close()

#发送邮件
email(['2454456664@qq.com'],'企业数据%s' %time.strftime('%Y%m%d'))

---------------------
作者：幸福丶如此
来源：CSDN
原文：https://blog.csdn.net/m0_3788642 ... 740?utm_source=copy
版权声明：本文为博主原创文章，转载请附上博文链接！

作者: 不二晨 时间: 2018-10-25 11:05

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2