python清洗数据入库_利用python 将 mysql 数据进行抽取并清理成标准格式后存入MSSql 数据中...-CSDN博客

from pymongo import MongoClient

from 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(',
')[0]

record['Host_Info_Model'] = row[2].split(',
')[1]

record['Host_Info_Version']=row[2].split(',
')[2]

record['Host_Info_SN'] = row[2].split(',
')[3]

record['Host_Info_location'] = row[2].split(',
')[4]

record['Host_Info_Contact'] = row[2].split(',
')[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)