python3小脚本-监控服务器性能并插入mysql数据库

操作系统：

centos版本	7.4
防火墙	关闭
selinux	关闭
python版本	3.6
mysql版本	5.7

#操作系统性能脚本

[root@localhost sql]# cat cpu_mac.py 

#!/usr/bin/env python3

#coding:utf-8

import subprocess

def monitor(frist_invoke=1):

    shell_command = 'sar 1 1| grep "^Average:"'

    status,result = subprocess.getstatusoutput(shell_command)

    if status != 0:

        value_dic = {'status': status}

    else:

        value_dic = {}

        #print('---res:',result)

        user,nice,system,iowait,steal,idle = result.split()[2:]

        #拿回监控数据后保存为字典

        value_dic= {

            'user': user,

            'nice': nice,

            'system': system,

            'idle': idle,

            'status': status

        }

    return value_dic

if __name__ == '__main__':

    print(monitor())

#获取服务器性能后使用python3插入数据库脚本

[root@localhost sql]# cat /root/sql/class_sql.py

import pymysql

import cpu_mac

from datetime import datetime

db = pymysql.connect("192.168.10.100", "root", "root", "dx_monitor", charset="utf8")

#获取当前时间

NOW = datetime.now()

#获取当前时间，并格式化

TIME_DATE = NOW.strftime("%Y-%m-%d %H:%M:%S")

#获取当前时间，并格式化为年月

MONITOR_DATE = NOW.strftime("%Y_%m")

class monitor:

    def __init__(self, db, MONITOR_DATE, TIME_DATE):

        self.db = db

        self.MONITOR_DATE = MONITOR_DATE

        self.TIME_DATE = TIME_DATE

    def create_table(self):

#获取当前数据库游标

        cursor = self.db.cursor()

#查看当前数据表是否存在

        date_sql = "DROP TABLE IF EXISTS res_monitor_{0}".format(self.MONITOR_DATE)

        try:

#创建数据表

            sql = """

                CREATE TABLE  res_monitor_self.MONITOR_DATE (

                id int UNSIGNED AUTO_INCREMENT,

                user varchar(20) NOT NULL,

                nice varchar(20) NOT NULL,

                system varchar (20) NOT NULL,

                idle  varchar(20) NOT NULL,

                status varchar(20) NOT NULL,

                date DATETIME,

                PRIMARY KEY( id )

                )ENGINE=InnoDB DEFAULT CHARSET=utf8;

                """.format(self.MONITOR_DATE)

#判断数据表

            data = cursor.execute(date_sql)

#执行创建表

            cursor.execute(sql)

            self.db.close()

            #print("Database version : %s" % data)

            #print("表创建成功")

        except :

            print('表错误')

            data = cursor.execute(date_sql)

            cursor.execute(sql)

            self.db.close()

            print("Database version : %s" % data)

            print("表创建成功")

            cursor.close()

            self.db.close()

    def insert_table(self):

        de = cpu_mac.monitor()

#插入数据到数据库表空间

        sql = "INSERT INTO res_monitor_{0} (user, nice, system, idle, status, date) VALUES (%s, %s, %s, %s, %s, %s)".format(self.MONITOR_DATE)

        cursor = db.cursor()

        T = (( de['user']), (de['nice']), (de['system']), (de['idle']), (de['status']), (self.TIME_DATE))

        try:

#执行表

            cursor.execute(sql, T)

            self.db.commit()

            print('监控数据插入ok')

        #     print('ok 123')

        #     # sql = "INSERT INTO res_monitor_{0} (user, nice, system, idle, status, date) VALUES ({1}, {2}, {3}, {4}, {5})".format(MONITOR_DATE, de['user'], de['nice'], de['system'], de['idle'], de['status'])

        #

        #     print(sql)

        #

        #     # cursor.execute(sql, T)

        #     print('监控数据插入ok')

        except:

            self.db.rollback()

            print('监控数据插入错误')

        cursor.close()

        self.db.close()

if __name__ == '__main__':

    db_sql = monitor(db,MONITOR_DATE,TIME_DATE)

    if TIME_DATE_MONTH == '01':

        db_sql.create_table()

    db_sql.insert_table()

#写入定时任务

[root@localhost sql]# crontab -l

* * * * * /usr/bin/python3 /root/sql/class_sql.py

#连接数据库

[root@localhost ~]# mysql -uroot -proot -h192.168.10.100

#创建的库

 

#创建的表

#执行的最终结果