前沿拓展：

stream.sys

一般都放在C盤(pán)WINDOWNS的SYSTEM32里！

背景

由于運(yùn)維工作需要，經(jīng)常需要將一些數(shù)據(jù)從數(shù)據(jù)庫(kù)中導(dǎo)出，發(fā)送給運(yùn)營(yíng)和需求部門(mén)，天天去手動(dòng)查詢(xún)，又有點(diǎn)太費(fèi)時(shí)間了，于是研究學(xué)習(xí)了Python的基本功能，通過(guò)Python腳本和Linux 的crontab命令實(shí)現(xiàn)了每天自動(dòng)化的數(shù)據(jù)查詢(xún)和郵件發(fā)送。

代碼實(shí)現(xiàn)

定義了以下幾個(gè)代碼模塊實(shí)現(xiàn)了配置文件讀取、日志記錄、數(shù)據(jù)庫(kù)連接訪問(wèn)查詢(xún)、導(dǎo)出到xlsx和帶附件的郵件發(fā)送功能。1、demo.py 示例文件2、config.yml 配置文件3、common_log.py 實(shí)現(xiàn)日志記錄4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送

1、demo.py 示例# coding: utf-8
import common_db as mydb
import common_xlsx as my_xlsx
import common_log as mylog
import common_email as my_email

if __name__ == "__main__":
phone = '13********7'
sql = """ select orderno,orderAmount,actualAmount,phone order where phone= '{0}' """.format(phone)
# 查詢(xún)訂單
result = mydb.select_by_parameters(sql)
filename = "測(cè)試.xlsx"
sheet_name = '訂單查詢(xún)'
# 郵件接收人
receivers = "zhangsan@aliyun.com"
receivers_cc = "lisi@aliyun.com"
# 郵件抄送人
if len(result) > 0:
# 創(chuàng)建工作表
my_xlsx.create_xlsx(filename)
mylog.logger.info("創(chuàng)建工作表成功"+filename)
# 將查詢(xún)結(jié)果放入工作表
my_xlsx.create_sheet_in_xlsx(filename, sheet_name, result,0)
mylog.logger.info("創(chuàng)建工作簿成功"+sheet_name)
# 對(duì)工作簿求和
my_xlsx.sum_col_for_sheet(filename, sheet_name)
mylog.logger.info("求和匯總成功")
# 刪除空工作表
my_xlsx.delete_sheet_from_xlsx(filename,'Sheet')
# 發(fā)送郵件
my_email.to_send_email("測(cè)試查詢(xún)結(jié)果", receivers, receivers_cc, filename, "訂單查詢(xún)結(jié)果.xlsx")
mylog.logger.info("發(fā)送郵件成功")2、config.yml——配置文件mysql:
host: 192.168.x.x
port: 3306
username: xxxx
password: xxxx
database: xxxx
log:
log_path: D:\log
log_size: 8
log_num: 3
email:
**tp: **tp.126.com
# 發(fā)送方郵件地址
from: xxxx@aliyun.com
# 發(fā)送方授權(quán)碼
password: fdsafafdsafsa
3、common_log.py 實(shí)現(xiàn)日志記錄import logging.handlers
import logging
import yaml
import os
import sys

# 提供日志功能
class logger:
# 先讀取XML文件中的配置數(shù)據(jù)
# 由于config.xml放置在與當(dāng)前文件相同的目錄下，因此通過(guò) __file__ 來(lái)獲取XML文件的目錄，第二再拼接成絕對(duì)路徑
# 這里利用了lxml庫(kù)來(lái)解析XML
# root = etree.parse(os.path.join(os.path.dirname(__file__), 'config.xml')).getroot()

# 先讀取yml中的配置數(shù)據(jù)
# 由于
# 讀取日志文件保存路徑
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_log = result["log"]

logpath = config_log['log_path']
# root.find('logpath').text
# 讀取日志文件容量，轉(zhuǎn)換為字節(jié)
logsize = 1024*1024*int(config_log['log_size'])
# 讀取日志文件保存?zhèn)€數(shù)
lognum = int(config_log['log_num'])

# 日志文件名：由用例腳本的名稱(chēng)，結(jié)合日志保存路徑，得到日志文件的絕對(duì)路徑
logname = os.path.join(logpath, sys.argv[0].split('/')[-1].split('.')[0])+".log"

# 初始化logger
log = logging.getLogger()
# 日志格式，可以根據(jù)需要設(shè)置
fmt = logging.Formatter('[%(asctime)s][%(filename)s][line:%(lineno)d][%(levelname)s] %(message)s', '%Y-%m-%d %H:%M:%S')

# 日志輸出到文件，這里用到了上面獲取的日志名稱(chēng)，大小，保存?zhèn)€數(shù)
handle1 = logging.handlers.RotatingFileHandler(logname, maxBytes=logsize, backupCount=lognum)
handle1.setFormatter(fmt)
# 同時(shí)輸出到屏幕，便于實(shí)施觀察
handle2 = logging.StreamHandler(stream=sys.stdout)
handle2.setFormatter(fmt)
log.addHandler(handle1)
log.addHandler(handle2)

# 設(shè)置日志基本，這里設(shè)置為INFO，表示只有INFO級(jí)別及以上的會(huì)打印
log.setLevel(logging.INFO)

# 日志接口，用戶(hù)只需調(diào)用這里的接口即可，這里只**了INFO, WARNING, ERROR三個(gè)級(jí)別的日志，可根據(jù)需要定義更多接口
@clas**ethod
def info(cls, msg):
cls.log.info(msg)
return

@clas**ethod
def warning(cls, msg):
cls.log.warning(msg)
return

@clas**ethod
def error(cls, msg):
cls.log.error(msg)
return4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)import time
import pymysql
from common_log import *

# 連接數(shù)據(jù)庫(kù)
def get_connection():
_conn_status = True
_max_retries_count = 10 # 設(shè)置最大重試次數(shù)
_conn_retries_count = 0 # 初始重試次數(shù)
_conn_timeout = 3 # 連接超時(shí)時(shí)間為3秒
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_mysql = result["mysql"]
while _conn_status and _conn_retries_count <= _max_retries_count:
try:
connect = pymysql.connect(host=config_mysql['host'], user=config_mysql['username'],
password=config_mysql['password'], database=config_mysql['database'],
port=config_mysql['port'])
_conn_status = False # 如果conn成功則_status為設(shè)置為False則退出循環(huán)，返回db連接對(duì)象
logger.info("連接數(shù)據(jù)庫(kù)成功")
return connect
except Exception as e:
_conn_retries_count += 1
logger.info("第%s次連接數(shù)據(jù)庫(kù)失敗"%(_conn_retries_count))
logger.error(e)
time.sleep(3)
continue

# 查詢(xún)函數(shù)
def select_by_parameters(sql, params=None):
try:
connect = get_connection()
cursor = connect.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, params)
result = cursor.fetchall()
return result
except Exception as e:
logger.error("執(zhí)行查詢(xún)報(bào)錯(cuò)")
logger.info(sql)
logger.error(e)
finally:
try:
cursor.close()
except Exception as e:
logger.error("關(guān)閉游標(biāo)對(duì)象報(bào)錯(cuò)")
logger.error(e)
try:
connect.close()
except Exception as e:
print(e)
print("數(shù)據(jù)庫(kù)鏈接關(guān)閉異常")5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理import openpyxl
import os
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, colors
from openpyxl.utils import get_column_letter

# 定義邊框
thin_border = Border(left=Side(style='thin', color='FFFFFF'), right=Side(style='thin', color='FFFFFF'),
top=Side(style='thin', color='FFFFFF'), bottom=Side(style='thin', color='FFFFFF'))
# 居中對(duì)齊
alignment_center = Alignment(horizontal='center', vertical='center')
# 右對(duì)齊
alignment_right = Alignment(horizontal='center', vertical='center')

# 雙行填充
fill_double = PatternFill(fgColor='FFDCE6F1', fill_type='solid')
# 單行填充
fill_single = PatternFill(fgColor='FFB8CCE4', fill_type='solid')

# 表頭填充
fill_head = PatternFill(fgColor='FF366092', fill_type='solid')
font_head = Font(bold=True, color='FFFFFFFF')
# 1、創(chuàng)建xlsx腳本——在指定的filepath，創(chuàng)建指定的filename的xlsx文件
def create_xlsx(filename):
wb = openpyxl.Workbook()
wb.save(filename)

# 2、增加工作簿腳本——讀取指定路徑下的xlsx文件，在工作表第index位置增加一個(gè)工作簿，并將mysql查詢(xún)結(jié)果result寫(xiě)入到該工作簿
def create_sheet_in_xlsx(filename, sheet_name, result, index):
# 加載文件
wb = openpyxl.load_workbook(filename)
# 在指**置創(chuàng)建工作表
wb.create_sheet(sheet_name, index)
# 獲取新建的工作表
ws = wb[sheet_name]
j = 1
if len(result) > 0:
# 寫(xiě)表頭
for key, value in (result[0].items()):
ws.cell(1, j, format(key)).border = thin_border
# 定義對(duì)齊方式
ws.cell(1, j).alignment = alignment_center
# 字體 顏色為白色
ws.cell(1, j).font = font_head
# 填充
ws.cell(1, j).fill = fill_head
# 邊框
j = j + 1
ws.row_dimensions[1].height = 30
# 寫(xiě)數(shù)據(jù)
# 根據(jù)結(jié)果集行數(shù)量進(jìn)行循環(huán)
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i+2, j).fill = fill_double
else:
ws.cell(i+2, j).fill = fill_single
ws.cell(i + 2, j, value)
ws.cell(i + 2, j).border = thin_border
ws.cell(i + 2, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i+2, j).alignment = alignment_center
ws.cell(i+2, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i+2, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i+2, j).alignment = alignment_right
ws.cell(i+2, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
# 保存工作表
wb.save(filename)

# 3、刪除工作簿
def delete_sheet_from_xlsx(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
wb.remove_sheet(wb[sheet_name])
wb.save(filename)

# 4、往工作簿中追加行 將mysql的查詢(xún)結(jié)果追加到工作表sheet_name末尾
def add_result_to_sheet(filename,new_sheet_name,result):
wb = openpyxl.load_workbook(filename)
ws = wb[new_sheet_name]
# 獲取最大行
mr = ws.max_row
if len(result) > 0:
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i + mr, j).fill = fill_double
else:
ws.cell(i + mr, j).fill = fill_single
ws.cell(i + mr, j, value)
ws.cell(i + mr, j).border = thin_border
ws.cell(i + mr, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i + mr, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i + mr, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i + mr, j).alignment = alignment_right
ws.cell(i + mr, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
wb.save(filename)

# 5、刪除工作簿中最大行
def delete_max_row_from_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_rows(ws.max_row)
wb.save(filename)

# 6、刪除工作簿中指定列
def delete_col_from_sheet(filename,sheet_name,colno):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_cols(colno)
wb.save(filename)

# 7、對(duì)表格中金額和數(shù)量字段進(jìn)行求和
def sum_col_for_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
for col in list(ws.columns):
l = [c.value for c in col]
if '金額' in l[0] or '數(shù)量' in l[0]:
# 本列行的數(shù)量
row_size = len(col) + 1
# 本列的列號(hào)是
col_no = col[1].column
col_code = get_column_letter(col[1].column)
ws.cell(row_size, col_no, "=sum(" + str(col_code) + str(2) + ":" + str(col_code) + str(
row_size – 1) + ")").border = thin_border
ws.cell(row_size, col_no).font = font_head
# 填充
ws.cell(row_size, col_no).fill = fill_head
# 測(cè)試添加樣式
if '金額' in l[0]:
ws.cell(row_size, col_no).number_format = '#,##0.00'
ws.cell(row_size, col_no).alignment = alignment_right
if '數(shù)量' in l[0]:
ws.cell(row_size, col_no).number_format = '0'
ws.cell(row_size, col_no).alignment = alignment_center
ws.row_dimensions[col_no].height = 20
wb.save(filename)6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from **tplib import **TP_SSL
from common_log import *
import yaml

#
# file_Name是路徑名稱(chēng)加文件名和擴(kuò)展名；
# new_file_name是在郵件附件中顯示的名稱(chēng)
# receivers是收件人列表，中間逗號(hào)隔開(kāi)
# receivers_cc是抄送人列表
# mail_subject是郵件主題
def to_send_email(mail_subject,receivers,receivers_cc,file_name,new_file_name):
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_email = result["email"]
password = config_email['password']
msg = MIMEMultipart('related')
msgAlternative = MIMEMultipart('alternative')
msgAlternative.attach(MIMEText("<h1>見(jiàn)附件</h1> <br />", "html", "utf-8"))
msg.attach(msgAlternative)
# file_name 是指文件路徑加名稱(chēng)和擴(kuò)展名
file1 = MIMEText( open(file_name, 'rb').read(), 'base64', 'utf-8' )
file1["Content-Type"] = 'application/octet-stream'
# new_file_name是指郵件附件中顯示的名稱(chēng)
file1.add_header('Content-Disposition', 'attachment',filename=new_file_name)
msg.attach(file1)
msg['Subject'] = Header(mail_subject, 'utf-8').encode()
msg['From'] = config_email['from']
msg['To'] = receivers
msg['Cc'] = receivers_cc
try:
**tp = **TP_SSL(config_email['**tp'])
**tp.login(msg['From'], password)
**tp.sendmail(msg['From'], msg['To'].split(',') + msg['Cc'].split(','), msg.as_string())
logger.info("發(fā)送郵件成功,郵件接收人是:%s,郵件抄送人是:%s"%(receivers, receivers_cc))
except Exception as e:
logger.error("發(fā)送郵件出現(xiàn)錯(cuò)誤,郵件接收人是:%s,郵件抄送人是:%s" % (receivers, receivers_cc))
logger.error(e)
finally:
try:
**tp.quit()
except Exception as e:
logger.error(e)

拓展知識(shí)：

stream.sys

C:\windows\drivers :

如果用的是GHOST版的XP的話(huà),在C:\windows\下面有個(gè)driver的文件夾,里面是集成驅(qū)動(dòng)安裝文件,是方便不同機(jī)器裝機(jī)時(shí)自動(dòng)安裝驅(qū)動(dòng)的,這個(gè)是可以刪除的
,集成驅(qū)動(dòng)文件,都被放在Drivers里,安裝完后為了節(jié)省空間,可刪除.
C:\Drivers可以刪
C:\windows\Drivers可以刪
C:\windows\system\Drivers可以刪
C:\windows\system32\Drivers絕對(duì)不能刪

C:\WINDOWS\SYSTEM32\drivers :

這個(gè)文件夾是系統(tǒng)非常重要的文件夾,包含著系統(tǒng)的很多驅(qū)動(dòng)程序 絕對(duì)不能刪
文件夾下常見(jiàn)文件說(shuō)明:
acpiec.sys …..ACPI 嵌入控制器驅(qū)動(dòng)
amdk6.sys ……AMD K6 CPU 驅(qū)動(dòng)
amdk7.sys …… AMD K7 CPU 驅(qū)動(dòng)
arp1394.sys ….IP/1394 Arp 客戶(hù)端
asyncmac.sys …MS 遠(yuǎn)程訪問(wèn)串行網(wǎng)絡(luò)驅(qū)動(dòng)
atmarpc.sys ….IP/ATM Arp 客戶(hù)端
atmepvc.sys ….ATM 網(wǎng)絡(luò)封包媒介驅(qū)動(dòng)
atmlane.sys ….ATM 局域網(wǎng)仿真驅(qū)動(dòng)
atmuni.sys …..ATM UNI 調(diào)用管理器
bridge.sys …..MAC 網(wǎng)橋驅(qū)動(dòng)
cbidf2k.sys ….CardBus/PCMCIA IDE **端口 驅(qū)動(dòng)
cinemst2.sys …CineMaster C 1.2 WDM 主驅(qū)動(dòng)
cpqdap01.sys …Compaq PA-1 播放器驅(qū)動(dòng)
crusoe.sys ….. Crusoe CPU 驅(qū)動(dòng)
cxthsfS2.cty …CTY 文件
diskdump.sys …崩潰轉(zhuǎn)儲(chǔ)磁盤(pán)驅(qū)動(dòng)
dmboot.sys …..NT 磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
dmio.sys …….NT磁盤(pán)管理器輸入輸出驅(qū)動(dòng)
dmload.sys …..NT磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
fastfat.sys ….快速 FAT 文件系統(tǒng)驅(qū)動(dòng)
fsvga.sys ……全屏視頻驅(qū)動(dòng)
gm.dls ………DLS 文件 (MIDI 軟波表)
hidclass.sys …Hid 類(lèi)型庫(kù)
hidparse.sys …Hid 分析庫(kù)
HSFBS2S2.sys …HSF_HWB2 WDM 驅(qū)動(dòng)
HSFCXTS2.sys …HSF_CNXT 驅(qū)動(dòng)
HSFDPSP2.sys …HSF_DP 驅(qū)動(dòng)
HSFProf.cty ….CTY 文件
http.sys …….HTTP 協(xié)議堆棧
imagedrv.sys …NERO 鏡像驅(qū)動(dòng)器 SCSI **端口
imagesrv.sys …Nero 鏡像服務(wù)器
irenum.sys …..**線(xiàn)總線(xiàn)枚舉器
mcd.sys ……..中型 changer 類(lèi)驅(qū)動(dòng)
mf.sys ………多功能端口枚舉器
mrxdav.sys …..Windows NT WebDav Minirdr
mrx**b.sys …..Windows NT WebDav Minirdr
MSKSSRV.sys ….MS KS 服務(wù)器
MSPCLOCK.sys …MS **時(shí)鐘
MSPQM.sys ……MS **質(zhì)量管理器
ndisuio.sys ….NDIS 用戶(hù)模式輸入輸出驅(qū)動(dòng)
netbios.sys ….NetBIOS 接口驅(qū)動(dòng)
nic1394.sys ….IEEE1394 Ndis **端口和調(diào)用管理器
nikedrv.sys ….NikeDrv U** 驅(qū)動(dòng)
nmnt.sys …….Netmon NT 驅(qū)動(dòng)
nwlnkipx.sys …NWLINK2 IPX 協(xié)議驅(qū)動(dòng)
nwlnknb.sys ….NWLINK2 IPX Netbios 協(xié)議驅(qū)動(dòng)
nwlnkspx.sys …NWLINK2 SPX 協(xié)議驅(qū)動(dòng)
oprghdlr.sys …ACPI **作注冊(cè)驅(qū)動(dòng)
p3.sys ………P3 CPU 驅(qū)動(dòng)
pcmcia.sys …..PCMCIA 總線(xiàn)驅(qū)動(dòng)
processr.sys …通用處理器設(shè)備驅(qū)動(dòng)
psched.sys …..MS QoS 數(shù)據(jù)包計(jì)劃程序
rawwan.sys …..Raw **局域網(wǎng)傳輸驅(qū)動(dòng)
rdbss.sys ……重定向的驅(qū)動(dòng)器緩沖子系統(tǒng)驅(qū)動(dòng)
rio8drv.sys ….Rio8Drv.sys U** 驅(qū)動(dòng) (Rio U** MP3)
riodrv.sys …..RioDrv U** 驅(qū)動(dòng)(Rio U** MP3)
RMCast.sys …..可靠的多址廣播傳輸
rndi**p.sys ….遠(yuǎn)程 NDIS **端口
rootmdm.sys ….舊式非即插即用調(diào)制解調(diào)器設(shè)備驅(qū)動(dòng)
scsiport.sys …SCSI 端口驅(qū)動(dòng)
sdbus.sys ……安全數(shù)碼總線(xiàn)驅(qū)動(dòng)
secdrv.sys …..系統(tǒng)文件
sffdisk.sys ….**all Form Factor 磁盤(pán)驅(qū)動(dòng)
sffp_sd.sys ….**all Form Factor SD 協(xié)議驅(qū)動(dòng)
**clib.sys …..智能卡驅(qū)動(dòng)庫(kù)
sonydcam.sys …1394 桌面攝像頭驅(qū)動(dòng)
srv.sys ……..服務(wù)驅(qū)動(dòng)
stream.sys …..WDM 編解碼類(lèi)設(shè)備驅(qū)動(dòng) 2.0
tape.sys …….SCSI 磁帶類(lèi)設(shè)備驅(qū)動(dòng)
tos**d.sys …..東芝 **D解壓卡驅(qū)動(dòng)驅(qū)動(dòng)
t**vcap.sys ….WDM 東芝視頻捕獲驅(qū)動(dòng)
tunmp.sys ……Microsoft隧道接口驅(qū)動(dòng)
u**8023.sys ….遠(yuǎn)程 NDIS U** 驅(qū)動(dòng)
u**camd.sys ….U** 攝像頭驅(qū)動(dòng)
u**camd2.sys …U** 攝像頭驅(qū)動(dòng)
u**intel.sys …Intel U** 攝像頭驅(qū)動(dòng)
vdmin**d.sys …CineMaster C WDM **D **驅(qū)動(dòng)
wpdu**.sys …..WPD U** 驅(qū)動(dòng)
ws2ifsl.sys ….Winsock2 IFS 層

本回答被提問(wèn)者采納

stream.sys

system32下的drivers是WINXP自帶的驅(qū)動(dòng)
c盤(pán)根目錄下的drivers是你用還原系統(tǒng)安裝時(shí),還原系統(tǒng)自帶的驅(qū)動(dòng),電腦安裝完成后,此文件夾可以刪除.

stream.sys

沒(méi)什么區(qū)別，都不要亂碰

前沿拓展：

stream.sys

一般都放在C盤(pán)WINDOWNS的SYSTEM32里！

背景

由于運(yùn)維工作需要，經(jīng)常需要將一些數(shù)據(jù)從數(shù)據(jù)庫(kù)中導(dǎo)出，發(fā)送給運(yùn)營(yíng)和需求部門(mén)，天天去手動(dòng)查詢(xún)，又有點(diǎn)太費(fèi)時(shí)間了，于是研究學(xué)習(xí)了Python的基本功能，通過(guò)Python腳本和Linux 的crontab命令實(shí)現(xiàn)了每天自動(dòng)化的數(shù)據(jù)查詢(xún)和郵件發(fā)送。

代碼實(shí)現(xiàn)

定義了以下幾個(gè)代碼模塊實(shí)現(xiàn)了配置文件讀取、日志記錄、數(shù)據(jù)庫(kù)連接訪問(wèn)查詢(xún)、導(dǎo)出到xlsx和帶附件的郵件發(fā)送功能。1、demo.py 示例文件2、config.yml 配置文件3、common_log.py 實(shí)現(xiàn)日志記錄4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送

1、demo.py 示例# coding: utf-8
import common_db as mydb
import common_xlsx as my_xlsx
import common_log as mylog
import common_email as my_email

if __name__ == "__main__":
phone = '13********7'
sql = """ select orderno,orderAmount,actualAmount,phone order where phone= '{0}' """.format(phone)
# 查詢(xún)訂單
result = mydb.select_by_parameters(sql)
filename = "測(cè)試.xlsx"
sheet_name = '訂單查詢(xún)'
# 郵件接收人
receivers = "zhangsan@aliyun.com"
receivers_cc = "lisi@aliyun.com"
# 郵件抄送人
if len(result) > 0:
# 創(chuàng)建工作表
my_xlsx.create_xlsx(filename)
mylog.logger.info("創(chuàng)建工作表成功"+filename)
# 將查詢(xún)結(jié)果放入工作表
my_xlsx.create_sheet_in_xlsx(filename, sheet_name, result,0)
mylog.logger.info("創(chuàng)建工作簿成功"+sheet_name)
# 對(duì)工作簿求和
my_xlsx.sum_col_for_sheet(filename, sheet_name)
mylog.logger.info("求和匯總成功")
# 刪除空工作表
my_xlsx.delete_sheet_from_xlsx(filename,'Sheet')
# 發(fā)送郵件
my_email.to_send_email("測(cè)試查詢(xún)結(jié)果", receivers, receivers_cc, filename, "訂單查詢(xún)結(jié)果.xlsx")
mylog.logger.info("發(fā)送郵件成功")2、config.yml——配置文件mysql:
host: 192.168.x.x
port: 3306
username: xxxx
password: xxxx
database: xxxx
log:
log_path: D:\log
log_size: 8
log_num: 3
email:
**tp: **tp.126.com
# 發(fā)送方郵件地址
from: xxxx@aliyun.com
# 發(fā)送方授權(quán)碼
password: fdsafafdsafsa
3、common_log.py 實(shí)現(xiàn)日志記錄import logging.handlers
import logging
import yaml
import os
import sys

# 提供日志功能
class logger:
# 先讀取XML文件中的配置數(shù)據(jù)
# 由于config.xml放置在與當(dāng)前文件相同的目錄下，因此通過(guò) __file__ 來(lái)獲取XML文件的目錄，第二再拼接成絕對(duì)路徑
# 這里利用了lxml庫(kù)來(lái)解析XML
# root = etree.parse(os.path.join(os.path.dirname(__file__), 'config.xml')).getroot()

# 先讀取yml中的配置數(shù)據(jù)
# 由于
# 讀取日志文件保存路徑
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_log = result["log"]

logpath = config_log['log_path']
# root.find('logpath').text
# 讀取日志文件容量，轉(zhuǎn)換為字節(jié)
logsize = 1024*1024*int(config_log['log_size'])
# 讀取日志文件保存?zhèn)€數(shù)
lognum = int(config_log['log_num'])

# 日志文件名：由用例腳本的名稱(chēng)，結(jié)合日志保存路徑，得到日志文件的絕對(duì)路徑
logname = os.path.join(logpath, sys.argv[0].split('/')[-1].split('.')[0])+".log"

# 初始化logger
log = logging.getLogger()
# 日志格式，可以根據(jù)需要設(shè)置
fmt = logging.Formatter('[%(asctime)s][%(filename)s][line:%(lineno)d][%(levelname)s] %(message)s', '%Y-%m-%d %H:%M:%S')

# 日志輸出到文件，這里用到了上面獲取的日志名稱(chēng)，大小，保存?zhèn)€數(shù)
handle1 = logging.handlers.RotatingFileHandler(logname, maxBytes=logsize, backupCount=lognum)
handle1.setFormatter(fmt)
# 同時(shí)輸出到屏幕，便于實(shí)施觀察
handle2 = logging.StreamHandler(stream=sys.stdout)
handle2.setFormatter(fmt)
log.addHandler(handle1)
log.addHandler(handle2)

# 設(shè)置日志基本，這里設(shè)置為INFO，表示只有INFO級(jí)別及以上的會(huì)打印
log.setLevel(logging.INFO)

# 日志接口，用戶(hù)只需調(diào)用這里的接口即可，這里只**了INFO, WARNING, ERROR三個(gè)級(jí)別的日志，可根據(jù)需要定義更多接口
@clas**ethod
def info(cls, msg):
cls.log.info(msg)
return

@clas**ethod
def warning(cls, msg):
cls.log.warning(msg)
return

@clas**ethod
def error(cls, msg):
cls.log.error(msg)
return4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)import time
import pymysql
from common_log import *

# 連接數(shù)據(jù)庫(kù)
def get_connection():
_conn_status = True
_max_retries_count = 10 # 設(shè)置最大重試次數(shù)
_conn_retries_count = 0 # 初始重試次數(shù)
_conn_timeout = 3 # 連接超時(shí)時(shí)間為3秒
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_mysql = result["mysql"]
while _conn_status and _conn_retries_count <= _max_retries_count:
try:
connect = pymysql.connect(host=config_mysql['host'], user=config_mysql['username'],
password=config_mysql['password'], database=config_mysql['database'],
port=config_mysql['port'])
_conn_status = False # 如果conn成功則_status為設(shè)置為False則退出循環(huán)，返回db連接對(duì)象
logger.info("連接數(shù)據(jù)庫(kù)成功")
return connect
except Exception as e:
_conn_retries_count += 1
logger.info("第%s次連接數(shù)據(jù)庫(kù)失敗"%(_conn_retries_count))
logger.error(e)
time.sleep(3)
continue

# 查詢(xún)函數(shù)
def select_by_parameters(sql, params=None):
try:
connect = get_connection()
cursor = connect.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, params)
result = cursor.fetchall()
return result
except Exception as e:
logger.error("執(zhí)行查詢(xún)報(bào)錯(cuò)")
logger.info(sql)
logger.error(e)
finally:
try:
cursor.close()
except Exception as e:
logger.error("關(guān)閉游標(biāo)對(duì)象報(bào)錯(cuò)")
logger.error(e)
try:
connect.close()
except Exception as e:
print(e)
print("數(shù)據(jù)庫(kù)鏈接關(guān)閉異常")5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理import openpyxl
import os
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, colors
from openpyxl.utils import get_column_letter

# 定義邊框
thin_border = Border(left=Side(style='thin', color='FFFFFF'), right=Side(style='thin', color='FFFFFF'),
top=Side(style='thin', color='FFFFFF'), bottom=Side(style='thin', color='FFFFFF'))
# 居中對(duì)齊
alignment_center = Alignment(horizontal='center', vertical='center')
# 右對(duì)齊
alignment_right = Alignment(horizontal='center', vertical='center')

# 雙行填充
fill_double = PatternFill(fgColor='FFDCE6F1', fill_type='solid')
# 單行填充
fill_single = PatternFill(fgColor='FFB8CCE4', fill_type='solid')

# 表頭填充
fill_head = PatternFill(fgColor='FF366092', fill_type='solid')
font_head = Font(bold=True, color='FFFFFFFF')
# 1、創(chuàng)建xlsx腳本——在指定的filepath，創(chuàng)建指定的filename的xlsx文件
def create_xlsx(filename):
wb = openpyxl.Workbook()
wb.save(filename)

# 2、增加工作簿腳本——讀取指定路徑下的xlsx文件，在工作表第index位置增加一個(gè)工作簿，并將mysql查詢(xún)結(jié)果result寫(xiě)入到該工作簿
def create_sheet_in_xlsx(filename, sheet_name, result, index):
# 加載文件
wb = openpyxl.load_workbook(filename)
# 在指**置創(chuàng)建工作表
wb.create_sheet(sheet_name, index)
# 獲取新建的工作表
ws = wb[sheet_name]
j = 1
if len(result) > 0:
# 寫(xiě)表頭
for key, value in (result[0].items()):
ws.cell(1, j, format(key)).border = thin_border
# 定義對(duì)齊方式
ws.cell(1, j).alignment = alignment_center
# 字體 顏色為白色
ws.cell(1, j).font = font_head
# 填充
ws.cell(1, j).fill = fill_head
# 邊框
j = j + 1
ws.row_dimensions[1].height = 30
# 寫(xiě)數(shù)據(jù)
# 根據(jù)結(jié)果集行數(shù)量進(jìn)行循環(huán)
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i+2, j).fill = fill_double
else:
ws.cell(i+2, j).fill = fill_single
ws.cell(i + 2, j, value)
ws.cell(i + 2, j).border = thin_border
ws.cell(i + 2, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i+2, j).alignment = alignment_center
ws.cell(i+2, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i+2, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i+2, j).alignment = alignment_right
ws.cell(i+2, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
# 保存工作表
wb.save(filename)

# 3、刪除工作簿
def delete_sheet_from_xlsx(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
wb.remove_sheet(wb[sheet_name])
wb.save(filename)

# 4、往工作簿中追加行 將mysql的查詢(xún)結(jié)果追加到工作表sheet_name末尾
def add_result_to_sheet(filename,new_sheet_name,result):
wb = openpyxl.load_workbook(filename)
ws = wb[new_sheet_name]
# 獲取最大行
mr = ws.max_row
if len(result) > 0:
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i + mr, j).fill = fill_double
else:
ws.cell(i + mr, j).fill = fill_single
ws.cell(i + mr, j, value)
ws.cell(i + mr, j).border = thin_border
ws.cell(i + mr, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i + mr, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i + mr, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i + mr, j).alignment = alignment_right
ws.cell(i + mr, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
wb.save(filename)

# 5、刪除工作簿中最大行
def delete_max_row_from_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_rows(ws.max_row)
wb.save(filename)

# 6、刪除工作簿中指定列
def delete_col_from_sheet(filename,sheet_name,colno):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_cols(colno)
wb.save(filename)

# 7、對(duì)表格中金額和數(shù)量字段進(jìn)行求和
def sum_col_for_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
for col in list(ws.columns):
l = [c.value for c in col]
if '金額' in l[0] or '數(shù)量' in l[0]:
# 本列行的數(shù)量
row_size = len(col) + 1
# 本列的列號(hào)是
col_no = col[1].column
col_code = get_column_letter(col[1].column)
ws.cell(row_size, col_no, "=sum(" + str(col_code) + str(2) + ":" + str(col_code) + str(
row_size – 1) + ")").border = thin_border
ws.cell(row_size, col_no).font = font_head
# 填充
ws.cell(row_size, col_no).fill = fill_head
# 測(cè)試添加樣式
if '金額' in l[0]:
ws.cell(row_size, col_no).number_format = '#,##0.00'
ws.cell(row_size, col_no).alignment = alignment_right
if '數(shù)量' in l[0]:
ws.cell(row_size, col_no).number_format = '0'
ws.cell(row_size, col_no).alignment = alignment_center
ws.row_dimensions[col_no].height = 20
wb.save(filename)6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from **tplib import **TP_SSL
from common_log import *
import yaml

#
# file_Name是路徑名稱(chēng)加文件名和擴(kuò)展名；
# new_file_name是在郵件附件中顯示的名稱(chēng)
# receivers是收件人列表，中間逗號(hào)隔開(kāi)
# receivers_cc是抄送人列表
# mail_subject是郵件主題
def to_send_email(mail_subject,receivers,receivers_cc,file_name,new_file_name):
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_email = result["email"]
password = config_email['password']
msg = MIMEMultipart('related')
msgAlternative = MIMEMultipart('alternative')
msgAlternative.attach(MIMEText("<h1>見(jiàn)附件</h1> <br />", "html", "utf-8"))
msg.attach(msgAlternative)
# file_name 是指文件路徑加名稱(chēng)和擴(kuò)展名
file1 = MIMEText( open(file_name, 'rb').read(), 'base64', 'utf-8' )
file1["Content-Type"] = 'application/octet-stream'
# new_file_name是指郵件附件中顯示的名稱(chēng)
file1.add_header('Content-Disposition', 'attachment',filename=new_file_name)
msg.attach(file1)
msg['Subject'] = Header(mail_subject, 'utf-8').encode()
msg['From'] = config_email['from']
msg['To'] = receivers
msg['Cc'] = receivers_cc
try:
**tp = **TP_SSL(config_email['**tp'])
**tp.login(msg['From'], password)
**tp.sendmail(msg['From'], msg['To'].split(',') + msg['Cc'].split(','), msg.as_string())
logger.info("發(fā)送郵件成功,郵件接收人是:%s,郵件抄送人是:%s"%(receivers, receivers_cc))
except Exception as e:
logger.error("發(fā)送郵件出現(xiàn)錯(cuò)誤,郵件接收人是:%s,郵件抄送人是:%s" % (receivers, receivers_cc))
logger.error(e)
finally:
try:
**tp.quit()
except Exception as e:
logger.error(e)

拓展知識(shí)：

stream.sys

C:\windows\drivers :

如果用的是GHOST版的XP的話(huà),在C:\windows\下面有個(gè)driver的文件夾,里面是集成驅(qū)動(dòng)安裝文件,是方便不同機(jī)器裝機(jī)時(shí)自動(dòng)安裝驅(qū)動(dòng)的,這個(gè)是可以刪除的
,集成驅(qū)動(dòng)文件,都被放在Drivers里,安裝完后為了節(jié)省空間,可刪除.
C:\Drivers可以刪
C:\windows\Drivers可以刪
C:\windows\system\Drivers可以刪
C:\windows\system32\Drivers絕對(duì)不能刪

C:\WINDOWS\SYSTEM32\drivers :

這個(gè)文件夾是系統(tǒng)非常重要的文件夾,包含著系統(tǒng)的很多驅(qū)動(dòng)程序 絕對(duì)不能刪
文件夾下常見(jiàn)文件說(shuō)明:
acpiec.sys …..ACPI 嵌入控制器驅(qū)動(dòng)
amdk6.sys ……AMD K6 CPU 驅(qū)動(dòng)
amdk7.sys …… AMD K7 CPU 驅(qū)動(dòng)
arp1394.sys ….IP/1394 Arp 客戶(hù)端
asyncmac.sys …MS 遠(yuǎn)程訪問(wèn)串行網(wǎng)絡(luò)驅(qū)動(dòng)
atmarpc.sys ….IP/ATM Arp 客戶(hù)端
atmepvc.sys ….ATM 網(wǎng)絡(luò)封包媒介驅(qū)動(dòng)
atmlane.sys ….ATM 局域網(wǎng)仿真驅(qū)動(dòng)
atmuni.sys …..ATM UNI 調(diào)用管理器
bridge.sys …..MAC 網(wǎng)橋驅(qū)動(dòng)
cbidf2k.sys ….CardBus/PCMCIA IDE **端口 驅(qū)動(dòng)
cinemst2.sys …CineMaster C 1.2 WDM 主驅(qū)動(dòng)
cpqdap01.sys …Compaq PA-1 播放器驅(qū)動(dòng)
crusoe.sys ….. Crusoe CPU 驅(qū)動(dòng)
cxthsfS2.cty …CTY 文件
diskdump.sys …崩潰轉(zhuǎn)儲(chǔ)磁盤(pán)驅(qū)動(dòng)
dmboot.sys …..NT 磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
dmio.sys …….NT磁盤(pán)管理器輸入輸出驅(qū)動(dòng)
dmload.sys …..NT磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
fastfat.sys ….快速 FAT 文件系統(tǒng)驅(qū)動(dòng)
fsvga.sys ……全屏視頻驅(qū)動(dòng)
gm.dls ………DLS 文件 (MIDI 軟波表)
hidclass.sys …Hid 類(lèi)型庫(kù)
hidparse.sys …Hid 分析庫(kù)
HSFBS2S2.sys …HSF_HWB2 WDM 驅(qū)動(dòng)
HSFCXTS2.sys …HSF_CNXT 驅(qū)動(dòng)
HSFDPSP2.sys …HSF_DP 驅(qū)動(dòng)
HSFProf.cty ….CTY 文件
http.sys …….HTTP 協(xié)議堆棧
imagedrv.sys …NERO 鏡像驅(qū)動(dòng)器 SCSI **端口
imagesrv.sys …Nero 鏡像服務(wù)器
irenum.sys …..**線(xiàn)總線(xiàn)枚舉器
mcd.sys ……..中型 changer 類(lèi)驅(qū)動(dòng)
mf.sys ………多功能端口枚舉器
mrxdav.sys …..Windows NT WebDav Minirdr
mrx**b.sys …..Windows NT WebDav Minirdr
MSKSSRV.sys ….MS KS 服務(wù)器
MSPCLOCK.sys …MS **時(shí)鐘
MSPQM.sys ……MS **質(zhì)量管理器
ndisuio.sys ….NDIS 用戶(hù)模式輸入輸出驅(qū)動(dòng)
netbios.sys ….NetBIOS 接口驅(qū)動(dòng)
nic1394.sys ….IEEE1394 Ndis **端口和調(diào)用管理器
nikedrv.sys ….NikeDrv U** 驅(qū)動(dòng)
nmnt.sys …….Netmon NT 驅(qū)動(dòng)
nwlnkipx.sys …NWLINK2 IPX 協(xié)議驅(qū)動(dòng)
nwlnknb.sys ….NWLINK2 IPX Netbios 協(xié)議驅(qū)動(dòng)
nwlnkspx.sys …NWLINK2 SPX 協(xié)議驅(qū)動(dòng)
oprghdlr.sys …ACPI **作注冊(cè)驅(qū)動(dòng)
p3.sys ………P3 CPU 驅(qū)動(dòng)
pcmcia.sys …..PCMCIA 總線(xiàn)驅(qū)動(dòng)
processr.sys …通用處理器設(shè)備驅(qū)動(dòng)
psched.sys …..MS QoS 數(shù)據(jù)包計(jì)劃程序
rawwan.sys …..Raw **局域網(wǎng)傳輸驅(qū)動(dòng)
rdbss.sys ……重定向的驅(qū)動(dòng)器緩沖子系統(tǒng)驅(qū)動(dòng)
rio8drv.sys ….Rio8Drv.sys U** 驅(qū)動(dòng) (Rio U** MP3)
riodrv.sys …..RioDrv U** 驅(qū)動(dòng)(Rio U** MP3)
RMCast.sys …..可靠的多址廣播傳輸
rndi**p.sys ….遠(yuǎn)程 NDIS **端口
rootmdm.sys ….舊式非即插即用調(diào)制解調(diào)器設(shè)備驅(qū)動(dòng)
scsiport.sys …SCSI 端口驅(qū)動(dòng)
sdbus.sys ……安全數(shù)碼總線(xiàn)驅(qū)動(dòng)
secdrv.sys …..系統(tǒng)文件
sffdisk.sys ….**all Form Factor 磁盤(pán)驅(qū)動(dòng)
sffp_sd.sys ….**all Form Factor SD 協(xié)議驅(qū)動(dòng)
**clib.sys …..智能卡驅(qū)動(dòng)庫(kù)
sonydcam.sys …1394 桌面攝像頭驅(qū)動(dòng)
srv.sys ……..服務(wù)驅(qū)動(dòng)
stream.sys …..WDM 編解碼類(lèi)設(shè)備驅(qū)動(dòng) 2.0
tape.sys …….SCSI 磁帶類(lèi)設(shè)備驅(qū)動(dòng)
tos**d.sys …..東芝 **D解壓卡驅(qū)動(dòng)驅(qū)動(dòng)
t**vcap.sys ….WDM 東芝視頻捕獲驅(qū)動(dòng)
tunmp.sys ……Microsoft隧道接口驅(qū)動(dòng)
u**8023.sys ….遠(yuǎn)程 NDIS U** 驅(qū)動(dòng)
u**camd.sys ….U** 攝像頭驅(qū)動(dòng)
u**camd2.sys …U** 攝像頭驅(qū)動(dòng)
u**intel.sys …Intel U** 攝像頭驅(qū)動(dòng)
vdmin**d.sys …CineMaster C WDM **D **驅(qū)動(dòng)
wpdu**.sys …..WPD U** 驅(qū)動(dòng)
ws2ifsl.sys ….Winsock2 IFS 層

本回答被提問(wèn)者采納

stream.sys

system32下的drivers是WINXP自帶的驅(qū)動(dòng)
c盤(pán)根目錄下的drivers是你用還原系統(tǒng)安裝時(shí),還原系統(tǒng)自帶的驅(qū)動(dòng),電腦安裝完成后,此文件夾可以刪除.

stream.sys

沒(méi)什么區(qū)別，都不要亂碰

前沿拓展：

stream.sys

一般都放在C盤(pán)WINDOWNS的SYSTEM32里！

背景

由于運(yùn)維工作需要，經(jīng)常需要將一些數(shù)據(jù)從數(shù)據(jù)庫(kù)中導(dǎo)出，發(fā)送給運(yùn)營(yíng)和需求部門(mén)，天天去手動(dòng)查詢(xún)，又有點(diǎn)太費(fèi)時(shí)間了，于是研究學(xué)習(xí)了Python的基本功能，通過(guò)Python腳本和Linux 的crontab命令實(shí)現(xiàn)了每天自動(dòng)化的數(shù)據(jù)查詢(xún)和郵件發(fā)送。

代碼實(shí)現(xiàn)

定義了以下幾個(gè)代碼模塊實(shí)現(xiàn)了配置文件讀取、日志記錄、數(shù)據(jù)庫(kù)連接訪問(wèn)查詢(xún)、導(dǎo)出到xlsx和帶附件的郵件發(fā)送功能。1、demo.py 示例文件2、config.yml 配置文件3、common_log.py 實(shí)現(xiàn)日志記錄4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送

1、demo.py 示例# coding: utf-8
import common_db as mydb
import common_xlsx as my_xlsx
import common_log as mylog
import common_email as my_email

if __name__ == "__main__":
phone = '13********7'
sql = """ select orderno,orderAmount,actualAmount,phone order where phone= '{0}' """.format(phone)
# 查詢(xún)訂單
result = mydb.select_by_parameters(sql)
filename = "測(cè)試.xlsx"
sheet_name = '訂單查詢(xún)'
# 郵件接收人
receivers = "zhangsan@aliyun.com"
receivers_cc = "lisi@aliyun.com"
# 郵件抄送人
if len(result) > 0:
# 創(chuàng)建工作表
my_xlsx.create_xlsx(filename)
mylog.logger.info("創(chuàng)建工作表成功"+filename)
# 將查詢(xún)結(jié)果放入工作表
my_xlsx.create_sheet_in_xlsx(filename, sheet_name, result,0)
mylog.logger.info("創(chuàng)建工作簿成功"+sheet_name)
# 對(duì)工作簿求和
my_xlsx.sum_col_for_sheet(filename, sheet_name)
mylog.logger.info("求和匯總成功")
# 刪除空工作表
my_xlsx.delete_sheet_from_xlsx(filename,'Sheet')
# 發(fā)送郵件
my_email.to_send_email("測(cè)試查詢(xún)結(jié)果", receivers, receivers_cc, filename, "訂單查詢(xún)結(jié)果.xlsx")
mylog.logger.info("發(fā)送郵件成功")2、config.yml——配置文件mysql:
host: 192.168.x.x
port: 3306
username: xxxx
password: xxxx
database: xxxx
log:
log_path: D:\log
log_size: 8
log_num: 3
email:
**tp: **tp.126.com
# 發(fā)送方郵件地址
from: xxxx@aliyun.com
# 發(fā)送方授權(quán)碼
password: fdsafafdsafsa
3、common_log.py 實(shí)現(xiàn)日志記錄import logging.handlers
import logging
import yaml
import os
import sys

# 提供日志功能
class logger:
# 先讀取XML文件中的配置數(shù)據(jù)
# 由于config.xml放置在與當(dāng)前文件相同的目錄下，因此通過(guò) __file__ 來(lái)獲取XML文件的目錄，第二再拼接成絕對(duì)路徑
# 這里利用了lxml庫(kù)來(lái)解析XML
# root = etree.parse(os.path.join(os.path.dirname(__file__), 'config.xml')).getroot()

# 先讀取yml中的配置數(shù)據(jù)
# 由于
# 讀取日志文件保存路徑
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_log = result["log"]

logpath = config_log['log_path']
# root.find('logpath').text
# 讀取日志文件容量，轉(zhuǎn)換為字節(jié)
logsize = 1024*1024*int(config_log['log_size'])
# 讀取日志文件保存?zhèn)€數(shù)
lognum = int(config_log['log_num'])

# 日志文件名：由用例腳本的名稱(chēng)，結(jié)合日志保存路徑，得到日志文件的絕對(duì)路徑
logname = os.path.join(logpath, sys.argv[0].split('/')[-1].split('.')[0])+".log"

# 初始化logger
log = logging.getLogger()
# 日志格式，可以根據(jù)需要設(shè)置
fmt = logging.Formatter('[%(asctime)s][%(filename)s][line:%(lineno)d][%(levelname)s] %(message)s', '%Y-%m-%d %H:%M:%S')

# 日志輸出到文件，這里用到了上面獲取的日志名稱(chēng)，大小，保存?zhèn)€數(shù)
handle1 = logging.handlers.RotatingFileHandler(logname, maxBytes=logsize, backupCount=lognum)
handle1.setFormatter(fmt)
# 同時(shí)輸出到屏幕，便于實(shí)施觀察
handle2 = logging.StreamHandler(stream=sys.stdout)
handle2.setFormatter(fmt)
log.addHandler(handle1)
log.addHandler(handle2)

# 設(shè)置日志基本，這里設(shè)置為INFO，表示只有INFO級(jí)別及以上的會(huì)打印
log.setLevel(logging.INFO)

# 日志接口，用戶(hù)只需調(diào)用這里的接口即可，這里只**了INFO, WARNING, ERROR三個(gè)級(jí)別的日志，可根據(jù)需要定義更多接口
@clas**ethod
def info(cls, msg):
cls.log.info(msg)
return

@clas**ethod
def warning(cls, msg):
cls.log.warning(msg)
return

@clas**ethod
def error(cls, msg):
cls.log.error(msg)
return4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)import time
import pymysql
from common_log import *

# 連接數(shù)據(jù)庫(kù)
def get_connection():
_conn_status = True
_max_retries_count = 10 # 設(shè)置最大重試次數(shù)
_conn_retries_count = 0 # 初始重試次數(shù)
_conn_timeout = 3 # 連接超時(shí)時(shí)間為3秒
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_mysql = result["mysql"]
while _conn_status and _conn_retries_count <= _max_retries_count:
try:
connect = pymysql.connect(host=config_mysql['host'], user=config_mysql['username'],
password=config_mysql['password'], database=config_mysql['database'],
port=config_mysql['port'])
_conn_status = False # 如果conn成功則_status為設(shè)置為False則退出循環(huán)，返回db連接對(duì)象
logger.info("連接數(shù)據(jù)庫(kù)成功")
return connect
except Exception as e:
_conn_retries_count += 1
logger.info("第%s次連接數(shù)據(jù)庫(kù)失敗"%(_conn_retries_count))
logger.error(e)
time.sleep(3)
continue

# 查詢(xún)函數(shù)
def select_by_parameters(sql, params=None):
try:
connect = get_connection()
cursor = connect.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, params)
result = cursor.fetchall()
return result
except Exception as e:
logger.error("執(zhí)行查詢(xún)報(bào)錯(cuò)")
logger.info(sql)
logger.error(e)
finally:
try:
cursor.close()
except Exception as e:
logger.error("關(guān)閉游標(biāo)對(duì)象報(bào)錯(cuò)")
logger.error(e)
try:
connect.close()
except Exception as e:
print(e)
print("數(shù)據(jù)庫(kù)鏈接關(guān)閉異常")5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理import openpyxl
import os
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, colors
from openpyxl.utils import get_column_letter

# 定義邊框
thin_border = Border(left=Side(style='thin', color='FFFFFF'), right=Side(style='thin', color='FFFFFF'),
top=Side(style='thin', color='FFFFFF'), bottom=Side(style='thin', color='FFFFFF'))
# 居中對(duì)齊
alignment_center = Alignment(horizontal='center', vertical='center')
# 右對(duì)齊
alignment_right = Alignment(horizontal='center', vertical='center')

# 雙行填充
fill_double = PatternFill(fgColor='FFDCE6F1', fill_type='solid')
# 單行填充
fill_single = PatternFill(fgColor='FFB8CCE4', fill_type='solid')

# 表頭填充
fill_head = PatternFill(fgColor='FF366092', fill_type='solid')
font_head = Font(bold=True, color='FFFFFFFF')
# 1、創(chuàng)建xlsx腳本——在指定的filepath，創(chuàng)建指定的filename的xlsx文件
def create_xlsx(filename):
wb = openpyxl.Workbook()
wb.save(filename)

# 2、增加工作簿腳本——讀取指定路徑下的xlsx文件，在工作表第index位置增加一個(gè)工作簿，并將mysql查詢(xún)結(jié)果result寫(xiě)入到該工作簿
def create_sheet_in_xlsx(filename, sheet_name, result, index):
# 加載文件
wb = openpyxl.load_workbook(filename)
# 在指**置創(chuàng)建工作表
wb.create_sheet(sheet_name, index)
# 獲取新建的工作表
ws = wb[sheet_name]
j = 1
if len(result) > 0:
# 寫(xiě)表頭
for key, value in (result[0].items()):
ws.cell(1, j, format(key)).border = thin_border
# 定義對(duì)齊方式
ws.cell(1, j).alignment = alignment_center
# 字體 顏色為白色
ws.cell(1, j).font = font_head
# 填充
ws.cell(1, j).fill = fill_head
# 邊框
j = j + 1
ws.row_dimensions[1].height = 30
# 寫(xiě)數(shù)據(jù)
# 根據(jù)結(jié)果集行數(shù)量進(jìn)行循環(huán)
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i+2, j).fill = fill_double
else:
ws.cell(i+2, j).fill = fill_single
ws.cell(i + 2, j, value)
ws.cell(i + 2, j).border = thin_border
ws.cell(i + 2, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i+2, j).alignment = alignment_center
ws.cell(i+2, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i+2, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i+2, j).alignment = alignment_right
ws.cell(i+2, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
# 保存工作表
wb.save(filename)

# 3、刪除工作簿
def delete_sheet_from_xlsx(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
wb.remove_sheet(wb[sheet_name])
wb.save(filename)

# 4、往工作簿中追加行 將mysql的查詢(xún)結(jié)果追加到工作表sheet_name末尾
def add_result_to_sheet(filename,new_sheet_name,result):
wb = openpyxl.load_workbook(filename)
ws = wb[new_sheet_name]
# 獲取最大行
mr = ws.max_row
if len(result) > 0:
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i + mr, j).fill = fill_double
else:
ws.cell(i + mr, j).fill = fill_single
ws.cell(i + mr, j, value)
ws.cell(i + mr, j).border = thin_border
ws.cell(i + mr, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i + mr, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i + mr, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i + mr, j).alignment = alignment_right
ws.cell(i + mr, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
wb.save(filename)

# 5、刪除工作簿中最大行
def delete_max_row_from_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_rows(ws.max_row)
wb.save(filename)

# 6、刪除工作簿中指定列
def delete_col_from_sheet(filename,sheet_name,colno):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_cols(colno)
wb.save(filename)

# 7、對(duì)表格中金額和數(shù)量字段進(jìn)行求和
def sum_col_for_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
for col in list(ws.columns):
l = [c.value for c in col]
if '金額' in l[0] or '數(shù)量' in l[0]:
# 本列行的數(shù)量
row_size = len(col) + 1
# 本列的列號(hào)是
col_no = col[1].column
col_code = get_column_letter(col[1].column)
ws.cell(row_size, col_no, "=sum(" + str(col_code) + str(2) + ":" + str(col_code) + str(
row_size – 1) + ")").border = thin_border
ws.cell(row_size, col_no).font = font_head
# 填充
ws.cell(row_size, col_no).fill = fill_head
# 測(cè)試添加樣式
if '金額' in l[0]:
ws.cell(row_size, col_no).number_format = '#,##0.00'
ws.cell(row_size, col_no).alignment = alignment_right
if '數(shù)量' in l[0]:
ws.cell(row_size, col_no).number_format = '0'
ws.cell(row_size, col_no).alignment = alignment_center
ws.row_dimensions[col_no].height = 20
wb.save(filename)6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from **tplib import **TP_SSL
from common_log import *
import yaml

#
# file_Name是路徑名稱(chēng)加文件名和擴(kuò)展名；
# new_file_name是在郵件附件中顯示的名稱(chēng)
# receivers是收件人列表，中間逗號(hào)隔開(kāi)
# receivers_cc是抄送人列表
# mail_subject是郵件主題
def to_send_email(mail_subject,receivers,receivers_cc,file_name,new_file_name):
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_email = result["email"]
password = config_email['password']
msg = MIMEMultipart('related')
msgAlternative = MIMEMultipart('alternative')
msgAlternative.attach(MIMEText("<h1>見(jiàn)附件</h1> <br />", "html", "utf-8"))
msg.attach(msgAlternative)
# file_name 是指文件路徑加名稱(chēng)和擴(kuò)展名
file1 = MIMEText( open(file_name, 'rb').read(), 'base64', 'utf-8' )
file1["Content-Type"] = 'application/octet-stream'
# new_file_name是指郵件附件中顯示的名稱(chēng)
file1.add_header('Content-Disposition', 'attachment',filename=new_file_name)
msg.attach(file1)
msg['Subject'] = Header(mail_subject, 'utf-8').encode()
msg['From'] = config_email['from']
msg['To'] = receivers
msg['Cc'] = receivers_cc
try:
**tp = **TP_SSL(config_email['**tp'])
**tp.login(msg['From'], password)
**tp.sendmail(msg['From'], msg['To'].split(',') + msg['Cc'].split(','), msg.as_string())
logger.info("發(fā)送郵件成功,郵件接收人是:%s,郵件抄送人是:%s"%(receivers, receivers_cc))
except Exception as e:
logger.error("發(fā)送郵件出現(xiàn)錯(cuò)誤,郵件接收人是:%s,郵件抄送人是:%s" % (receivers, receivers_cc))
logger.error(e)
finally:
try:
**tp.quit()
except Exception as e:
logger.error(e)

拓展知識(shí)：

stream.sys

C:\windows\drivers :

如果用的是GHOST版的XP的話(huà),在C:\windows\下面有個(gè)driver的文件夾,里面是集成驅(qū)動(dòng)安裝文件,是方便不同機(jī)器裝機(jī)時(shí)自動(dòng)安裝驅(qū)動(dòng)的,這個(gè)是可以刪除的
,集成驅(qū)動(dòng)文件,都被放在Drivers里,安裝完后為了節(jié)省空間,可刪除.
C:\Drivers可以刪
C:\windows\Drivers可以刪
C:\windows\system\Drivers可以刪
C:\windows\system32\Drivers絕對(duì)不能刪

C:\WINDOWS\SYSTEM32\drivers :

這個(gè)文件夾是系統(tǒng)非常重要的文件夾,包含著系統(tǒng)的很多驅(qū)動(dòng)程序 絕對(duì)不能刪
文件夾下常見(jiàn)文件說(shuō)明:
acpiec.sys …..ACPI 嵌入控制器驅(qū)動(dòng)
amdk6.sys ……AMD K6 CPU 驅(qū)動(dòng)
amdk7.sys …… AMD K7 CPU 驅(qū)動(dòng)
arp1394.sys ….IP/1394 Arp 客戶(hù)端
asyncmac.sys …MS 遠(yuǎn)程訪問(wèn)串行網(wǎng)絡(luò)驅(qū)動(dòng)
atmarpc.sys ….IP/ATM Arp 客戶(hù)端
atmepvc.sys ….ATM 網(wǎng)絡(luò)封包媒介驅(qū)動(dòng)
atmlane.sys ….ATM 局域網(wǎng)仿真驅(qū)動(dòng)
atmuni.sys …..ATM UNI 調(diào)用管理器
bridge.sys …..MAC 網(wǎng)橋驅(qū)動(dòng)
cbidf2k.sys ….CardBus/PCMCIA IDE **端口 驅(qū)動(dòng)
cinemst2.sys …CineMaster C 1.2 WDM 主驅(qū)動(dòng)
cpqdap01.sys …Compaq PA-1 播放器驅(qū)動(dòng)
crusoe.sys ….. Crusoe CPU 驅(qū)動(dòng)
cxthsfS2.cty …CTY 文件
diskdump.sys …崩潰轉(zhuǎn)儲(chǔ)磁盤(pán)驅(qū)動(dòng)
dmboot.sys …..NT 磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
dmio.sys …….NT磁盤(pán)管理器輸入輸出驅(qū)動(dòng)
dmload.sys …..NT磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
fastfat.sys ….快速 FAT 文件系統(tǒng)驅(qū)動(dòng)
fsvga.sys ……全屏視頻驅(qū)動(dòng)
gm.dls ………DLS 文件 (MIDI 軟波表)
hidclass.sys …Hid 類(lèi)型庫(kù)
hidparse.sys …Hid 分析庫(kù)
HSFBS2S2.sys …HSF_HWB2 WDM 驅(qū)動(dòng)
HSFCXTS2.sys …HSF_CNXT 驅(qū)動(dòng)
HSFDPSP2.sys …HSF_DP 驅(qū)動(dòng)
HSFProf.cty ….CTY 文件
http.sys …….HTTP 協(xié)議堆棧
imagedrv.sys …NERO 鏡像驅(qū)動(dòng)器 SCSI **端口
imagesrv.sys …Nero 鏡像服務(wù)器
irenum.sys …..**線(xiàn)總線(xiàn)枚舉器
mcd.sys ……..中型 changer 類(lèi)驅(qū)動(dòng)
mf.sys ………多功能端口枚舉器
mrxdav.sys …..Windows NT WebDav Minirdr
mrx**b.sys …..Windows NT WebDav Minirdr
MSKSSRV.sys ….MS KS 服務(wù)器
MSPCLOCK.sys …MS **時(shí)鐘
MSPQM.sys ……MS **質(zhì)量管理器
ndisuio.sys ….NDIS 用戶(hù)模式輸入輸出驅(qū)動(dòng)
netbios.sys ….NetBIOS 接口驅(qū)動(dòng)
nic1394.sys ….IEEE1394 Ndis **端口和調(diào)用管理器
nikedrv.sys ….NikeDrv U** 驅(qū)動(dòng)
nmnt.sys …….Netmon NT 驅(qū)動(dòng)
nwlnkipx.sys …NWLINK2 IPX 協(xié)議驅(qū)動(dòng)
nwlnknb.sys ….NWLINK2 IPX Netbios 協(xié)議驅(qū)動(dòng)
nwlnkspx.sys …NWLINK2 SPX 協(xié)議驅(qū)動(dòng)
oprghdlr.sys …ACPI **作注冊(cè)驅(qū)動(dòng)
p3.sys ………P3 CPU 驅(qū)動(dòng)
pcmcia.sys …..PCMCIA 總線(xiàn)驅(qū)動(dòng)
processr.sys …通用處理器設(shè)備驅(qū)動(dòng)
psched.sys …..MS QoS 數(shù)據(jù)包計(jì)劃程序
rawwan.sys …..Raw **局域網(wǎng)傳輸驅(qū)動(dòng)
rdbss.sys ……重定向的驅(qū)動(dòng)器緩沖子系統(tǒng)驅(qū)動(dòng)
rio8drv.sys ….Rio8Drv.sys U** 驅(qū)動(dòng) (Rio U** MP3)
riodrv.sys …..RioDrv U** 驅(qū)動(dòng)(Rio U** MP3)
RMCast.sys …..可靠的多址廣播傳輸
rndi**p.sys ….遠(yuǎn)程 NDIS **端口
rootmdm.sys ….舊式非即插即用調(diào)制解調(diào)器設(shè)備驅(qū)動(dòng)
scsiport.sys …SCSI 端口驅(qū)動(dòng)
sdbus.sys ……安全數(shù)碼總線(xiàn)驅(qū)動(dòng)
secdrv.sys …..系統(tǒng)文件
sffdisk.sys ….**all Form Factor 磁盤(pán)驅(qū)動(dòng)
sffp_sd.sys ….**all Form Factor SD 協(xié)議驅(qū)動(dòng)
**clib.sys …..智能卡驅(qū)動(dòng)庫(kù)
sonydcam.sys …1394 桌面攝像頭驅(qū)動(dòng)
srv.sys ……..服務(wù)驅(qū)動(dòng)
stream.sys …..WDM 編解碼類(lèi)設(shè)備驅(qū)動(dòng) 2.0
tape.sys …….SCSI 磁帶類(lèi)設(shè)備驅(qū)動(dòng)
tos**d.sys …..東芝 **D解壓卡驅(qū)動(dòng)驅(qū)動(dòng)
t**vcap.sys ….WDM 東芝視頻捕獲驅(qū)動(dòng)
tunmp.sys ……Microsoft隧道接口驅(qū)動(dòng)
u**8023.sys ….遠(yuǎn)程 NDIS U** 驅(qū)動(dòng)
u**camd.sys ….U** 攝像頭驅(qū)動(dòng)
u**camd2.sys …U** 攝像頭驅(qū)動(dòng)
u**intel.sys …Intel U** 攝像頭驅(qū)動(dòng)
vdmin**d.sys …CineMaster C WDM **D **驅(qū)動(dòng)
wpdu**.sys …..WPD U** 驅(qū)動(dòng)
ws2ifsl.sys ….Winsock2 IFS 層

本回答被提問(wèn)者采納

stream.sys

system32下的drivers是WINXP自帶的驅(qū)動(dòng)
c盤(pán)根目錄下的drivers是你用還原系統(tǒng)安裝時(shí),還原系統(tǒng)自帶的驅(qū)動(dòng),電腦安裝完成后,此文件夾可以刪除.

stream.sys

沒(méi)什么區(qū)別，都不要亂碰

前沿拓展：

stream.sys

一般都放在C盤(pán)WINDOWNS的SYSTEM32里！

背景

由于運(yùn)維工作需要，經(jīng)常需要將一些數(shù)據(jù)從數(shù)據(jù)庫(kù)中導(dǎo)出，發(fā)送給運(yùn)營(yíng)和需求部門(mén)，天天去手動(dòng)查詢(xún)，又有點(diǎn)太費(fèi)時(shí)間了，于是研究學(xué)習(xí)了Python的基本功能，通過(guò)Python腳本和Linux 的crontab命令實(shí)現(xiàn)了每天自動(dòng)化的數(shù)據(jù)查詢(xún)和郵件發(fā)送。

代碼實(shí)現(xiàn)

定義了以下幾個(gè)代碼模塊實(shí)現(xiàn)了配置文件讀取、日志記錄、數(shù)據(jù)庫(kù)連接訪問(wèn)查詢(xún)、導(dǎo)出到xlsx和帶附件的郵件發(fā)送功能。1、demo.py 示例文件2、config.yml 配置文件3、common_log.py 實(shí)現(xiàn)日志記錄4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送

1、demo.py 示例# coding: utf-8
import common_db as mydb
import common_xlsx as my_xlsx
import common_log as mylog
import common_email as my_email

if __name__ == "__main__":
phone = '13********7'
sql = """ select orderno,orderAmount,actualAmount,phone order where phone= '{0}' """.format(phone)
# 查詢(xún)訂單
result = mydb.select_by_parameters(sql)
filename = "測(cè)試.xlsx"
sheet_name = '訂單查詢(xún)'
# 郵件接收人
receivers = "zhangsan@aliyun.com"
receivers_cc = "lisi@aliyun.com"
# 郵件抄送人
if len(result) > 0:
# 創(chuàng)建工作表
my_xlsx.create_xlsx(filename)
mylog.logger.info("創(chuàng)建工作表成功"+filename)
# 將查詢(xún)結(jié)果放入工作表
my_xlsx.create_sheet_in_xlsx(filename, sheet_name, result,0)
mylog.logger.info("創(chuàng)建工作簿成功"+sheet_name)
# 對(duì)工作簿求和
my_xlsx.sum_col_for_sheet(filename, sheet_name)
mylog.logger.info("求和匯總成功")
# 刪除空工作表
my_xlsx.delete_sheet_from_xlsx(filename,'Sheet')
# 發(fā)送郵件
my_email.to_send_email("測(cè)試查詢(xún)結(jié)果", receivers, receivers_cc, filename, "訂單查詢(xún)結(jié)果.xlsx")
mylog.logger.info("發(fā)送郵件成功")2、config.yml——配置文件mysql:
host: 192.168.x.x
port: 3306
username: xxxx
password: xxxx
database: xxxx
log:
log_path: D:\log
log_size: 8
log_num: 3
email:
**tp: **tp.126.com
# 發(fā)送方郵件地址
from: xxxx@aliyun.com
# 發(fā)送方授權(quán)碼
password: fdsafafdsafsa
3、common_log.py 實(shí)現(xiàn)日志記錄import logging.handlers
import logging
import yaml
import os
import sys

# 提供日志功能
class logger:
# 先讀取XML文件中的配置數(shù)據(jù)
# 由于config.xml放置在與當(dāng)前文件相同的目錄下，因此通過(guò) __file__ 來(lái)獲取XML文件的目錄，第二再拼接成絕對(duì)路徑
# 這里利用了lxml庫(kù)來(lái)解析XML
# root = etree.parse(os.path.join(os.path.dirname(__file__), 'config.xml')).getroot()

# 先讀取yml中的配置數(shù)據(jù)
# 由于
# 讀取日志文件保存路徑
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_log = result["log"]

logpath = config_log['log_path']
# root.find('logpath').text
# 讀取日志文件容量，轉(zhuǎn)換為字節(jié)
logsize = 1024*1024*int(config_log['log_size'])
# 讀取日志文件保存?zhèn)€數(shù)
lognum = int(config_log['log_num'])

# 日志文件名：由用例腳本的名稱(chēng)，結(jié)合日志保存路徑，得到日志文件的絕對(duì)路徑
logname = os.path.join(logpath, sys.argv[0].split('/')[-1].split('.')[0])+".log"

# 初始化logger
log = logging.getLogger()
# 日志格式，可以根據(jù)需要設(shè)置
fmt = logging.Formatter('[%(asctime)s][%(filename)s][line:%(lineno)d][%(levelname)s] %(message)s', '%Y-%m-%d %H:%M:%S')

# 日志輸出到文件，這里用到了上面獲取的日志名稱(chēng)，大小，保存?zhèn)€數(shù)
handle1 = logging.handlers.RotatingFileHandler(logname, maxBytes=logsize, backupCount=lognum)
handle1.setFormatter(fmt)
# 同時(shí)輸出到屏幕，便于實(shí)施觀察
handle2 = logging.StreamHandler(stream=sys.stdout)
handle2.setFormatter(fmt)
log.addHandler(handle1)
log.addHandler(handle2)

# 設(shè)置日志基本，這里設(shè)置為INFO，表示只有INFO級(jí)別及以上的會(huì)打印
log.setLevel(logging.INFO)

# 日志接口，用戶(hù)只需調(diào)用這里的接口即可，這里只**了INFO, WARNING, ERROR三個(gè)級(jí)別的日志，可根據(jù)需要定義更多接口
@clas**ethod
def info(cls, msg):
cls.log.info(msg)
return

@clas**ethod
def warning(cls, msg):
cls.log.warning(msg)
return

@clas**ethod
def error(cls, msg):
cls.log.error(msg)
return4、common_db.py 實(shí)現(xiàn)數(shù)據(jù)庫(kù)連接和訪問(wèn)import time
import pymysql
from common_log import *

# 連接數(shù)據(jù)庫(kù)
def get_connection():
_conn_status = True
_max_retries_count = 10 # 設(shè)置最大重試次數(shù)
_conn_retries_count = 0 # 初始重試次數(shù)
_conn_timeout = 3 # 連接超時(shí)時(shí)間為3秒
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_mysql = result["mysql"]
while _conn_status and _conn_retries_count <= _max_retries_count:
try:
connect = pymysql.connect(host=config_mysql['host'], user=config_mysql['username'],
password=config_mysql['password'], database=config_mysql['database'],
port=config_mysql['port'])
_conn_status = False # 如果conn成功則_status為設(shè)置為False則退出循環(huán)，返回db連接對(duì)象
logger.info("連接數(shù)據(jù)庫(kù)成功")
return connect
except Exception as e:
_conn_retries_count += 1
logger.info("第%s次連接數(shù)據(jù)庫(kù)失敗"%(_conn_retries_count))
logger.error(e)
time.sleep(3)
continue

# 查詢(xún)函數(shù)
def select_by_parameters(sql, params=None):
try:
connect = get_connection()
cursor = connect.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, params)
result = cursor.fetchall()
return result
except Exception as e:
logger.error("執(zhí)行查詢(xún)報(bào)錯(cuò)")
logger.info(sql)
logger.error(e)
finally:
try:
cursor.close()
except Exception as e:
logger.error("關(guān)閉游標(biāo)對(duì)象報(bào)錯(cuò)")
logger.error(e)
try:
connect.close()
except Exception as e:
print(e)
print("數(shù)據(jù)庫(kù)鏈接關(guān)閉異常")5、common_xlsx.py 實(shí)現(xiàn)數(shù)據(jù)表格的處理import openpyxl
import os
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, colors
from openpyxl.utils import get_column_letter

# 定義邊框
thin_border = Border(left=Side(style='thin', color='FFFFFF'), right=Side(style='thin', color='FFFFFF'),
top=Side(style='thin', color='FFFFFF'), bottom=Side(style='thin', color='FFFFFF'))
# 居中對(duì)齊
alignment_center = Alignment(horizontal='center', vertical='center')
# 右對(duì)齊
alignment_right = Alignment(horizontal='center', vertical='center')

# 雙行填充
fill_double = PatternFill(fgColor='FFDCE6F1', fill_type='solid')
# 單行填充
fill_single = PatternFill(fgColor='FFB8CCE4', fill_type='solid')

# 表頭填充
fill_head = PatternFill(fgColor='FF366092', fill_type='solid')
font_head = Font(bold=True, color='FFFFFFFF')
# 1、創(chuàng)建xlsx腳本——在指定的filepath，創(chuàng)建指定的filename的xlsx文件
def create_xlsx(filename):
wb = openpyxl.Workbook()
wb.save(filename)

# 2、增加工作簿腳本——讀取指定路徑下的xlsx文件，在工作表第index位置增加一個(gè)工作簿，并將mysql查詢(xún)結(jié)果result寫(xiě)入到該工作簿
def create_sheet_in_xlsx(filename, sheet_name, result, index):
# 加載文件
wb = openpyxl.load_workbook(filename)
# 在指**置創(chuàng)建工作表
wb.create_sheet(sheet_name, index)
# 獲取新建的工作表
ws = wb[sheet_name]
j = 1
if len(result) > 0:
# 寫(xiě)表頭
for key, value in (result[0].items()):
ws.cell(1, j, format(key)).border = thin_border
# 定義對(duì)齊方式
ws.cell(1, j).alignment = alignment_center
# 字體 顏色為白色
ws.cell(1, j).font = font_head
# 填充
ws.cell(1, j).fill = fill_head
# 邊框
j = j + 1
ws.row_dimensions[1].height = 30
# 寫(xiě)數(shù)據(jù)
# 根據(jù)結(jié)果集行數(shù)量進(jìn)行循環(huán)
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i+2, j).fill = fill_double
else:
ws.cell(i+2, j).fill = fill_single
ws.cell(i + 2, j, value)
ws.cell(i + 2, j).border = thin_border
ws.cell(i + 2, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i+2, j).alignment = alignment_center
ws.cell(i+2, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i+2, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i+2, j).alignment = alignment_right
ws.cell(i+2, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
# 保存工作表
wb.save(filename)

# 3、刪除工作簿
def delete_sheet_from_xlsx(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
wb.remove_sheet(wb[sheet_name])
wb.save(filename)

# 4、往工作簿中追加行 將mysql的查詢(xún)結(jié)果追加到工作表sheet_name末尾
def add_result_to_sheet(filename,new_sheet_name,result):
wb = openpyxl.load_workbook(filename)
ws = wb[new_sheet_name]
# 獲取最大行
mr = ws.max_row
if len(result) > 0:
for i in range(len(result)):
# 循環(huán)當(dāng)前行，定義j變量為列使用
j = 1
for key, value in (result[i].items()):
if i % 2 == 0:
ws.cell(i + mr, j).fill = fill_double
else:
ws.cell(i + mr, j).fill = fill_single
ws.cell(i + mr, j, value)
ws.cell(i + mr, j).border = thin_border
ws.cell(i + mr, j).alignment = alignment_center
ws.row_dimensions[i + 2].height = 20
if '時(shí)間' in format(key):
ws.cell(i + mr, j).number_format = 'yyyy-mm-dd hh:mm:ss'
j = j + 1
continue
# 如果字段名稱(chēng)是數(shù)量的話(huà)，不保留小數(shù)
if '數(shù)量' in format(key):
ws.cell(i + mr, j).number_format = '0'
j = j + 1
continue
# 如果是字段名稱(chēng)包含金額的話(huà)，則右對(duì)齊
if '金額' in format(key):
ws.cell(i + mr, j).alignment = alignment_right
ws.cell(i + mr, j).number_format = '#,##0.00'
j = j + 1
continue
j = j + 1
wb.save(filename)

# 5、刪除工作簿中最大行
def delete_max_row_from_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_rows(ws.max_row)
wb.save(filename)

# 6、刪除工作簿中指定列
def delete_col_from_sheet(filename,sheet_name,colno):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
ws.delete_cols(colno)
wb.save(filename)

# 7、對(duì)表格中金額和數(shù)量字段進(jìn)行求和
def sum_col_for_sheet(filename,sheet_name):
wb = openpyxl.load_workbook(filename)
ws = wb[sheet_name]
for col in list(ws.columns):
l = [c.value for c in col]
if '金額' in l[0] or '數(shù)量' in l[0]:
# 本列行的數(shù)量
row_size = len(col) + 1
# 本列的列號(hào)是
col_no = col[1].column
col_code = get_column_letter(col[1].column)
ws.cell(row_size, col_no, "=sum(" + str(col_code) + str(2) + ":" + str(col_code) + str(
row_size – 1) + ")").border = thin_border
ws.cell(row_size, col_no).font = font_head
# 填充
ws.cell(row_size, col_no).fill = fill_head
# 測(cè)試添加樣式
if '金額' in l[0]:
ws.cell(row_size, col_no).number_format = '#,##0.00'
ws.cell(row_size, col_no).alignment = alignment_right
if '數(shù)量' in l[0]:
ws.cell(row_size, col_no).number_format = '0'
ws.cell(row_size, col_no).alignment = alignment_center
ws.row_dimensions[col_no].height = 20
wb.save(filename)6、common_email.py 實(shí)現(xiàn)帶附件的郵件發(fā)送from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from **tplib import **TP_SSL
from common_log import *
import yaml

#
# file_Name是路徑名稱(chēng)加文件名和擴(kuò)展名；
# new_file_name是在郵件附件中顯示的名稱(chēng)
# receivers是收件人列表，中間逗號(hào)隔開(kāi)
# receivers_cc是抄送人列表
# mail_subject是郵件主題
def to_send_email(mail_subject,receivers,receivers_cc,file_name,new_file_name):
with open('config.yml', 'r') as f:
result = yaml.load(f, Loader=yaml.FullLoader)
config_email = result["email"]
password = config_email['password']
msg = MIMEMultipart('related')
msgAlternative = MIMEMultipart('alternative')
msgAlternative.attach(MIMEText("<h1>見(jiàn)附件</h1> <br />", "html", "utf-8"))
msg.attach(msgAlternative)
# file_name 是指文件路徑加名稱(chēng)和擴(kuò)展名
file1 = MIMEText( open(file_name, 'rb').read(), 'base64', 'utf-8' )
file1["Content-Type"] = 'application/octet-stream'
# new_file_name是指郵件附件中顯示的名稱(chēng)
file1.add_header('Content-Disposition', 'attachment',filename=new_file_name)
msg.attach(file1)
msg['Subject'] = Header(mail_subject, 'utf-8').encode()
msg['From'] = config_email['from']
msg['To'] = receivers
msg['Cc'] = receivers_cc
try:
**tp = **TP_SSL(config_email['**tp'])
**tp.login(msg['From'], password)
**tp.sendmail(msg['From'], msg['To'].split(',') + msg['Cc'].split(','), msg.as_string())
logger.info("發(fā)送郵件成功,郵件接收人是:%s,郵件抄送人是:%s"%(receivers, receivers_cc))
except Exception as e:
logger.error("發(fā)送郵件出現(xiàn)錯(cuò)誤,郵件接收人是:%s,郵件抄送人是:%s" % (receivers, receivers_cc))
logger.error(e)
finally:
try:
**tp.quit()
except Exception as e:
logger.error(e)

拓展知識(shí)：

stream.sys

C:\windows\drivers :

如果用的是GHOST版的XP的話(huà),在C:\windows\下面有個(gè)driver的文件夾,里面是集成驅(qū)動(dòng)安裝文件,是方便不同機(jī)器裝機(jī)時(shí)自動(dòng)安裝驅(qū)動(dòng)的,這個(gè)是可以刪除的
,集成驅(qū)動(dòng)文件,都被放在Drivers里,安裝完后為了節(jié)省空間,可刪除.
C:\Drivers可以刪
C:\windows\Drivers可以刪
C:\windows\system\Drivers可以刪
C:\windows\system32\Drivers絕對(duì)不能刪

C:\WINDOWS\SYSTEM32\drivers :

這個(gè)文件夾是系統(tǒng)非常重要的文件夾,包含著系統(tǒng)的很多驅(qū)動(dòng)程序 絕對(duì)不能刪
文件夾下常見(jiàn)文件說(shuō)明:
acpiec.sys …..ACPI 嵌入控制器驅(qū)動(dòng)
amdk6.sys ……AMD K6 CPU 驅(qū)動(dòng)
amdk7.sys …… AMD K7 CPU 驅(qū)動(dòng)
arp1394.sys ….IP/1394 Arp 客戶(hù)端
asyncmac.sys …MS 遠(yuǎn)程訪問(wèn)串行網(wǎng)絡(luò)驅(qū)動(dòng)
atmarpc.sys ….IP/ATM Arp 客戶(hù)端
atmepvc.sys ….ATM 網(wǎng)絡(luò)封包媒介驅(qū)動(dòng)
atmlane.sys ….ATM 局域網(wǎng)仿真驅(qū)動(dòng)
atmuni.sys …..ATM UNI 調(diào)用管理器
bridge.sys …..MAC 網(wǎng)橋驅(qū)動(dòng)
cbidf2k.sys ….CardBus/PCMCIA IDE **端口 驅(qū)動(dòng)
cinemst2.sys …CineMaster C 1.2 WDM 主驅(qū)動(dòng)
cpqdap01.sys …Compaq PA-1 播放器驅(qū)動(dòng)
crusoe.sys ….. Crusoe CPU 驅(qū)動(dòng)
cxthsfS2.cty …CTY 文件
diskdump.sys …崩潰轉(zhuǎn)儲(chǔ)磁盤(pán)驅(qū)動(dòng)
dmboot.sys …..NT 磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
dmio.sys …….NT磁盤(pán)管理器輸入輸出驅(qū)動(dòng)
dmload.sys …..NT磁盤(pán)管理器啟動(dòng)驅(qū)動(dòng)
fastfat.sys ….快速 FAT 文件系統(tǒng)驅(qū)動(dòng)
fsvga.sys ……全屏視頻驅(qū)動(dòng)
gm.dls ………DLS 文件 (MIDI 軟波表)
hidclass.sys …Hid 類(lèi)型庫(kù)
hidparse.sys …Hid 分析庫(kù)
HSFBS2S2.sys …HSF_HWB2 WDM 驅(qū)動(dòng)
HSFCXTS2.sys …HSF_CNXT 驅(qū)動(dòng)
HSFDPSP2.sys …HSF_DP 驅(qū)動(dòng)
HSFProf.cty ….CTY 文件
http.sys …….HTTP 協(xié)議堆棧
imagedrv.sys …NERO 鏡像驅(qū)動(dòng)器 SCSI **端口
imagesrv.sys …Nero 鏡像服務(wù)器
irenum.sys …..**線(xiàn)總線(xiàn)枚舉器
mcd.sys ……..中型 changer 類(lèi)驅(qū)動(dòng)
mf.sys ………多功能端口枚舉器
mrxdav.sys …..Windows NT WebDav Minirdr
mrx**b.sys …..Windows NT WebDav Minirdr
MSKSSRV.sys ….MS KS 服務(wù)器
MSPCLOCK.sys …MS **時(shí)鐘
MSPQM.sys ……MS **質(zhì)量管理器
ndisuio.sys ….NDIS 用戶(hù)模式輸入輸出驅(qū)動(dòng)
netbios.sys ….NetBIOS 接口驅(qū)動(dòng)
nic1394.sys ….IEEE1394 Ndis **端口和調(diào)用管理器
nikedrv.sys ….NikeDrv U** 驅(qū)動(dòng)
nmnt.sys …….Netmon NT 驅(qū)動(dòng)
nwlnkipx.sys …NWLINK2 IPX 協(xié)議驅(qū)動(dòng)
nwlnknb.sys ….NWLINK2 IPX Netbios 協(xié)議驅(qū)動(dòng)
nwlnkspx.sys …NWLINK2 SPX 協(xié)議驅(qū)動(dòng)
oprghdlr.sys …ACPI **作注冊(cè)驅(qū)動(dòng)
p3.sys ………P3 CPU 驅(qū)動(dòng)
pcmcia.sys …..PCMCIA 總線(xiàn)驅(qū)動(dòng)
processr.sys …通用處理器設(shè)備驅(qū)動(dòng)
psched.sys …..MS QoS 數(shù)據(jù)包計(jì)劃程序
rawwan.sys …..Raw **局域網(wǎng)傳輸驅(qū)動(dòng)
rdbss.sys ……重定向的驅(qū)動(dòng)器緩沖子系統(tǒng)驅(qū)動(dòng)
rio8drv.sys ….Rio8Drv.sys U** 驅(qū)動(dòng) (Rio U** MP3)
riodrv.sys …..RioDrv U** 驅(qū)動(dòng)(Rio U** MP3)
RMCast.sys …..可靠的多址廣播傳輸
rndi**p.sys ….遠(yuǎn)程 NDIS **端口
rootmdm.sys ….舊式非即插即用調(diào)制解調(diào)器設(shè)備驅(qū)動(dòng)
scsiport.sys …SCSI 端口驅(qū)動(dòng)
sdbus.sys ……安全數(shù)碼總線(xiàn)驅(qū)動(dòng)
secdrv.sys …..系統(tǒng)文件
sffdisk.sys ….**all Form Factor 磁盤(pán)驅(qū)動(dòng)
sffp_sd.sys ….**all Form Factor SD 協(xié)議驅(qū)動(dòng)
**clib.sys …..智能卡驅(qū)動(dòng)庫(kù)
sonydcam.sys …1394 桌面攝像頭驅(qū)動(dòng)
srv.sys ……..服務(wù)驅(qū)動(dòng)
stream.sys …..WDM 編解碼類(lèi)設(shè)備驅(qū)動(dòng) 2.0
tape.sys …….SCSI 磁帶類(lèi)設(shè)備驅(qū)動(dòng)
tos**d.sys …..東芝 **D解壓卡驅(qū)動(dòng)驅(qū)動(dòng)
t**vcap.sys ….WDM 東芝視頻捕獲驅(qū)動(dòng)
tunmp.sys ……Microsoft隧道接口驅(qū)動(dòng)
u**8023.sys ….遠(yuǎn)程 NDIS U** 驅(qū)動(dòng)
u**camd.sys ….U** 攝像頭驅(qū)動(dòng)
u**camd2.sys …U** 攝像頭驅(qū)動(dòng)
u**intel.sys …Intel U** 攝像頭驅(qū)動(dòng)
vdmin**d.sys …CineMaster C WDM **D **驅(qū)動(dòng)
wpdu**.sys …..WPD U** 驅(qū)動(dòng)
ws2ifsl.sys ….Winsock2 IFS 層

本回答被提問(wèn)者采納

stream.sys

system32下的drivers是WINXP自帶的驅(qū)動(dòng)
c盤(pán)根目錄下的drivers是你用還原系統(tǒng)安裝時(shí),還原系統(tǒng)自帶的驅(qū)動(dòng),電腦安裝完成后,此文件夾可以刪除.

stream.sys

沒(méi)什么區(qū)別，都不要亂碰