#!/user/bin/python

#-*-coding: utf-8-*-

# 1.登錄數據庫查詢數據 2.將查詢的數據生成excel文件 3.xls文件通過郵件發送

# 導入模塊：pymysql模塊用于登錄數據庫等相關操作（python3），python2中使用mysqldb模塊,需自行安裝：pip3 install pymysql

#? ? ? ? ? ?xlwt用于生成excel文件,需自行安裝：pip3 istall xlwt

#? ? ? ? ? ?smtplib與email為python自帶模塊，用于郵件的發送

import datetime

import pymysql

import xlwt

import smtplib

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.mime.application import MIMEApplication

# 定義數據查詢日期

# datetime.timedelta 時間加減

# datetime.date.today()? ? ? ?2019-10-25

# tmf_day 本月第一天? ? ? ? ? 2019-10-01

# lme_day 上月最后一天? ? ? ? 2019-09-30

# lmf_day 上月第一天? ? ? ? ? 2019-09-01

# datetime.datetime.now()? ? ?2019-10-25 11:03:52

# today_zero 今天的零時? ? ? ?2019-10-25 00:00:00

# tmf_zero 本月的第一個零時? ?2019-10-01 00:00:00

# lme_minute 上月的最后一分鐘 2019-09-30 23：59：59

today = datetime.date.today()

tmf_day = today.replace(day=1)

lme_day = tmf_day - datetime.timedelta(days=1)

lmf_day = lme_day.replace(day=1)

now = datetime.datetime.now()

today_zero = now - datetime.timedelta(hours=now.hour, minutes=now.minute, seconds=now.second)

tmf_zero = today_zero.replace(day=1)

lme_minute = tmf_zero - datetime.timedelta(hours=0, minutes=0, seconds=1)

# 1.登錄數據庫

# myconn 定義連接數據庫語句

# pymysql.cursors.DictCursor會輸出字典格式，pymysql.cursors.Cursor默認情況下cursor方法返回的是BaseCursor類型對象，BaseCursor類型對象在>執行查詢后每條記錄的結果以列表(list)表示。

# query_sql 定義查詢語句

# mycursor.fetchmany(x)接收x個數據結果，mycursor.fetchall()接收所有數據結果。

# mycursor.close()查詢結束

# myconn.close()斷開連接

# 傳遞單個參數：field = '-'

#? ? ? ? ? ? ? ?sql_talk="UPDATE cnp.Test set a='' where b='%s'"

#? ? ? ? ? ? ? ?cursor.execute(sql_talk % field)

# 傳遞多個參數：使用{0}占位符

#? ? ? ? ? ? ? ?field = '-'

#? ? ? ? ? ? ? ?a = 'code'

#? ? ? ? ? ? ? ?sql_talk="UPDATE cnp.Test set {0}='' where business_registration_code='{1}'".format(a,field)

#? ? ? ? ? ? ? ?cursor.execute(sql_talk)

# 或者? ? ? ? ? ?

#? ? ? ? ? ? ? ?cur.execute("select score from class_info where sex='%s' and age=%d" % ('XX','XX')) %s表示字符，%d表示整型

myconn = pymysql.connect(host='192.168.42.128',user='XXXX',passwd='xxxx',database='test',charset="utf8")

mycursor = myconn.cursor(cursor=pymysql.cursors.Cursor)

query_sql = """

SELECT? conference_number,conference_title,conference_name,ccs_login_name,start_time,amount FROM `cost_08` where end_time between '%s' and '%s'

"""

mycursor.execute(query_sql % (lmf_day,lme_minute))

query_result=mycursor.fetchall()?

mycursor.close()

myconn.close()

# 2.生成excel文件

# excel的位置坐標為0開始，第一行第一列為（0,0），第一行第二列（0,1），第二行第一列（1,0）...

# list1 第一行，用于生成需要查詢的字段

# wbk.add_sheet創建worksheet，相當于excel中的的sheet1

# xlwt.XFStyle()初始化樣式

# datastyle.num_format_str 設置excel單元格格式

list1 = ('第一列','第二列','第三列','第四列','第五列','第六列')

wbk = xlwt.Workbook(encoding='utf-8')

worksheet = wbk.add_sheet('My worksheet')

datastyle = xlwt.XFStyle()

datastyle.num_format_str = 'yyyy-mm-dd hh:mm:ss'

# len(list1):list1的長度，用于表示list1的字段數

# list 查詢結果的數據列數，即需要查詢的字段數

# worksheet.write：將元素寫入對應的表格中

# worksheet.write(0,list,list1[list]):將list1的相應字段填入第一行，及需要查詢的字段。

# row 查詢結果的條數數，即共查詢到多少條記錄。（0,0）已填入數據，所以從（1,0）開始,row+1行結束。

# list循環到第四列時，設置excel單元格格式，其余列未設置格式，循環不變。

# worksheet.write(row+1,list,query_result[row][list])等同于:worksheet.write(row+1,0,query_result[row][0])

# 如查詢結果為('1,2','2,3','3,4')則:

# (1,0)結果為1? (1,1)結果為2? ? ? ? ? ?12

# (2,0)結果為2? (2,1)結果為3? ? ? ? ? ?23

# (3,0)結果為3? (3,1)結果為4? ? ? ? ? ?34

# wbk.save 保存文件，后跟文件名稱

??

for list in range (0,len(list1)):

? ? worksheet.write(0,list,list1[list])

? ? for row in range(0,len(query_result)):

? ? ? ? if list == 4:

? ?

? ? ? ? ? ?worksheet.write(row+1,4,query_result[row][4],datastyle)

? ??

? ? ? ? else:

? ? ? ? ? ??

? ? ? ? ? ?worksheet.write(row+1,list,query_result[row][list])

?

wbk.save('xx.xls')

# 3.定義發送郵件函數

# def send_email():? ?定義函數

# msg = MIMEMultipart()用于構建帶附件的實例

# msg["Subject"] 郵件主題

# msg["From"] = user 發件人

# msg["To"] = to 收件人

# part1 = MIMEText("你好,\n\n? ? ? ?頂育上月使用數據見附件。") 構建純文本文件

# msg.attach(part1) attach表示固定，用于構建郵件內容

# part2 = MIMEApplication(open(r'C:\Users\Administrator.USER-20190917HT\Desktop\頂育.xls','rb').read()) 構建附件1

# open(r'C:\Users\Administrator.USER-20190917HT\Desktop\頂育.xls','rb').read()? ?以二進制只讀方式打開xx文件

# 等同于? f = open('test.txt')

#? ? ? ? ? ?f.read()

# rb二進制只讀模式? 'r'讀模式、'w'寫模式、'a'追加模式、'b'二進制模式、'+'讀/寫模式。

# open(r'C:\)? ? r'是防止字符轉義的 如果路徑中出現'\t'的話 不加r的話\t就會被轉義 而加了'r'之后'\t'就能保留原有的樣子

# part2.add_header('Content-Disposition','attachment',filename='頂育.xls') 頭部消息,filename可以任意寫，寫什么名字，郵件中顯示什么名字

# smtplib.SMTP("smtp.139.com",timeout=30) 發件人郵箱中的SMTP服務器，默認端口是25

# s.login(user,pwd)登錄郵箱

# send_email()調用函數

def send_email():

? ? user = 'xxxxxx@139.com'

??

? ? pwd = 'xxxxxxx'

??

? ? to = 'xxxxxx@qq.com'

??

? ? msg = MIMEMultipart()

??

? ? msg["Subject"] = 'xxxx上月使用情況統計'

??

? ? msg["From"] = user

??

? ? msg["To"] = to

? ??

? ? part1 = MIMEText("你好,\n\n? ? ? ?xxxx上月使用數據見附件。")

? ??

? ? msg.attach(part1)

??

? ? part2 = MIMEApplication(open(r'C:\Users\Administrator.USER-20190917HT\Desktop\xx.xls','rb').read())

? ??

? ? part2.add_header('Content-Disposition','attachment',filename='xx.xls')

? ??

? ? msg.attach(part2)

? ??

? ? s = smtplib.SMTP("smtp.139.com",timeout=30)

? ? s.login(user,pwd)

? ? s.sendmail(user,to,msg.as_string())? ?

? ? s.close()

? ??send_email()

? ??