【python自动化运维】python第三方运维模块应用

本文链接：https://blog.csdn.net/leo__programmer/article/details/134264420

一、pymysql模块 —— 数据库编程

1、介绍

第三方模块
作用:
连接MySQL数据库

1.1、操作流程

创建数据库连接
基于连接创建游标 cursor
数据库读写操作
关闭游标
关闭数据库连接

2、pymysql模块的使用

2.1、准备工作

准备MySQL数据库
安装pymysql模块

pip install pymysql

安装配置MySQL数据库

[root@localhost ~]# yum install -y mariadb-server 

[root@localhost ~]# systemctl enable --now mariadb

[root@localhost ~]# netstat -tunlp | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      16685/mysqld     

[root@localhost ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> create database testdb charset utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> 
MariaDB [testdb]> create table servers(
    -> id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    -> ip CHAR(32),
    -> ssh_user CHAR(10),
    -> ssh_pwd CHAR(20),
    -> ssh_port INT);
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> insert into servers(ip,ssh_user,ssh_pwd,ssh_port) values("192.168.183.13","root","redhat",22),("192.168.183.14","root","redhat",22);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from servers;
+----+----------------+----------+---------+----------+
| id | ip             | ssh_user | ssh_pwd | ssh_port |
+----+----------------+----------+---------+----------+
|  1 | 192.168.183.13 | root     | redhat  |       22 |
|  2 | 192.168.183.14 | root     | redhat  |       22 |
+----+----------------+----------+---------+----------+
2 rows in set (0.00 sec)

MariaDB [testdb]> grant all on testdb.* to 'admin'@"%" identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> exit
Bye

2.2、执行查询操作

import pymysql

# 执行查询操作
def selectData():
    # 建立数据库连接
    dbconn = pymysql.connect(host="192.168.183.10", user="admin", password="redhat", database="game")
    # 建议游标
    cr = dbconn.cursor()

    # 执行SQL语句
    test_sql = "select * from account"
    # 直接返回数据的行数
    result = cr.execute(test_sql)
    print(result)


    # 获取数据   fetchall() 获取所有数据
    # data_01 = cr.fetchall()
    # print(data_01)
    # print(data_01[0][1])
    # for u_id, u_name, u_pwd, u_level in data_01:
    #     print("用户名: %s, 密码: %s, 等级: %s" % (u_name, u_pwd, u_level))
    # print("-----------" * 8)

    # fetchmany(n)  获取前n行数据
    # data_02 = cr.fetchmany(1)
    # print(data_02)

    # fetchone()    获取单条数据
    data_03 = cr.fetchone()
    print(data_03)

    data_04 = cr.fetchone()
    print(data_04)



    cr.close()
    dbconn.close()


if __name__ == '__main__':
    selectData()```

2.3、执行写操作

def insertData():
    dbconn = pymysql.connect(host="192.168.183.10", user="admin", password="redhat", database="game")
    cr = dbconn.cursor()

    test_sql = "insert into account(name,password,level) values('tome','abc',67)"

    result = cr.execute(test_sql)
    # 提交事务
    dbconn.commit()

    cr.close()
    dbconn.close()

3、服务器信息管理

import pymysql
import sys

# 建立数据库连接
def MySQLCONN():
    try:
        dbconn = pymysql.connect(host="192.168.183.13",user="admin", password="redhat", database="testdb")
    except Exception as e:
        print("连接数据库失败，检查地址、用户名、密码是否正确")
        print(e)
        sys.exit()
    cr = dbconn.cursor()
    return cr, dbconn

# 获取数据库中服务器ssh连接信息
def getServerSSHInfo():
    cr, dbconn = MySQLCONN()
    query_server_sql = "select ip, ssh_user, ssh_pwd, ssh_port from servers"
    result = cr.execute(query_server_sql)
    if result != 0:
        servers = cr.fetchall()
        return servers
    else:
        return False
    cr.close()
    dbconn.close()


if __name__ == '__main__':
    servers_info = getServerSSHInfo()
    for ip, ssh_user, ssh_pwd, ssh_port in servers_info:
        print("ssh %s@%s -p %s" % (ssh_user, ip, ssh_port))

4、导入excel中的数据到数据库

MariaDB [testdb]> create table info(
    -> id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    -> name CHAR(50),
    -> age INT,
    -> level CHAR(50));

import pandas
import pymysql
import sys

# 向数据库插入数据
def insertData(name, age, level):
    try:
        dbconn = pymysql.connect(host="192.168.183.13", user="admin", password="redhat", database="testdb")
    except Exception as e:
        print("数据库连接失败")
        print(e)
        sys.exit()
    cr = dbconn.cursor()

    insert_sql = "insert into info(name, age, level) value('%s', '%s', '%s')" % (name, age, level)
    result = cr.execute(insert_sql)
    dbconn.commit()

    cr.close()
    dbconn.close()


# 读取excel文件中的数据
def loadExcelToDB():
    excel_file = r"E:\project0528\utils\test.xlsx"
    result = pandas.read_excel(excel_file)
    # 返回是DataFrame类型的数据
    # print(result)
    # print(type(result))

    # itertuples() 将数据转换成元组
    for index, name, age, level in result.itertuples():
        insertData(name=name, age=age, level=level)

if __name__ == '__main__':
    loadExcelToDB()

二、paramiko模块 —— 实现SSH

1、paramiko模块

1.1、作用

基于python实现的SSH协议

执行远程命令
上传、下载文件

1.2、安装模块

pip install paramiko

(project0805_venv) D:\>python
Python 3.8.6 (tags/v3.8.6:db45529, Sep 23 2020, 15:52:53) [MSC v.1927 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>>
>>> import paramiko
>>>
>>> exit()

2、执行远程命令

import paramiko

def sshCMD():
    # 创建ssh客户端工具
    ssh_client = paramiko.SSHClient()
    # 设置丢失主机秘钥的策略  自动添加的策略 作用是本地没有远程的服务器的秘钥信息会自动接收
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh_client.connect(hostname="192.168.140.10", username="root", password="redhat", port=22)

    '''
        返回三个特殊文件: 标准输入、标准输出、标准错误
    '''
    stdin, stdout, stderr = ssh_client.exec_command("df -hT")
    print(stdout.read().decode(encoding="utf-8"))

    ssh_client.close()


if __name__ == '__main__':
    sshCMD()

示例：批量执行命令

import paramiko
import sys

def exeCMD(ip, user, pwd, cmd, port=22):
    ssh_client = paramiko.SSHClient()
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

    try:
        ssh_client.connect(hostname=ip, username=user, password=pwd, port=port)
    except Exception as e:
        print("检测服务器地址、用户名、密码是否有误!!!!!")
        print(e)
        sys.exit()	# 服务器退出

    stdin, stdout, stderr = ssh_client.exec_command(cmd)

    print(stdout.read().decode(encoding="utf-8"))

    ssh_client.close()

if __name__ == '__main__':
    cmd = input("命令: ").strip()

    servers = {
        "192.168.140.10": {
            "ssh_user": "root",
            "ssh_pwd": "redhat",
            "ssh_port": 22
        },
        "192.168.140.11": {
            "ssh_user": "root",
            "ssh_pwd": "redhat",
            "ssh_port": 333
        }
    }

    for server_ip, ssh_info in servers.items():
        print("服务器[%s]执行[%s]结果如下: " % (server_ip, cmd))
        exeCMD(ip=server_ip, user=ssh_info.get("ssh_user"), pwd=ssh_info.get("ssh_pwd"),
               port=ssh_info.get("ssh_port"), cmd=cmd)
        print("-------------" * 10)

3、上传、下载文件

def fileOP():
    # 创建SSH连接
    ssh_conn = paramiko.Transport(("192.168.140.10", 22))
    ssh_conn.connect(username="root", password="redhat")

    # 基于SSH连接创建FTP客户端
    ftp_client = paramiko.SFTPClient.from_transport(ssh_conn)

    # 下载文件
    ftp_client.get("/etc/fstab", r"D:\project0805\files\fstab")

    # 上传文件
    ftp_client.put(r"D:\project0805\files\test02.html", "/tmp/test02.html")

    ftp_client.close()
    ssh_conn.close()


if __name__ == '__main__':
    fileOP()

示例：批量上传文件

import paramiko
import os
import sys 
from funDemo import fileManager as fm

# 上传文件  server_dir  服务器目录   /tmp
def uploadFile(ip, user, pwd, src_file, server_dir, port=22):
    # 获取目的文件名
    if server_dir.endswith("/"):
        dest_file_name = server_dir + os.path.basename(src_file)
    else:
        dest_file_name = server_dir + "/" + os.path.basename(src_file)

    try:
        ssh_conn = paramiko.Transport((ip, port))
    except Exception as e:
        print("检查服务器地址、端口是否有误!!!!")
        print(e)
        sys.exit()
    
    try:
        ssh_conn.connect(username=user, password=pwd)
    except Exception as e:
        print("检查用户名、密码是否有误!!!!!")
        print(e)
        sys.exit()

    ftp_client = paramiko.SFTPClient.from_transport(ssh_conn)
    ftp_client.put(src_file, dest_file_name)

    ftp_client.close()
    ssh_conn.close()
    return dest_file_name

# 获取指定服务器上指定文件的校验码
def remoteFileMD5(ip, user, pwd, file_name, port=22):
    ssh_client = paramiko.SSHClient()
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    try:
        ssh_client.connect(hostname=ip, username=user, password=pwd, port=port)
    except Exception as e:
        print("检查服务器地址、用户名、密码是否误!!!!!")
        print(e)
        sys.exit()
        
    stdin, stdout, stderr = ssh_client.exec_command("md5sum %s | awk '{print $1}'" % file_name)
    return stdout.read().decode(encoding="utf-8").strip()

    ssh_client.close()

if __name__ == '__main__':
    source_file = input("源文件: ").strip()
    server_dir = input("服务器目录: ").strip()

    servers = {
        "192.168.140.10": {
            "ssh_user": "root",
            "ssh_pwd": "redhat",
            "ssh_port": 22
        },
        "192.168.140.11": {
            "ssh_user": "root",
            "ssh_pwd": "redhat",
            "ssh_port": 33333
        }
    }

    # 获取源文件校验码
    source_file_md5 = fm.md5File(source_file)
    print("源文件[%s]的校验码: %s" % (source_file, source_file_md5))
    print("-----------" * 10)


    for server_ip, ssh_info in servers.items():
        # 上传文件
        dest_file_name = uploadFile(ip=server_ip, user=ssh_info.get("ssh_user"), pwd=ssh_info.get("ssh_pwd"), port=ssh_info.get("ssh_port"),
                   src_file=source_file,
                   server_dir=server_dir)
        # 获取服务器文件的校验码
        remote_file_md5 = remoteFileMD5(ip=server_ip, user=ssh_info.get("ssh_user"), pwd=ssh_info.get("ssh_pwd"), port=ssh_info.get("ssh_port"),
                      file_name=dest_file_name)
        print("服务器[%s]文件[%s]的校验码: %s" % (server_ip, dest_file_name, remote_file_md5))


        if source_file_md5 == remote_file_md5:
            print("文件[%s]上传成功!!!!!" % source_file)
        else:
            print("文件[%s]上传失败!!!!!" % source_file)

        print("-----------" * 10)

三、psutil模块 —— 实现服务器配置信息检索

1、介绍

作用:

跨平台
查看系统运行状态

安装模块

pip install psutil

2、psutil使用

import psutil

# 查看CPU相关信息

# 查看CPU数量
print(psutil.cpu_count())
print(psutil.cpu_count(logical=False))

# cpu使用率
print(psutil.cpu_times())
print(psutil.cpu_times().user)

print(psutil.cpu_percent())
print(psutil.cpu_stats())

# cpu负载
print(psutil.getloadavg())


# 查看物理内存
print(psutil.virtual_memory())
# 查看虚拟内存
print(psutil.swap_memory())

# 查看磁盘分区
print(psutil.disk_partitions())
# 查看磁盘容量使用情况
print(psutil.disk_usage(path="E:"))
# 查看磁盘IO
print(psutil.disk_io_counters())

# 查看网卡地址信息
print(psutil.net_if_addrs())
# 查看网卡IO
print(psutil.net_io_counters())
# 查看网卡状态
print(psutil.net_if_stats())
# 查看网络连接
print(psutil.net_connections())


# 查看用户
print(psutil.users())

# 查看所有进程ID
print(psutil.pids())
print(len(psutil.pids()))

# 查看进程具体信息
p1 = psutil.Process(3856)
print(p1.cpu_times)
print(p1.status())
print(p1.name())
print(p1.ppid())


# 查看系统运行时长
boot_time = psutil.boot_time()
print(boot_time)

import datetime
print(datetime.datetime.fromtimestamp(boot_time))