Windows 8,10,11互联网和内网环境下利用Python及其第三方库实现数据库（MySQL和Oracle）和邮件（SMTP）自动化

蓝桉
518 阅读12分钟

A.互联网状态：Python部署，VSCode部署Python插件，第三库介绍及其下载

检查Python和pip（python自带的库（library）安装器）的安装状态

检查Python的版本（Version）：python --version

检查pip的版本：pip --version

安装Python 3

安装python3.9.7的windows版：www.python.org/ftp/python/…

安装本项目所需的Python库

本项目所需的第三方库如下：

import xlwt
import pymysql
import time
import smtplib
import imaplib
import email
import pandas as pd
#额外下载openpyxl

除Python自带的库之外，我们都需要来亲自安装，安装方法：

  1. 需要鼠标右击任务栏开始键位置，选择Windows终端（管理员）

image.png

image.png

  1. 除了pymysql之外，第三方库在Windows终端（管理员）的安装命令如下：
pip install xlwt
pip install cryptography
pip install smtplib
pip install openpyxl
pip install pandas
pip install imaplib
  1. 对于pymysql来说，安装方法如下（系统需要安装完visual c++ 9.0，并且安装cryptography第三方库）：
  • a. 下载pymysql安装包，下载地址：github.com/PyMySQL/PyM…
  • b. 将压缩包进行解压，在命令行中切换到解压文件所在的位置，运行python setup.py install，最后会出现Finished dependencies...
  • c. 运行python，输入import pymysql,验证是否安装成功。

B.互联网状态：云服务器MySQL数据库及表结构介绍

云服务器环境：基于Linux的CentOS 7，数据库使用的时候需要指明下列元素：

  1. host = '106.54.138.175'（数据库所在的云服务器地址）
  2. user = 'chinamobile'（数据库用户名）
  3. pwd = 'cmcc123456'（数据库密码）
  4. port = 3306
  5. db = 'chinamobile'

在此基础上，你可以选择利用宝塔面板进行数据库的安装，也可以利用Docker的MySQL镜像进行安装

本次测试用例的MySQL版本：8，在这里，具体的安装细节我们就不展示了，想学的可以在网上百度一下学学，这个不是难点

下面列举在宝塔界面下我们的数据库配置：

image.png

为了保障客户的隐私，所有测试表中的手机号码都是随机跑出来的

image.png

测试表数据如上图所示，表结构为3列servnumber（手机号码），countyname（号码归属区县），zdkh（重点客户标识）

C.互联网状态：互联网邮件协议及其配置接口介绍

想要利用python来实现邮件服务器内邮件的读取和发送，那就必须要了解邮件的相关协议

那么什么是SMTP和IMAP呢？POP3请自行百度！

SMTP 的全称是“Simple Mail Transfer Protocol”，即简单邮件传输协议。它是一组用于从源地址到目的地址传输邮件的规范，通过它来控制邮件的中转方式。SMTP 协议属于 TCP/IP 协议簇，它帮助每台计算机在发送或中转信件时找到下一个目的地。SMTP 服务器就是遵循 SMTP 协议的发送邮件服务器。\

SMTP 认证，简单地说就是要求必须在提供了账户名和密码之后才可以登录 SMTP 服务器

在本项目中，SMTP用来登录邮箱发送邮件的，需要指明下列元素：

  1. mailhost（邮件服务器的地址，例如smtp.139.com）
  2. username（自己本机的邮箱地址）
  3. password（自己本机的邮箱密码）
  4. port（邮件服务器传输端口）

IMAP全称是Internet Mail Access Protocol，即交互式邮件存取协议，它是跟POP3类似邮件访问标准协议之一。

不同的是，开启了IMAP后，您在电子邮件客户端收取的邮件仍然保留在服务器上，同时在客户端上的操作都会反馈到服务器上，如：删除邮件，标记已读等，服务器上的邮件也会做相应的动作。所以无论从浏览器登录邮箱或者客户端软件登录邮箱，看到的邮件以及状态都是一致的。

在本项目中，IMAP用来接收别人发给自己的邮件的，需要指明下列元素：

  1. mailhost（邮件服务器的地址，例如smtp.139.com）
  2. username（自己本机的邮箱地址）
  3. password（自己本机的邮箱密码）
  4. port（邮件服务器传输端口）
  5. mailfrom（邮件来源者）

D.互联网状态：项目内声明的函数介绍及其解释

将源代码取消展开如下（展示结构）：

image.png

代码量在280行左右，体积不大，得益于Python广泛的开源第三方库，才能写这么少！

下面针对每个函数进行解释：

judgefrom函数：负责判断邮件来源

这个函数是自己写的，目的很简单，判断今天的邮件来源是否为客服部，如果是对应邮箱返回整数1，否则整数0

外部输入参数：judgemailfrom，字符串（string），待判断的邮箱字符串

def judgefrom(judgemailfrom):
    #判断今天的邮件来源是否为客服部
    
    if (judgemailfrom=="mickeywang98@139.com"):
        return 1
    else:
        return 0

judgedate函数：负责判断邮件附件日期和命名格式

同样的，逻辑和上面差不多，time.strftime函数负责以时间元组，返回以可读字符串表示的当地时间，格式由参数 format 决定，函数格式time.strftime(format[, t])

外部输入参数：filename，字符串（string），待判断的邮箱附件文件名带后缀

def judgedate(filename):
    # 并且邮件来自本日
    # 格式符合"kefushuju_"+当前时间
    if (filename=="kefushuju_" + time.strftime("%Y-%m-%d")+'.xlsx') or (filename=="kefushuju_" + time.strftime("%Y-%m-%d")+'.xls'):
        return 1
    else:
        return 0

excel_one_line_to_list函数：将excel第一列转换为数组list

这里用到了pandas的read_excel函数，header=None是表示filename这个xlsx文件没有表头，不需要跳过第一行读取数据

外部输入参数：filename，字符串（string），待判断的邮箱附件文件名带后缀

str(filename)是强制转换为字符串，写法类似于C语言的强制转换函数

def excel_one_line_to_list(filename):
    #将xls第一列的servnumber处理成整形数组result
    df = pd.read_excel(filename,header=None)  # 读取项目名称列,打开同级目录下的文件

    df_li = df.values.tolist()
    result = []
    for s_li in df_li:
        result.append(s_li[0])
    print(result)
    return result

parseHeader函数：解析邮件头部

在这里，Python 3不需要进行编码（encode）统一码（unicode）操作，原函数被我修改了一下，该函数在getMail中被使用

email.utils.parseaddr(address)是模块中专门用来解析邮件地址的函数

外部输入参数：message，类型为email.message，在邮件包中的核心类是消息类message，从email.message模块导入。它是为电子邮件对象模型的基类。消息提供的核心功能，用于设置和查询报头字段，以及用于访问消息体

def parseHeader(message):
    #解析邮件头部
    subject = message.get('subject')  
    h = email.header.Header(subject)
    dh = email.header.decode_header(h)
    #subject = unicode(dh[0][0], dh[0][1]).encode('gb2312')
    subject = (dh[0][0], dh[0][1])

    # 主题
    print (subject)
    print ("</br>")
    # 发件人
    print ("From:"+ email.utils.parseaddr(message.get('from'))[1])
    print ("</br>")
    # 收件人
    print ("To:" + email.utils.parseaddr(message.get('to'))[1])
    print ("</br>")
    # 抄送人
    print ("Cc:"+ email.utils.parseaddr(message.get_all('cc'))[1])

parseBody函数：负责解析邮件内容

该函数在getMail中被使用

外部输入参数：message，类型为email.message

multipart代指数据块,str(fname).split("'")[1]将fname强制转为str后利用'符号将字符串分割为多个字符串数组，取出数组中的第二个值即为文件名（fname的字符串格式为b'文件名'）

def parseBody(message):
    #解析邮件内容
    # 循环信件中的每一个mime的数据块
    for part in message.walk():
        # 这里要判断是否是multipart，是的话，里面的数据是一个message 列表
        if not part.is_multipart():
            charset = part.get_charset()
            # print 'charset: ', charset
            contenttype = part.get_content_type()
            # print 'content-type', contenttype
            name = part.get_param("name") #如果是附件，这里就会取出附件的文件名


            if name:#在这里额外添加中国移动的条件
                # 有附件
                # 下面的三行代码只是为了解码像 =?gbk?Q?=CF=E0=C6=AC.rar?=这样的文件名
                fh = email.header.Header(name)
                fdh = email.header.decode_header(fh)
                fname = fdh[0][0]
                print ('附件名:',fname)
                 #解码出附件数据，然后存储到文件中
                attach_data = part.get_payload(decode=True)
      
                try:
                    f = open(fname, 'wb') #注意一定要用wb来打开文件，因为附件一般都是二进制文件
                except:
                    print ("附件名有非法字符，自动换一个")
                    f = open('kefushuju_'+time.strftime("%Y-%m-%d")+'.xlsx', 'wb')
                f.write(attach_data)
                f.close()
            else:
                #不是附件，是文本内容
                print (part.get_payload(decode=True)) # 解码出文本内容，直接输出来就可以了。
                # pass
            # print '+'*60 # 用来区别各个部分的输出
    return str(fname).split("'")[1]

getMail函数：获得邮件内容（文本&附件）

调用上面声明的parseBody和parseHeader函数，利用imap协议来建立从服务器收取邮件的链接，建立serv连接后利用search来寻找邮箱名

外部输入参数：host, username, password, port, mailfrom，类型为字符串（string），分别为收信服务器地址，邮箱，邮箱密码，服务端口，邮箱发信人

def getMail(host, username, password, port, mailfrom):
    #global fn
    try:
        serv = imaplib.IMAP4_SSL(host, port)
    except Exception as e:
        serv = imaplib.IMAP4(host, port)
 
    serv.login(username, password)
    serv.select()
    # 搜索邮件内容
    typ, data = serv.search(None, '(FROM "' + mailfrom + '")')

    #调用中国移动邮件来源判断
    if judgefrom(mailfrom)==1:
        x=1
    else:
        return

    count = 1
    pcount = 1
    for num in data[0].split()[::-1]:
        typ, data = serv.fetch(num, '(RFC822)')
        text = data[0][1]
        message = email.message_from_bytes(text)   # 转换为email.message对象

        parseHeader(message)
        print ("</br>")
        
        f=parseBody(message)   #返回值是为了把文件名返回主函数
        if f.strip()!='':
            fn=f


        pcount += 1
        if pcount > count:
            break
 
    serv.close()
    serv.logout()
    return fn#返回值是为了把文件名返回主函数

export函数：连接数据库，匹配手机号信息，写入xls

建立数据库连接实例，利用SQL拼接的方式逐条查找，逐行逐列写入xls即可

外部输入参数：servnumber，类型为数组列表（list），传入手机号信息

def export(servnumber):
    
    #数据库连接
    conn = pymysql.connect(db=db, user=user,password=pwd, host=host, port=port)#这个链接的配置
    cursor = conn.cursor()#链接数据库得到的实例

    result=()
    counts=0
    sql = 'select servnumber,countyname,zdkh from zdkh_test '


    print(servnumber)


    for num in servnumber:
        cursor.execute(sql + "where servnumber='" + str(num) + "';") #执行sql语句
        result = cursor.fetchall() + result #返回值是多个元组,即返回多个行记录
        counts = cursor.rowcount + counts #记录这个sql执行完毕是多少行


    print("Select  " + str(counts) + "  Records")
    # cursor.scroll(0, mode='relative')
    
    fields = cursor.description  # 数据表的标题
    workbook = xlwt.Workbook(encoding='utf-8')  # 创建excel文档
    
    # 根据sheet_name创建excel文档的sheet
    sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)

    # 写入数据表的文件头
    for field in range(0, len(fields)):  
        sheet.write(0, field, fields[field][0])
    
    # 逐行逐列的添加数据
    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表格
    return counts

send_email函数：发送邮件

外部输入参数：count，类型为整数（int），值为遍历数据库中查找统计的条数

def send_email(count):  
    # ----这是文字部分-----
    content = '''Dear 客服部, \n附件是每日统计情况，请查收！\n总计结果数位：''' + str(count)
    part = MIMEText(content, 'plain', 'utf-8')
    msg.attach(part)
    if count > 0:
        #这是附件部分
        # xls类型附件
        file_name = 'C:/Users/Lenovo/Desktop/PYOUT/' + 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_SSL('smtp.chinamobile.com', 465)  #连接smtp邮件服务器
        s = smtplib.SMTP_SSL('smtp.139.com', 465)  #连接smtp邮件服务器

        s.login(_user, _pwd)
        #s.sendmail(_user, areceiver.split(',') + acc.split(','), msg.as_string())  # send mail
        s.sendmail(_user, areceiver, msg.as_string())  # send mail

        print("Email send successfully")
        s.close()

    else:
        print("nothing to send!")

main函数：负责调用上述子程序实现

声明各种变量，调用子程序完成整个功能模块

判断文件的日期是否为当天，如果不是的话，后面有个if会拒绝运行数据库查询语句模块和邮件发送模块

#####################主程序环节########################

##########################运行前所有的变量配置和msg配置###########################
# 连接数据库的参数：主机，用户，密码，端口及数据库名称

host = '106.54.138.175'
user = 'chinamobile'
pwd = 'cmcc123456'
port = 3306
db = 'chinamobile'

# 变量声明
sheet_name = 'report' + time.strftime("%Y-%m-%d")
filename = 'report_' + time.strftime("%Y-%m-%d" + "-" + "%H%M%S") + '.xls'
out_path = "C:/Users/Lenovo/Desktop/PYOUT/report_" + \
    time.strftime("%Y-%m-%d" + "-" + "%H%M%S") + \
    ".xls"  # 路径文件名使用日期时间来命名，但是文件命名不支持冒号：所以去掉冒号
cur_path = 'C:/Users/Lenovo/Desktop/PYOUT'
print(out_path)

#workbook = xlwt.Workbook(encoding='utf-8')
#sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
# workbook.save(out_path)


#本机邮箱用户名密码

_user = "mickeywang1998@139.com"
_pwd = "Wcq980220"
areceiver = "mickeywang98@139.com"
#接收者
#areceiver = "mickeywang1998@139.com"
#抄送
acc = ""
#发送本机的历史邮件来源
#mailfrom = "111@139.com"
mailfrom = "mickeywang98@139.com"
#如名字所示Multipart就是多个部分
msg = MIMEMultipart()
msg["Subject"] = u'[Data Select_' + time.strftime("%Y-%m-%d") + u']'
msg["From"] = _user
msg["To"] = areceiver
msg["Cc"] = acc

#sendhost = "imap.chinamobile.com" 
sendhost = "imap.139.com"
# "pop.mail_serv.com"

########################调用写好的子程序实现程序功能##########################


#getMail里面调用解析邮件头和邮件文件，获得邮件附件，并将邮件附件路径写入字符串
mainfilename=getMail(sendhost, _user, _pwd, 993, mailfrom)

print(mainfilename)
print(mainfilename)
print(mainfilename)
print(mainfilename)
print(mainfilename)

#将收件附件当中的servnumber读到数组中，准备链接数据库进行SQL匹配获取
servnumber=excel_one_line_to_list(mainfilename)



if judgedate(mainfilename)==1:
    #链接数据库，获取数据，写入xlsx
    countnum=export(servnumber)

    #发送邮件
    send_email(countnum)

print("Running successfully!!!")

E.互联网状态：在Windows环境下定时运行脚本

如何将Python的py文件打包成Windows的exe文件

首先，要对写好的python程序进行编译和打包，我们需要安装pyinstaller库，总不能每次都开着编辑器，看着源码跑吧

image.png

安装完成之后，我们进行编译，打开python代码所在文件夹

image.png

点击红色位置，输入cmd，按下回车enter

image.png

输入下面的代码，回车enter，等待一分钟左右，在这个目录下面会出dist文件夹

pyinstaller --onefile --nowindowed --icon="C:\Users\Lenovo\Desktop\PYTEST\cmcc.ico" cmcc_sendemail.py

image.png

image.png

而这个exe的图标就是命令里面的cmcc.ico

如何实现定时运行exe程序

类似linux中的脚本sh，在windows中我们也有对应的bat脚本

我们可以编写脚本来完成以下动作，每日定时12:00检查邮件服务器有没有客服部的邮件，取数原路返回对应的邮件

image.png

新建txt，敲完下面代码，改后缀为bat

@echo off
SCHTASKS /Create /TN sendemail /TR C:\Users\Lenovo\Desktop\PYTEST\dist\cmcc_sendemail.exe /SC DAILY /ST 02:02
pause

代码什么意思呢？

  1. /create表示创建
  2. /tn表示创建的任务叫什么名字
  3. /tr引号表示具体要执行的路径
  4. /sc表示执行的频率，once就是执行一次
  5. /st表示开始执行的时间

运行bat显示下面图片（之前我存在同名进程，进行覆盖）

image.png

之后只要你的windows不关机到了2:02，就会运行对应的命令！！！大功告成！！！

F.内网状态：涉及保密信息，处理完毕之后更新，敬请期待！

avatar
文章
阅读
粉丝