剛?cè)腴T(mén)python，發(fā)現(xiàn)確實(shí)是一個(gè)不錯(cuò)的語(yǔ)言。
成都創(chuàng)新互聯(lián)主營(yíng)恭城網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,app軟件開(kāi)發(fā),恭城h5微信小程序定制開(kāi)發(fā)搭建,恭城網(wǎng)站營(yíng)銷(xiāo)推廣歡迎恭城等地區(qū)企業(yè)咨詢
業(yè)務(wù)部門(mén)要求將將某一個(gè)數(shù)據(jù)庫(kù)中的表，定期發(fā)送到相關(guān)部門(mén)人員郵箱。
其實(shí)整個(gè)業(yè)務(wù)需求很簡(jiǎn)單，實(shí)現(xiàn)起來(lái)也不難。
但是由于剛?cè)腴T(mén)python，所以還是借鑒了不上網(wǎng)上的內(nèi)容，也得到了許多群友的提醒。
業(yè)務(wù)部門(mén)使用的是Postgresql數(shù)據(jù)庫(kù)，所以使用 了psyconpg2的模塊。
整個(gè)腳本分為三部分：
1.數(shù)據(jù)庫(kù)的連接及數(shù)據(jù)寫(xiě)入excel表中（整個(gè)對(duì)新手來(lái)說(shuō)，應(yīng)該是難點(diǎn)）
2.郵件的發(fā)送
3.生成excel文件的刪除
# coding: utf-8
import sys
import xlwt
import psycopg2
import datetime
import time
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
import os.path
#連接數(shù)據(jù)庫(kù)的參數(shù)：主機(jī)，用戶，密碼，端口及數(shù)據(jù)庫(kù)名稱
host = '192.168.1.77'
user = 'postgres'
pwd = 'postgres'
port = 5432
db = 'pytest'
sheet_name = 'report' + time.strftime("%Y-%m-%d")
filename = 'report_' + time.strftime("%Y-%m-%d" + "-" + "%H%M%S") + '.xls'
out_path = "d:/test/report_" + time.strftime("%Y-%m-%d" + "-" + "%H%M%S") + ".xls" #路徑文件名使用日期時(shí)間來(lái)命名，但是文件命名不支持冒號(hào)：所以去掉冒號(hào)
cur_path = 'd:/test'
print(out_path)
sql = 'select * from website;'
def export():
#數(shù)據(jù)庫(kù)連接
conn = psycopg2.connect(dbname=db, user=user, password=pwd, host=host, port=port)
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
count = cursor.rowcount
print("Select " + str(count) + " Records")
# cursor.scroll(0, mode='relative')
fields = cursor.description #數(shù)據(jù)表的標(biāo)題
workbook = xlwt.Workbook(encoding='utf-8') #創(chuàng)建excel文檔
sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True) #根據(jù)sheet_name創(chuàng)建excel文檔的sheet
for field in range(1, len(fields)): #寫(xiě)入數(shù)據(jù)表的文件頭
sheet.write(0, field, fields[field][0])
#逐行逐列的添加數(shù)據(jù)
for row in range(1, len(result) + 1):
for col in range(0, len(fields)):
sheet.write(row, col, u'%s'%result[row-1][col])
workbook.save(out_path) #按照out_path的格式和路徑保存excel表格
_user = "user1@abc.com"
_pwd = "123456."
areceiver = "user2@163.com"
acc = "user1@abc.com"
#如名字所示Multipart就是多個(gè)部分
msg = MIMEMultipart()
msg["Subject"] = u'[Data Select_' + time.strftime("%Y-%m-%d") + u']'
msg["From"] = _user
msg["To"] = areceiver
msg["Cc"] = acc
def send_email():
conn = psycopg2.connect(dbname=db, user=user, password=pwd, host=host, port=port)
cursor = conn.cursor()
cursor.execute(sql)
cursor.fetchall()
count = cursor.rowcount # summary rows number
# ----這是文字部分-----
content = '''Dear All, \n附件是每日統(tǒng)計(jì)情況，請(qǐng)查收！
總計(jì)結(jié)果數(shù)位：''' + str(count)
part = MIMEText(content, 'plain', 'utf-8')
msg.attach(part)
if count > 0:
#這是附件部分
# xls類(lèi)型附件
file_name = 'd:/test/' + filename
part = MIMEText(open(file_name, 'rb').read(), 'base64', 'gb2312')
part["Content-Type"] = 'application/octet-stream'
basename = os.path.basename(file_name)
# part["Content-Disposition"] = 'attachment; filename=%s' % basename.encode('utf-8')
part['Content-Disposition'] = 'attachment; filename=%s' % basename
# part.add_header('Content-Disposition', 'attachment', filename=('utf-8', basename))
msg.attach(part)
s = smtplib.SMTP('mail.ucinbox.com', timeout=120) #連接smtp郵件服務(wù)器
s.login(_user, _pwd)
s.sendmail(_user, areceiver.split(',') + acc.split(','), msg.as_string()) # send mail
print("Email send successfully")
s.close()
else:
print("nothing to send!")
#刪除生成的excel文件
# 之前使用的是將不同的excel放入不同的文件夾，所以寫(xiě)了遍歷刪除所有excel
def delete(path):
ls = os.listdir(cur_path)
for l in ls:
path_file = os.path.join(path,l) #取文件路徑
if os.path.isfile(path_file):
os.remove(path_file)
else:
for f in os.listdir(path_file):
path_file2 = os.path.join(path_file,f)
if os.path.isfile(path_file2):
os.remove(path_file2)
#調(diào)用函數(shù)
if __name__ == "__main__":
export()
send_email()
delete(cur_path)
重慶分公司
重慶分公司
