ansible获取主机信息并存入mysql数据库

#!/usr/bin/python
# -*- coding:UTF-8 -*-


import os
import sys
import commands
import MySQLdb
import json

#获取全部的fact并以主机名为文件名存到某个路径下
(status, output) = commands.getstatusoutput('ansible all -m setup --tree /root/hosts')
#print status,'++++++++++++++'

#为/root/hosts下文件加入.json后缀
def rename_file(path):

for file in os.listdir(path):

if os.path.isfile(os.path.join(path,file)) == True:

if file.find('.json') < 0:

newname = file + '.json'

os.rename(os.path.join(path,file),os.path.join(path,newname))

print file,'ok'

else:

continue

else:

continue

print rename_file('/root/hosts')

#获取主机facts并存入数据库
if ~status:
#获取文件列表

(status,output) = commands.getstatusoutput('ls /root/hosts > /root/host_list')

# print status,'-------------'

if ~status:

# 打开数据库连接
db = MySQLdb.connect("localhost","ansible","R00t@123","ansible" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 创建hosts数据表
flag = cursor.execute(" show tables like 'hosts' ")
# print flag,'!!!!!!!!!!!!!!!!!'
if flag == 0:

create_hosts_table = ''' CREATE TABLE hosts (
        IP  CHAR(20) PRIMARY KEY NOT NULL,
        hostname  CHAR(50),
        gateway CHAR(20),
        mac CHAR(30),
        distribution CHAR(20),
        distribution_version CHAR(20),
        architecture CHAR(20),
        kernel CHAR(30),
        processor CHAR(100),
        processor_cores CHAR(20),
        processor_count CHAR(20))'''
cursor.execute(create_hosts_table)

file = open("/root/host_list","r")

if file:

for line in file:

fact = line.strip('\n')

print fact

#获取每个主机的facts

file_fact = open("/root/hosts/"+fact,"r")

if file_fact:

data = json.loads(file_fact.read())

hostname = data['ansible_facts']['ansible_hostname']

IP = data['ansible_facts']['ansible_default_ipv4']['address']

gateway = data['ansible_facts']['ansible_default_ipv4']['gateway']

mac = data['ansible_facts']['ansible_default_ipv4']['macaddress']

distribution = data['ansible_facts']['ansible_distribution']

distribution_version = data['ansible_facts']['ansible_distribution_version']

architecture = data['ansible_facts']['ansible_architecture']

kernel = data['ansible_facts']['ansible_kernel']

processor = data['ansible_facts']['ansible_processor'][1]

processor_cores = data['ansible_facts']['ansible_processor_cores']

processor_count = data['ansible_facts']['ansible_processor_count']

print hostname,architecture,distribution,distribution_version,kernel,IP,gateway,mac,processor

#检查主机数据是否存在，

check_host = "select * from hosts where IP = '%s' " % (IP)

flag = cursor.execute(check_host)

# print (flag,type(flag))

#如果主机已经存在，更新主机信息

if flag != 0L:

sql = "update hosts set IP = '%s',hostname = '%s',gateway = '%s',mac = '%s',distribution = '%s',distribution_version = '%s',architecture = '%s',kernel = '%s',processor = '%s',processor_cores = '%s',processor_count = '%s' where IP = '%s' " % (IP,hostname,gateway,mac,distribution,distribution_version,architecture,kernel,processor,processor_cores,processor_count,IP)

#如果主机不存在，插入主机信息

else:

sql = "insert into hosts (IP,hostname,gateway,mac,distribution,distribution_version,architecture,kernel,processor,processor_cores,processor_count) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') " % (IP,hostname,gateway,mac,distribution,distribution_version,architecture,kernel,processor,processor_cores,processor_count)

try:

  # 执行sql语句

#   print (sql,'******************')

  cursor.execute(sql)

  # 提交到数据库执行

  db.commit()

  print (" '%s' update successfully !") % (hostname)

except:

  # 发生错误时回滚

  db.rollback()

  print 'Something is wrong !!! '

file_fact.close #关闭文件"/root/hosts/"+fact

db.close()