import pymysql #Python3的mysql模塊，Python2 是mysqldb
import os
import datetime #定時(shí)發(fā)送，以及日期
import shutil #文件操作
import smtplib #郵件模塊
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import time
import xlwt #excel寫(xiě)入

def eMysql(mysql_host,mysql_port,mysql_user,mysql_password,mysql_db,sql):
 try:
  db = pymysql.connect(host=mysql_host,port=mysql_port,user=mysql_user, password=mysql_password, db=mysql_db,charset = 'utf8')#連接數(shù)據(jù)庫(kù)編碼注意是utf8，不然中文結(jié)果輸出會(huì)亂碼
  print("MYSQL CONNECTED.")# 連接數(shù)據(jù)庫(kù)
  cursor = db.cursor()# 使用cursor()方法獲取操作游標(biāo)
  cursor.execute(sql)# 執(zhí)行SQL語(yǔ)句
  print('SQL EXECUTED')
  results = cursor.fetchall()# 結(jié)果
  return results
  print('RESULTS EXECUTED')
  db.close() # 關(guān)閉數(shù)據(jù)庫(kù)連接
  print('MYSQL CLOSED')
 except:
  print('SQL FAILED')

def eWrite(fLocate,results,file_sheet,file_subject,style0):
 try:
  if os.path.exists(fLocate):
   os.remove(fLocate) # 如果文件存在，則刪除
  f = xlwt.Workbook(encoding='utf-8') #打開(kāi)excel文件
  fs = f.add_sheet(file_sheet) #sheet名
  subject = list(file_subject) #列表化
  for i in range(len(subject)): #找到日期列
   if '日期' in subject[i]:
    col_num=i
  for i in range(len(subject)): #sheet標(biāo)題
   fs.write(0, i, subject[i])

  for i in range(len(results)): #sheet數(shù)據(jù),日期列格式為date
   for j in range(len(results[0])):
    if j== col_num:
     fs.write(i + 1, j, results[i][j],style0)
    else:
     fs.write(i + 1, j, results[i][j])


  for i in range(10): #單元格寬度為
   fs.col(i).width=3333

  print("WRITE FINISHED")
  f.save(fLocate)
 except :
  print ("WRITE FAILED")

def eSend(sender,receiver,username,password,smtpserver,subject,e_content,file_path,file_name):
 try:
#郵件頭
  message = MIMEMultipart()
  message['From'] = sender#發(fā)送
  message['To'] = ",".join(receiver)#收件
  message['Subject'] = Header(subject, 'utf-8')
  message.attach(MIMEText(e_content, 'plain', 'utf-8'))# 郵件正文

# 構(gòu)造附件
  att1 = MIMEText(open(file_path+file_name,'rb').read(), 'base64', 'utf-8')
  att1["Content-Type"] = 'application/octet-stream'
  att1["Content-Disposition"] = "attachment;filename="+file_name
  message.attach(att1)

#執(zhí)行
  smtp = smtplib.SMTP()
  smtp.connect(smtpserver) #連接服務(wù)器
  smtp.login(username, password) #登錄
  smtp.sendmail(sender, receiver, message.as_string()) #發(fā)送
  smtp.quit()
  print("SEND")
 except:
  print("SEND FAILED")

while True:
#配置
 #__time_____
 ehour=5#定時(shí)小時(shí)
 emin=21#定時(shí)分鐘
 esec=41#定時(shí)秒
 current_time = time.localtime(time.time())  #當(dāng)前時(shí)間date
 cur_time = time.strftime('%H%M', time.localtime(time.time()))    #當(dāng)前時(shí)間str
 #__mysql_____
 mysql_host = mysql_host #登錄host
 mysql_port =mysql_port #登錄port
 mysql_user = mysql_user #登錄名
 mysql_password = mysql_password #登錄密碼
 mysql_db = mysql_db #數(shù)據(jù)庫(kù)
 sql = sql.encode('utf-8') #sql查詢語(yǔ)句編碼
 #__email_____
 sender = sender #發(fā)件人郵箱
 receiver = ['453032441@qq.com'] #收件人郵箱，可以多個(gè)（列表形式）群發(fā)
#
 username = username  #發(fā)件人姓名
 password = password #smtp密碼，qq是給你分配一串，163是自己設(shè)置
 smtpserver = smtpserver #郵箱服務(wù)器
 subject = "Hey,here's something interesting"  #郵件標(biāo)題
 e_content = '{0:^27}\n{1:^27}\n{2:^25}\n{3:^25}'.format('i','/ \\','(-----)','(--------)') #郵件正文 #郵件正文
 #__file_____
 file_path = "D:/" #文件位置
 file_name="shit.xls" #文件名
 fLocate = file_path + file_name  #文件路徑
 file_subject='Gave', 'you', 'a', 'piece', 'of', 'shit.' #sheet標(biāo)題
 file_sheet='ok' #sheet名
 style0=xlwt.XFStyle()
 style0.num_format_str='YYYY-MM-DD'
#操作
 if ((current_time.tm_hour == ehour) and (current_time.tm_min == emin) and (current_time.tm_sec == esec)):
  print ("START")
  results=eMysql(mysql_host,mysql_port,mysql_user,mysql_password,mysql_db,sql)
  eWrite(fLocate, results, file_sheet, file_subject,style0)
  eSend(sender, receiver, username, password, smtpserver, subject, e_content, file_path,file_name)
  print(cur_time)
 time.sleep(1)