自动化终端考核检查系统的搭建过程

自从2020年进入公司从学生变成社会人以来,接手的第一项工作就是检查并整改公司终端的季度考核指标;

公司总部要求每季度进行一次评估(胡说八道。),需要管理的终端数量约为1500个

[En]

Quarterly assessment is required by the company headquarters, once a quarter (nonsense. ), the number of terminals to be managed is about 1500

主要用于检查各类管理软件(桌面管理、杀毒等)的安装率和版本合规性。企业内部网终端。

[En]

The main purpose is to check the installation rate and version compliance of all kinds of management software (desktop management, anti-virus, etc.) of intranet terminals.

各类软件都有总部提供的平台,需要做的就是去各个平台导出数据、Excel汇总计算,

然后查看哪些考核不达标,并通知负责终端维护的外包公司前往各个地点进行维护。

[En]

Then see which assessment is not up to standard, and inform the outsourcing company responsible for terminal maintenance to go to various locations for maintenance.

虽然工作难度不大,但每个季度末都要反复检查,确保达标,工作量大,一天至少要一两个小时。

[En]

Although the work is not difficult, it has to be checked repeatedly at the end of each quarter to make sure it is up to standard, so the workload is heavy, and it takes at least an hour or two a day.

其实刚接手的时候,我有过做自动巡检系统的想法,但当时我的技术还不够。

[En]

In fact, when I first took over, I had the idea of doing an automatic inspection system, but I didn’t have enough skills at that time.

而且一进公司,我要学的东西太多了,就暂时搁置了。

[En]

And as soon as I entered the company, I had too much to learn, so I put it aside for the time being.

22年一季度不是很忙,就利用工作之余的上班时间做出来了;

每天自动将结果发送给外包公司,大大减少了季度考核的工作量。

[En]

The results are automatically sent to the outsourcing company every day, which greatly reduces the workload of quarterly assessment.

主要的实现过程大致是三个步骤:

一、利用Python的selenium WEB自动化工具去各个平台下载数据;

二、利用Python的pymysql数据库工具将数据文件导入MySQL数据库;

三、MySQL数据库按照考核标准计算结果,生成结果文件,再通过邮件发送结果文件。

结构图:

一、利用Python的selenium WEB自动化工具去各个平台下载数据

其实,用浏览器控件去掉衍生品不太可靠,效率也不高,但找每个平台的项目组连接数据库太麻烦了,大家可能也不愿意打开数据库给你使用。

[En]

In fact, using browser control to remove derivatives is less reliable and inefficient, but it is too troublesome to find the project team of each platform to connect to the database, and people may not be willing to open the database for you to use.

并且这个工具最多季度末的时候一天跑两三次,并不需要太高的效率,所以就采用了selenium WEB自动化;

selenium的学习参考的是腾讯云社区的教程:Python中Selenium库使用教程详解 – 云+社区 – 腾讯云 (tencent.com)

首先是pip安装selenium,指定国内源加快下载速度:

pip install selenium -i https://pypi.tuna.tsinghua.edu.cn/simple

我用的是谷歌浏览器版本99,需要下载一个对应版本99的驱动程序才能调起浏览器,下载地址:http://chromedriver.storage.googleapis.com/index.html;

使用示例:

from selenium import webdriver
from time import sleep

实例化一款浏览器
browser = webdriver.Chrome(executable_path = "chromedriver.exe")
对指定的url发起请求
browser.get("https://www.bilibili.com/")
设定窗口大小
browser.set_window_size(1600, 900)
在页面中寻找元素
element = browser.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul[2]/li[1]/li/div/div/span') # “登录”
对元素进行操作
element.click() # 点击
延时3秒,等待页面回应
sleep(3)
关闭浏览器
browser.quit()

一般浏览器都可以用F12进入开发工具查看页面中的元素,点击1处的工具,再点击2处页面中任意的元素,在3处会跳转并高亮元素在HTML代码中的位置;

我是按照完整xpath来寻找元素的 browser.find_element_by_xpath(),也可以使用元素的id、name、标签等等;

点击元素后 element.click(),如果有后续操作可以添加一个延时,否则页面可能来不及响应。

自动化终端考核检查系统的搭建过程

1. 切换frame

某些页面会用到frame的结构,比如:

<html lang="en">
<head>
    <title>FrameTest</title>
</head>
<body>
<iframe src="a.html" id="frame1" name="myframe"></iframe>
</body>
</html>

在frame中的元素无法被 browser.find_element_by_xpath()直接寻找到,需要进行frame的切换:

frame = browser.find_elements_by_tag_name('iframe')[1] # &#x5B9A;&#x4E49;frame
browser.switch_to.frame(frame) # &#x5207;&#x6362;frame
element = browser.find_element_by_xpath('XXXXXX') # &#x5BFB;&#x627E;frame&#x4E2D;&#x7684;&#x5143;&#x7D20;
element.click() # &#x70B9;&#x51FB;&#x5143;&#x7D20;
browser.switch_to.default_content() # &#x9000;&#x51FA;frame&#x56DE;&#x5230;&#x4E3B;&#x4F53;&#x7ED3;&#x6784;

如果需要回到主体结构继续操作要使用 browser.switch_to.default_content()

2. 切换浏览器窗口

在一些地方点击后,会创建一个新的标签或弹出一个新的浏览器窗口。此时,您需要切换窗口以继续:

[En]

After clicking in some places, a new tab will be created or a new browser window will pop up. At this time, you need to switch the window to continue:

windows = browser.window_handles
browser.switch_to.window(windows[1]) # &#x6253;&#x5F00;&#x4E86;&#x65B0;&#x9875;&#x9762;&#xFF0C;&#x9700;&#x8981;&#x5207;&#x6362;&#x7A97;&#x53E3;
element = browser.find_element_by_xpath('XXXXXX')
element.click()

3. 新建标签页

如果您不想关闭浏览器,则需要打开新的选项卡才能继续:

[En]

If you do not want to close the browser, you need to open a new tab to continue:

js = "window.open('https://www.douban.com/')"
browser.execute_script(js) # &#x5728;&#x65B0;&#x6807;&#x7B7E;&#x9875;&#x4E2D;&#x8BBF;&#x95EE;

4. 识别简单的验证码

本来我以为用selenium查找元素一个一个去点是很重复无聊且枯燥的工作;

直到我遇到一个平台登录时需要输入图片验证码(其他平台只需要账号密码),事情才突然变得有趣起来。

[En]

Until I came across a platform that needed to enter an image verification code when logging in (other platforms only need account passwords), things suddenly became interesting.

其总体思路是首先截取验证码的图片,然后使用图片的工具识别文本来识别验证码,然后将其输入到网页验证码框中。

[En]

The general idea is to first intercept the picture of the CAPTCHA, then use the tool of the picture to recognize the text to identify the CAPTCHA, and then enter it into the web page verification code frame.

首先要解决把验证码图片截出来的问题,我参考的是selenium验证码识别之局部截图 – 简书 (jianshu.com)

然后选择了一个比较轻量的(因为要放到内网,太大了不好处理)图片识别文字的工具——pytesseract;

pytesseract的安装和使用参考的是Python OCR工具pytesseract详解_测试开发小记的博客-CSDN博客_pytesseract

因为图片识别验证码是一个不一定成功且准确的事件,所以我在这边使用了try finally结构,重复识别验证码并输入;

在使用过程中,识别正确率很低。一般来说,我要重复几十次才能通过。

[En]

In the course of using it, the correct rate of recognition is very low. Generally speaking, I have to repeat the recognition dozens of times before I can pass.

使用这个工具不需要追求高效率,所以我只要通过一次就可以满足我的需求。

[En]

The use of this tool does not need to pursue high efficiency, so I can meet my needs as long as I can pass it once.

以下是该函数这一部分的代码:

[En]

Here is the code for this part of the function:

from selenium import webdriver
from time import sleep
from PIL import Image
import pytesseract

&#x91CD;&#x590D;&#x5C1D;&#x8BD5;&#x8BC6;&#x522B;&#x9A8C;&#x8BC1;&#x7801;&#xFF0C;&#x5931;&#x8D25;&#x540E;&#x5237;&#x65B0;&#x91CD;&#x8BD5;&#xFF0C;&#x6210;&#x529F;&#x540E;&#x5BFB;&#x627E;&#x4E0D;&#x5230;try&#x90E8;&#x5206;&#x4E2D;&#x7B2C;&#x4E00;&#x4E2A;&#x5143;&#x7D20;&#xFF0C;&#x8DF3;&#x8F6C;&#x5230;finally&#x90E8;&#x5206;
try:
    r = 0
    while True:
        # &#x8F93;&#x5165;&#x7528;&#x6237;&#x540D;&#x5BC6;&#x7801;&#xFF0C;&#x8BC6;&#x522B;&#x9A8C;&#x8BC1;&#x7801;&#xFF0C;&#x70B9;&#x51FB;&#x201C;&#x767B;&#x5F55;&#x201D;
        element = browser.find_element_by_xpath('XXXXXX') # &#x7528;&#x6237;&#x540D;
        element.send_keys('&#x7528;&#x6237;&#x540D;')
        element = browser.find_element_by_xpath('XXXXXX') # &#x5BC6;&#x7801;
        element.send_keys('&#x5BC6;&#x7801;')
        # &#x622A;&#x56FE;&#x8BC6;&#x522B;&#x9A8C;&#x8BC1;&#x7801;&#x5E76;&#x8F93;&#x5165;
        browser.save_screenshot('browser.png') # &#x5BF9;&#x7F51;&#x9875;&#x8FDB;&#x884C;&#x622A;&#x56FE;
        code_png_lel = browser.find_element_by_xpath('XXXXXX') # &#x9A8C;&#x8BC1;&#x7801;
        location = code_png_lel.location # &#x83B7;&#x53D6;&#x9A8C;&#x8BC1;&#x7801;&#x5143;&#x7D20;&#x6240;&#x5728;&#x4F4D;&#x7F6E;
        print('location', location)
        size = code_png_lel.size # &#x83B7;&#x53D6;&#x9A8C;&#x8BC1;&#x7801;&#x5143;&#x7D20;&#x5927;&#x5C0F;
        print('size', size)
        rangle = (int(location['x']), int(location['y']), int(location['x'] + size['width']), int(location['y'] + size['height'])) # &#x627E;&#x5230;&#x9A8C;&#x8BC1;&#x7801;&#x5728;&#x7F51;&#x9875;&#x622A;&#x56FE;&#x4E2D;&#x7684;&#x4F4D;&#x7F6E;
        print('rangle', rangle)
        i = Image.open('browser.png')
        frame = i.crop(rangle) # &#x6309;&#x7167;&#x9A8C;&#x8BC1;&#x7801;&#x4F4D;&#x7F6E;&#x622A;&#x56FE;
        frame.save('code.png')
        j = Image.open('code.png')
        string = pytesseract.image_to_string(j) # &#x8BC6;&#x522B;&#x9A8C;&#x8BC1;&#x7801;&#x56FE;&#x7247;&#x4E2D;&#x7684;&#x6587;&#x5B57;
        string = string.replace(' ', '') # &#x5220;&#x9664;&#x8BC6;&#x522B;&#x7ED3;&#x679C;&#x4E2D;&#x7684;&#x7A7A;&#x683C;
        print(string)
        element = browser.find_element_by_xpath('XXXXXX') # &#x9A8C;&#x8BC1;&#x7801;&#x8F93;&#x5165;&#x6846;
        element.send_keys(string) # &#x8F93;&#x5165;&#x5220;&#x9664;&#x7A7A;&#x683C;&#x540E;&#x7684;&#x8BC6;&#x522B;&#x7ED3;&#x679C;
        element = browser.find_element_by_xpath('XXXXXX') # &#x201C;&#x767B;&#x5F55;&#x201D;
        element.click() # &#x70B9;&#x51FB;&#x767B;&#x5F55;
        browser.refresh() # &#x5237;&#x65B0;&#x9875;&#x9762;
        r = r + 1
        print(r)
&#x6210;&#x529F;&#x540E;&#x8DF3;&#x51FA;&#x5FAA;&#x73AF;&#xFF0C;&#x7EE7;&#x7EED;&#x64CD;&#x4F5C;
finally:
    sleep(5)
    element = browser.find_element_by_xpath('XXXXXX')
    element.click()

二、利用Python的pymysql数据库工具将数据文件导入MySQL数据库

获取各个平台的多张数据表后,下一步就是将数据导入MySQL数据库中进行计算(其实是两步,”导入”和”计算”);

首先,各个平台导出数据表的格式各不相同,有xlsx,有xls,有csv;

需要把它们全部整合成统一的格式csv,编码转为utf-8;

某些表中有许多无效行。将它们全部导入到数据库中将浪费大量时间,并且需要进行预处理。

[En]

There are many invalid rows in some tables. Importing them all into the database will waste a lot of time and need to be preprocessed.

xlsx或xls转csv:

import pandas

def xlsx_to_csv(xlsx_file_path, csv_file_path):
    print('>>&#x6587;&#x4EF6;%s&#x683C;&#x5F0F;&#x8F6C;&#x6362;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;' % xlsx_file_path)
    file_xlsx = pandas.read_excel(xlsx_file_path, index_col=0)
    file_xlsx.to_csv(csv_file_path, encoding='utf-8')
    print('>>&#x6587;&#x4EF6;%s&#x683C;&#x5F0F;&#x5DF2;&#x8F6C;&#x6362;&#x4E3A;csv' % xlsx_file_path)

xlsx或xls转csv,并删除无效表头(header = None):

import pandas

def xlsx_to_csv_noheader(xlsx_file_path, csv_file_path):
    print('>>&#x6587;&#x4EF6;%s&#x683C;&#x5F0F;&#x8F6C;&#x6362;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;' % xlsx_file_path)
    file_xlsx = pandas.read_excel(xlsx_file_path, index_col=0)
    file_xlsx.to_csv(csv_file_path, encoding='utf-8', header = None)
    print('>>&#x6587;&#x4EF6;%s&#x683C;&#x5F0F;&#x5DF2;&#x8F6C;&#x6362;&#x4E3A;csv&#xFF0C;&#x5E76;&#x5220;&#x9664;&#x4E86;&#x65E0;&#x6548;&#x8868;&#x5934;' % xlsx_file_path)

xlsx或xls转csv,并按照某关键字筛选(apply(lambda a:a == ‘关键字’)):

import pandas

def xlsx_to_csv_bgfxm(xlsx_file_path, csv_file_path):
    print('>>&#x6587;&#x4EF6;%s&#x683C;&#x5F0F;&#x8F6C;&#x6362;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;' % xlsx_file_path)
    file_xlsx = pandas.read_excel(xlsx_file_path, index_col=0)
    file_xlsx = file_xlsx.loc[file_xlsx['&#x5217;&#x540D;'].apply(lambda a:a == '&#x5173;&#x952E;&#x5B57;')]
    file_xlsx.to_csv(csv_file_path, encoding='utf-8', index = False)
    print('>>&#x6587;&#x4EF6;%s&#x683C;&#x5F0F;&#x5DF2;&#x8F6C;&#x6362;&#x4E3A;csv' % xlsx_file_path)

csv中按行删除某列中的重复值,保留重复值中从上到下的第一条(keep = ‘first’):

import pandas

def delete_duplicates(csv_file_path, keyword):
    print('>>&#x6587;&#x4EF6;%s&#x5220;&#x9664;%s&#x91CD;&#x590D;&#x8BB0;&#x5F55;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;' % (csv_file_path, keyword))
    csv_file = pandas.read_csv(csv_file_path)
    csv_file = csv_file.drop_duplicates(subset = [keyword], keep = 'first', inplace = False)
    csv_file.to_csv(csv_file_path, encoding='utf-8', index = False)
    print('>>&#x6587;&#x4EF6;%s&#x5220;&#x9664;%s&#x91CD;&#x590D;&#x8BB0;&#x5F55;&#x5DF2;&#x5B8C;&#x6210;' % (csv_file_path, keyword))

ANSI编码的csv转为UTF-8:

import codecs

def ansi2uft8(file):
    print('>>&#x6587;&#x4EF6;%s&#x7F16;&#x7801;&#x8F6C;&#x6362;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;' % file)
    f = codecs.open(file, 'r', 'ansi')
    ff = f.read()
    file_name = file.split('\\')[-1]
    file_path = file.replace(file_name, "")
    file_object = codecs.open(file_path + '\\' + file_name, 'w', 'utf-8')
    file_object.write(ff)
    print('>>&#x6587;&#x4EF6;%s&#x7F16;&#x7801;&#x5DF2;&#x8F6C;&#x6362;&#x4E3A;UTF-8' % file)

某些平台导出的csv文件每行末尾都会有一个英文逗号”,”,会导致导入数据库的时候表头出现空字段、每行多一个空值,因此需要删掉:

import os

def droplastcomma(file):
    print(">>&#x6587;&#x4EF6;%s&#x672B;&#x5C3E;','&#x5220;&#x9664;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;" % file)
    reader = open(file, 'r', encoding = 'utf8')
    write_file = file.strip()[:-4] + '_temp.csv'
    writer = open(write_file, 'w', encoding = 'utf8')
    rows_raw = reader.readlines()
    for row in rows_raw:
        row = row.rstrip()[:-1] + '\n'
        writer.writelines(row)
    reader.close()
    writer.close()
    os.remove(file)
    os.rename(write_file, file)
    print(">>&#x6587;&#x4EF6;%s&#x6BCF;&#x884C;&#x672B;&#x5C3E;','&#x5DF2;&#x5220;&#x9664;" % file)

接下来就是重头戏,连接数据库并导入csv文件;

首先需要安装一个MySQL数据库,安装完后建立一个空的数据库;

Python调用MySQL的组件为pymysql,以下为实现过程:

import pymysql

&#x8FDE;&#x63A5;&#x6570;&#x636E;&#x5E93;
db = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = '&#x7528;&#x6237;&#x540D;',
    passwd = '&#x5BC6;&#x7801;',
    db = '&#x5E93;&#x540D;',
    charset = 'utf8')
&#x5EFA;&#x7ACB;&#x8FDE;&#x63A5;&#x6E38;&#x6807;
cursor=db.cursor()
print('>>&#x5DF2;&#x8FDE;&#x63A5;&#x6570;&#x636E;&#x5E93;&#xFF0C;&#x5904;&#x7406;&#x4E2D;&#x3002;&#x3002;&#x3002;')

&#x51FD;&#x6570;&#xFF1A;&#x5220;&#x9664;&#x65E7;&#x8868;&#xFF0C;&#x4ECE;csv&#x6587;&#x4EF6;&#x521B;&#x5EFA;&#x65B0;&#x8868;
def load_csv(csv_file_path, table_name, database, decoding):
    print(">>&#x6587;&#x4EF6;%s&#x5F00;&#x59CB;&#x521B;&#x5EFA;&#x8868;&#x3002;&#x3002;&#x3002;" % csv_file_path)
    # &#x6253;&#x5F00;&#x6587;&#x4EF6;&#xFF0C;&#x521B;&#x5EFA;&#x8868;&#x548C;&#x8868;&#x5934;
    file = open(csv_file_path, 'r', encoding = decoding)
    # reader&#xFF1A;readline&#x8BFB;&#x53D6;csv&#x6587;&#x4EF6;&#x7B2C;&#x4E00;&#x884C;&#xFF0C;&#x7528;&#x4E8E;&#x521B;&#x5EFA;&#x8868;&#x5934;
    reader = file.readline()
    b = reader.split(',')
    colum = ''
    head = ''
    # for&#x5FAA;&#x73AF;&#xFF1A;&#x4ECE;csv&#x6587;&#x4EF6;&#x7B2C;&#x4E00;&#x884C;&#x9010;&#x4E2A;&#x7F16;&#x8F91;&#x5B57;&#x7B26;&#x4E32;&#xFF0C;&#x521B;&#x5EFA;&#x8868;&#x5934;&#x5B57;&#x7B26;&#x4E32;
    for a in b:
        # &#x53BB;&#x6389;&#x6BCF;&#x4E2A;&#x5B57;&#x7B26;&#x4E32;&#x7684;"&#x3001;&#x6362;&#x884C;
        c = a.strip('"' and '"\n')
        # head&#xFF1A;&#x7528;&#x4E8E;&#x9010;&#x884C;&#x63D2;&#x5165;&#x6570;&#x636E;&#x65F6;&#x9009;&#x62E9;&#x8868;&#x5934;&#xFF0C;&#x4E2D;&#x6587;&#x8868;&#x5934;&#x9700;&#x8981;&#x52A0;&#x95F4;&#x9694;&#x53F7;&#xFF08;1&#x5DE6;&#x8FB9;&#x7684;&#x952E;&#xFF09;
        head = head + "" + c + "" + ','
        # colum&#xFF1A;&#x7528;&#x4E8E;&#x521B;&#x5EFA;&#x8868;&#x65F6;&#x6307;&#x5B9A;&#x8868;&#x5934;&#xFF0C;&#x4E2D;&#x6587;&#x8868;&#x5934;&#x9700;&#x8981;&#x52A0;&#x95F4;&#x9694;&#x53F7;&#xFF08;1&#x5DE6;&#x8FB9;&#x7684;&#x952E;&#xFF09;&#xFF0C;&#x521B;&#x5EFA;&#x8868;&#x65F6;&#x9700;&#x8981;&#x52A0;&#x6570;&#x636E;&#x7C7B;&#x578B;&#xFF08;varchar(255)&#xFF09;
        colum = colum + "" + c + "" + ' varchar(255),'
    # [:-1]&#x53BB;&#x9664;&#x672B;&#x5C3E;&#x9017;&#x53F7;
    colum = colum[:-1]
    head = head[:-1]
    cursor.execute('use %s' % database)
    cursor.execute('set names utf8')
    cursor.execute('set character_set_connection=utf8')
    cursor.execute('drop table if exists %s' % table_name)
    # &#x521B;&#x5EFA;&#x8868;&#xFF0C;&#x589E;&#x52A0;id&#x5217;
    create_sql = 'create table if not exists ' + table_name + ' (id INT,' + colum + ')' + ' DEFAULT CHARSET=utf8'
    cursor.execute(create_sql)
    # while&#x5FAA;&#x73AF;&#xFF1A;&#x9010;&#x884C;&#x5BFC;&#x5165;csv
    # rows_raw&#xFF1A;readlines&#x9010;&#x884C;&#x8BFB;&#x53D6;csv
    rows_raw = file.readlines()
    count_rows = len(rows_raw)
    i = 0
    while i < count_rows:
        ii = str(i + 1)
        d = rows_raw[i]
        d = d.split(',')
        rows = ''
        for e in d:
            # &#x53BB;&#x6389;&#x6BCF;&#x4E2A;&#x5B57;&#x7B26;&#x4E32;&#x7684;"&#x3001;&#x6362;&#x884C;&#x3001;\&#xFF08;\&#x4F1A;&#x5728;insert&#x65F6;&#x62A5;&#x9519;&#xFF09;
            f = e.strip('"')
            f = f.strip('"\n')
            f = f.strip('\\')
            rows = rows + "'" + f + "'" + ','
        # rows&#x589E;&#x52A0;id&#x5217;&#x6570;&#x503C;
        rows = "'" + ii + "'," + rows[:-1]
        print(rows)
        insert_sql = 'insert into %s (id,%s) values (%s)' % (table_name, head, rows)
        cursor.execute(insert_sql)
        cursor.execute('commit')
        i = i + 1
    print(">>&#x6587;&#x4EF6;%s&#x521B;&#x5EFA;&#x8868;&#x5B8C;&#x6210;" % csv_file_path)

load_csv('XXX.csv', '&#x8868;&#x540D;', '&#x5E93;&#x540D;', 'UTF-8')

基本思路就是逐行读取csv文件,第一行作为表头,各个字段拼接成一句create table;

后续表中的内容同理,每行逐字段读取,拼接成一句insert into;

需要参与计算的每个数据表都会根据这个功能导入到数据库中,可以开始下一步的计算。

[En]

Each data table that needs to participate in the calculation is imported into the database according to this function, and you can start the next step of calculation.

三、MySQL数据库按照考核标准计算结果,生成结果文件,再通过邮件发送结果文件

MySQL的计算基本上就是按照考核标准处理多张表中的数据,通过SQL语句实现,因为涉及到公司内部信息比较多,这里就不展示细节了;

可以展示一下实现的框架,通过cursor.execute(“”)来执行SQL语句,但是不能把分号”;”放在里面,目前没找到解决方法,因此每一句都是分开的:

print('>>&#x5F00;&#x59CB;&#x751F;&#x6210;&#x7ED3;&#x679C;&#x8868;&#x3002;&#x3002;&#x3002;')

## SQL1
cursor.execute("update XXX set XXX=replace(XXX, 'A', 'B')")
print('>>XXX&#xFF0C;&#x5DF2;&#x5B8C;&#x6210;')

## SQL2
cursor.execute("delete from XXX where XXX != 'XXXXX'")
cursor.execute("delete from XXX where XXX = '' and XXX = '' and XXX = ''")
print('>>XXX&#xFF0C;&#x5DF2;&#x5B8C;&#x6210;')

下面是结果文件的生成,用xlwt导出结果表,还加入了一个按照内容调整每列宽度的功能,看起来方便些,最后断开数据库连接:

import xlwt
import copy

book = xlwt.Workbook()
def export_excel(table_name, sheet_name):
    cursor.execute('select * from %s' % table_name)  # &#x83B7;&#x53D6;&#x8868;
    fields = [field[0] for field in cursor.description]  # &#x83B7;&#x53D6;&#x6240;&#x6709;&#x5B57;&#x6BB5;&#x540D;
    all_data = cursor.fetchall()  # &#x6240;&#x6709;&#x6570;&#x636E;
    # &#x5199;&#x5165;excel
    sheet = book.add_sheet('%s' % sheet_name)
    col_num = [0 for x in range(0, len(fields))]
    col_list = []
    # &#x83B7;&#x53D6;&#x8868;&#x5934;&#x5BBD;&#x5EA6;&#xFF0C;&#x5B58;&#x5165;col_list
    for col,field in enumerate(fields):
        sheet.write(0,col,field)
        col_num[col] = len(str(field).encode('gb18030'))
    col_list.append(copy.copy(col_num))
    row = 1
    # &#x83B7;&#x53D6;&#x6BCF;&#x884C;&#x5BBD;&#x5EA6;&#xFF0C;&#x5B58;&#x5165;col_list
    for data in all_data:
        for col,field in enumerate(data):
            sheet.write(row,col,field)
            col_num[col] = len(str(field).encode('gb18030'))
        col_list.append(copy.copy(col_num))
        row += 1
    # &#x51FD;&#x6570;&#xFF1A;&#x83B7;&#x53D6;&#x6700;&#x9002;&#x5408;&#x7684;&#x5217;&#x5BBD;
    def get_max_col(max_list):
        line_list = []
        # i&#x8868;&#x793A;&#x884C;&#xFF0C;j&#x4EE3;&#x8868;&#x5217;
        for j in range(len(max_list[0])):
            line_num = []
            for i in range(len(max_list)):
                line_num.append(max_list[i][j])  # &#x5C06;&#x6BCF;&#x5217;&#x7684;&#x5BBD;&#x5EA6;&#x5B58;&#x5165;line_num
            line_list.append(max(line_num))  # &#x5C06;&#x6BCF;&#x5217;&#x6700;&#x5927;&#x5BBD;&#x5EA6;&#x5B58;&#x5165;line_list
        return line_list
    # &#x8C03;&#x7528;&#x51FD;&#x6570;get_max_col&#x4ECE;col_list&#x4E2D;&#x83B7;&#x53D6;&#x6700;&#x9002;&#x5408;&#x7684;&#x5217;&#x5BBD;&#xFF0C;&#x5E76;&#x8FDB;&#x884C;&#x8C03;&#x6574;
    col_max_num = get_max_col(col_list)
    for i in range(0, len(col_max_num)):
        sheet.col(i).width = 256 * (col_max_num[i] + 2)
    print(">>&#x7ED3;&#x679C;&#x8868;'%s'&#x5DF2;&#x5BFC;&#x51FA;&#x4E3A;'%s'" % (table_name, sheet_name))

export_excel('&#x6570;&#x636E;&#x5E93;&#x4E2D;&#x8868;1', '&#x5BFC;&#x51FA;&#x8868;1')
export_excel('&#x6570;&#x636E;&#x5E93;&#x4E2D;&#x8868;2', '&#x5BFC;&#x51FA;&#x8868;2')
export_excel('&#x6570;&#x636E;&#x5E93;&#x4E2D;&#x8868;3', '&#x5BFC;&#x51FA;&#x8868;3')
book.save('XXX.xls')
print('>>&#x7ED3;&#x679C;&#x6587;&#x4EF6;&#x5DF2;&#x751F;&#x6210;')

&#x63D0;&#x4EA4;&#x5E76;&#x65AD;&#x5F00;&#x6570;&#x636E;&#x5E93;&#x8FDE;&#x63A5;
cursor.execute('commit')
cursor.close()
db.close()
print('>>&#x6570;&#x636E;&#x5E93;&#x8FDE;&#x63A5;&#x5DF2;&#x65AD;&#x5F00;')

最后,通过电子邮件发送检查结果:

[En]

Finally, the inspection results are sent by email:

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.header import Header
import smtplib

smtpserver = 'XXX' # SMTP&#x670D;&#x52A1;&#x5668;&#x57DF;&#x540D;
username = 'XXX' # &#x767B;&#x5F55;&#x7528;&#x6237;&#x540D;
password = 'XXX' # &#x767B;&#x5F55;&#x5BC6;&#x7801;
sender = 'XXX' # &#x53D1;&#x9001;&#x8005;&#x5730;&#x5740;
receiver = ['XXX','XXX'] # &#x63A5;&#x6536;&#x8005;&#x5730;&#x5740;
subject = '&#x7EC8;&#x7AEF;&#x8003;&#x6838;&#x9879;&#x68C0;&#x67E5;&#x7ED3;&#x679C;' # &#x90AE;&#x4EF6;&#x6807;&#x9898;
subject = Header(subject, 'utf-8').encode()
&#x6784;&#x5EFA;&#x90AE;&#x4EF6;&#x5BF9;&#x8C61;
msg = MIMEMultipart('mixed')
msg['Subject'] = subject
msg['From'] = 'XXX'
msg['To'] = ";".join(receiver)
&#x6784;&#x9020;&#x6587;&#x5B57;&#x5185;&#x5BB9;
text = "XXXXXX"
text_plain = MIMEText(text, 'plain', 'utf-8')
msg.attach(text_plain)
&#x6784;&#x9020;&#x9644;&#x4EF6;
sendfile = open('XXX.xls').read()
file_att = MIMEText(sendfile, 'base64', 'utf-8')
file_att["Content-Type"] = 'application/octet-stream'
file_att.add_header('Content-Disposition', 'attachment', filename = 'XXX.xls')
msg.attach(file_att)
&#x53D1;&#x9001;&#x90AE;&#x4EF6;
smtp = smtplib.SMTP()
smtp.connect(smtpserver)
smtp.login(username, password)
smtp.sendmail(sender, receiver, msg.as_string())
smtp.quit()
print('>>&#x7ED3;&#x679C;&#x6587;&#x4EF6;&#x5DF2;&#x901A;&#x8FC7;&#x90AE;&#x4EF6;&#x53D1;&#x9001;')

整个工具编写完成后,总共有1000多行。先运行40分钟,然后在平台上优化导出表的前处理,运行时间减少到10分钟。

[En]

After the whole tool is written, there are 1000 + lines in total. At first, it takes 40 minutes to run, then the preprocessing of the exported table on the platform is optimized, and the running time is reduced to 10 minutes.

不得不说Python做自动化的工具是真的很好用,只需要熟悉现成的模块,组合一下,就能实现很强大的功能。

Original: https://www.cnblogs.com/BTday/p/16117880.html
Author: BTday
Title: 自动化终端考核检查系统的搭建过程

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/500004/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球