利用python 将 mysql 数据进行抽取并清理成标准格式后 存入MSSql 数据中
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了利用python 将 mysql 数据进行抽取并清理成标准格式后 存入MSSql 数据中,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5698字,纯文字阅读大概需要9分钟。
内容图文
from pymongo import MongoClientfrom pymysql import connect
import pymssql
from datetime import datetime, timedelta
import time
Nagios 数据库的IP 地址
NagiosDB_IP = ["10.1.1.44", "10.1.1.37"]
用于数据展示的BI 中间数据库地址及账号信息
BIDBServer=‘10.1.1.65‘
BI_NagiosDB=‘NagiosInfo‘
NagiosDB=‘nagios‘
BIDB_User=‘sa‘
BIDBPassword=‘123456!a‘
NagiosDB_User=‘report‘
NagiosDBPassword=‘report!a‘
链接中间数据库,该库用于存放Nagios 性能数据
Mssqlconn=pymssql.connect(host=BIDBServer,database=BI_NagiosDB,user=BIDB_User,password=BIDBPassword)
查询交换机端口状态信息
Querys_SwitchPortStatus = """SELECT NSS.service_object_id,NSS.status_update_time,NSS.output,NS.display_name,NH.display_name, NH.address ,NS.host_object_id, NSS.check_command from nagios_servicestatus NSS
LEFT join
nagios_services NS on NSS.service_object_id = NS.service_object_id
LEFT join
nagios_hosts NH on NS.host_object_id = NH.host_object_id
where (check_command LIKE ‘check_snmp_free_interfaces%‘ or check_command LIKE ‘check_snmp_usage_interfaces%‘)
ORDER BY
NH.display_name"""
查询交换机基础信息
Querys_SwitchInfo = """SELECT NSS.service_object_id,NSS.status_update_time,NSS.output,NS.display_name,NH.display_name, NH.address ,NS.host_object_id, NSS.check_command from nagios_servicestatus NSS
LEFT join
nagios_services NS on NSS.service_object_id = NS.service_object_id
LEFT join
nagios_hosts NH on NS.host_object_id = NH.host_object_id
where (check_command LIKE ‘snmp_cisco_info%‘ AND NS.display_name LIKE ‘1.1.2-Network Access Switch Info%‘)
ORDER BY
NH.display_name
"""
查询主机状态信息
Querys_HostStatus = """select host_object_id,status_update_time,output from nagios_hoststatus WHERE check_command LIKE ‘check-host-alive%‘"""
Mssqlconn=pymssql.connect(host=‘.‘,database=‘NagiosInfo‘,user=‘sa‘,password=‘123456!a‘)
Mssqlcur=Mssqlconn.cursor()
def Port_Status_DataClean():
start=time.strftime("%Y%m%d",(datetime.now()-timedelta(1)).timetuple())
# end=time.strftime("%Y%m%d",datetime.now().timetuple())
# t1=datetime.now()
Port_Status= ‘Unknown‘
for i in range(len(NagiosDB_IP)):
conn=connect(NagiosDB_IP[i], NagiosDB_User, NagiosDBPassword, NagiosDB, charset=‘utf8‘)
cursor=conn.cursor()
cursor.execute(Querys_SwitchPortStatus)
for row in cursor:
# print(row)
record= {}
record[‘Object_ID‘]=row[0]
record[‘Record_Time‘]=row[1]
record[‘Service_Name‘]=row[3]
record[‘Host_Name‘]=row[4]
record[‘IP_Addr‘]=row[5]
record[‘Host_ID‘]=row[6]
#record[‘Check_Command‘]=row[7]
if row[7].split(‘!‘)[0]== ‘check_snmp_free_interfaces‘:
Port_Status=‘Free‘
elif row[7].split(‘!‘)[0]==‘check_snmp_usage_interfaces‘:
Port_Status=‘Usage‘
try:
for Port in row[2].split(‘(0‘)[1].strip(‘)‘).split():
record["Port_Status"]= Port_Status
record[‘Port‘]=Port
Mssqlcur.execute("insert into ASwitchPortStatus values (‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)"%(record[‘Object_ID‘],record[‘Record_Time‘],record[‘Service_Name‘],record[‘Host_Name‘],record[‘IP_Addr‘],record[‘Host_ID‘],record["Port_Status"],record[‘Port‘]) )
except:
# print(row[2])
continue
Mssqlcur.connection.commit()
conn.close()
def SwitchInfo_DataClean():
start=time.strftime("%Y%m%d",(datetime.now()-timedelta(1)).timetuple())
# end=time.strftime("%Y%m%d",datetime.now().timetuple())
# t1=datetime.now()
for i in range(len(NagiosDB_IP)):
conn = connect(NagiosDB_IP[i], "client", "client!a", "nagios", charset=‘utf8‘)
cursor = conn.cursor()
cursor.execute(Querys_SwitchInfo)
for row in cursor:
record = {}
record[‘Object_ID‘] = row[0]
record[‘Record_Time‘] = row[1]
record[‘Service_Name‘] = row[3]
record[‘Host_Name‘] = row[4]
record[‘IP_Addr‘] = row[5]
record[‘Host_ID‘] = row[6]
try:
record[‘Host_Info_Name‘]=row[2].split(‘,<br> ‘)[0]
record[‘Host_Info_Model‘] = row[2].split(‘,<br> ‘)[1]
record[‘Host_Info_Version‘]=row[2].split(‘,<br> ‘)[2]
record[‘Host_Info_SN‘] = row[2].split(‘,<br> ‘)[3]
record[‘Host_Info_location‘] = row[2].split(‘,<br> ‘)[4]
record[‘Host_Info_Contact‘] = row[2].split(‘,<br> ‘)[5]
Mssqlcur.execute("insert into ASwitchInfo values(‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)"%(record[‘Object_ID‘], record[‘Record_Time‘], record[‘Service_Name‘], record[‘Host_Name‘],record[‘IP_Addr‘], record[‘Host_ID‘], record[‘Host_Info_Name‘], record[‘Host_Info_Model‘],record[‘Host_Info_Version‘], record[‘Host_Info_SN‘], record[‘Host_Info_location‘],record[‘Host_Info_Contact‘]))
except:
continue
Mssqlcur.connection.commit()
conn.close()
def HostStatus_DataClean():
start=time.strftime("%Y%m%d",(datetime.now()-timedelta(1)).timetuple())
# end=time.strftime("%Y%m%d",datetime.now().timetuple())
# t1=datetime.now()
for i in range(len(NagiosDB_IP)):
conn = connect(NagiosDB_IP[i], "client", "client!a", "nagios", charset=‘utf8‘)
cursor = conn.cursor()
cursor.execute(Querys_HostStatus)
for row in cursor:
record = {}
record[‘Host_ID‘] = row[0]
record[‘Record_Time‘] = row[1]
try:
if row[2].split(‘ - ‘)[0]== ‘OK‘:
record[‘Host_Status‘]=‘UP‘
elif row[2].split(‘ - ‘)[0]== ‘CRITICAL‘:
record[‘Host_Status‘] = ‘Down‘
else:
record[‘Host_Status‘] = ‘#N/A‘
except:
continue
Mssqlcur.execute("insert into HostStatus values(‘%s‘,‘%s‘,‘%s‘)" %(record[‘Host_ID‘], record[‘Record_Time‘], record[‘Host_Status‘]))
print(record)
Mssqlcur.connection.commit()
conn.close()
if name=="main":
Port_Status_DataClean()
SwitchInfo_DataClean()
HostStatus_DataClean()
利用python 将 mysql 数据进行抽取并清理成标准格式后 存入MSSql 数据中
标签:print model host mysql row user exe upd ike
本文系统来源:http://blog.51cto.com/unicom/2317961
内容总结
以上是互联网集市为您收集整理的利用python 将 mysql 数据进行抽取并清理成标准格式后 存入MSSql 数据中全部内容,希望文章能够帮你解决利用python 将 mysql 数据进行抽取并清理成标准格式后 存入MSSql 数据中所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。