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
#创建的库
#创建的表
#执行的最终结果
