VMware-数据库批量导入

• VMware数据库批量导入

# -*- coding:utf-8 -*-

import os
import datetime
#from datetime import datetime
from configparser import ConfigParser 
import atexit
from pyVmomi import vim, vmodl
from pyVim.connect import SmartConnectNoSSL, Disconnect
import xlwt
import pymysql


def getStorage(host):
    memoryCount = round((host.hardware.memorySize / 1024 ** 3), 2)
    memoryUsage = round((host.summary.quickStats.overallMemoryUsage / 1024),
                        2) if host.summary.quickStats.overallMemoryUsage else 0
    return memoryCount, memoryUsage


def hostSummary(dc, vc_ip, cluster, host):
    host_list = []
    memoryCount, memoryUsage = getStorage(host)
    # host_list.append(dc)
    host_list.append(vc_ip)
    # host_list.append('1')
    host_list.append(cluster)
    host_list.append(host.name)
    brand = host.hardware.systemInfo.vendor + ' ' + host.hardware.systemInfo.model
    # host_list.append(host.hardware.systemInfo.vendor) # 品牌
    # host_list.append(host.hardware.systemInfo.model) # 型号
    host_list.append(brand)
    product = host.config.product.fullName if host.config else 'None'
    host_list.append(product)
    host_list.append(host.summary.overallStatus)
    host_list.append(str(host.summary.hardware.numCpuThreads))
    # host_list.append(host.summary.hardware.cpuModel)
    host_list.append(str(memoryCount))
    host_list.append(str(memoryUsage))
    # host_list.append(memoryPercent)
    for summary in host.summary.hardware.otherIdentifyingInfo:
        if summary.identifierType.key == 'ServiceTag':
            host_list.append(summary.identifierValue)
    # print(host_list)
    return host_list


def run(si, vc_address):
    try:
        content = si.RetrieveContent()
        atexit.register(Disconnect, si)
        view = si.content.viewManager.CreateContainerView(si.content.rootFolder, [vim.HostSystem], True)
        hosts = view.view
        view.Destroy()
        # 数据库链接
        db = pymysql.connect(host='10.250.100.150', port=3306, database='db_vm', user='django', passwd='django', charset='utf8')
        cursor = db.cursor()

        for host in hosts:
            dc_name = host.parent.parent.parent.name
            cluster = host.parent.name
            host_info = hostSummary(dc_name, vc_address, cluster, host)
            print(host_info)
            sql = 'insert into HostInfo(dc_ip_id, cluster_name, host_ip, host_brand, host_build_version, ' \
                  'host_status, host_cpus, host_memory, host_memory_use, host_serial_number) values' \
                  ' (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
            try:
                cursor.execute(sql, host_info)
                db.commit()
                print("执行插入数据")
                # db.close()
            except Exception as e:
                print(e)

        db.close()
    except vmodl.MethodFault as error:
        return False, error.msg


def getNics(guest):
    ips = []
    for nic in guest.net:
        if nic.ipConfig is not None and nic.ipConfig.ipAddress is not None:
            ipconf = nic.ipConfig.ipAddress
            for ip in ipconf:
                ips.append(ip.ipAddress)
    IPS = ','.join(ips)
    print(IPS)
    # print(ips)
    return IPS


def getStorage_vm(storage):
    storage_userd = round((storage.committed / 1024 ** 3), 2)
    storage_left = round((storage.uncommitted / 1024 ** 3), 2)
    storage_total = int(storage_left) + int(storage_userd)
    return storage_userd, storage_total


def vmsummary(summary, guest):
    vm_list = []
    storage_userd, storage_total = getStorage_vm(summary.storage)
    ips = getNics(guest)
    # vm_list.append(dc)
    # vm_list.append(vc_ip)
    # vm_list.append(cluster)
    # vm_list.append(host.name) # 
    vm_list.append(summary.runtime.host.name) # 主机
    vm_list.append(summary.config.uuid)  # uuid
    vm_list.append(summary.config.name) # 名称
    vm_list.append(summary.guest.ipAddress) # IP
    vm_list.append(summary.runtime.powerState) # 电源状态
    vm_list.append(summary.guest.guestFullName) # 系统
    vm_list.append(str(summary.config.memorySizeMB)) # 内存
    vm_list.append(str(summary.config.numCpu)) # cpu
    vm_list.append(str(storage_total)) # 制备
    vm_list.append(storage_userd) # 已用
    # vm_list.append(summary.config.vmPathName) 
    vm_list.append(summary.config.annotation) # 备注
    vm_list.append(ips) # IPS
    return vm_list


def run_vm(si, vc_address, config:dict): 
    global vm_lists
    try:
        time_start = si.CurrentTime()
        view = si.content.viewManager.CreateContainerView(si.content.rootFolder, [vim.VirtualMachine], True)
        vms = view.view
        # 数据库连接
        db = pymysql.connect(host='10.250.100.150', port=3306, database='db_vm', user='django', passwd='django', charset='utf8')
        cursor = db.cursor()

        for vm in vms:
            vm_list = vmsummary(vm.summary, vm.guest)
            print(vm_list)
            sql = 'insert into VmInfo(host_ip_id, vm_uuid, vm_name, vm_ip, vm_power_status, vm_os, vm_memory, vm_cpu_num, vm_storage, \
            vm_storage_usage, vm_note, vm_ips) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
            try:
                cursor.execute(sql, vm_list)
                db.commit()
                print("执行插入数据")
                # db.close()
            except Exception as e:
                print(e)

        db.close()
        time_end = si.CurrentTime()
        #print("结束时间：", time_end.strftime("%Y-%m-%d:%H:%M"))
        time_spend = time_end - time_start
        print("耗时：", time_spend)
    except vmodl.MethodFault as error:
        print("Caught vmodl fault:" + error.msg)
        return False, error.msg
    else: 
        atexit.register(Disconnect, si)
        return True, "Ok"


    
if __name__ == "__main__":
    try:
        base_dir = os.path.dirname(os.path.abspath(__file__))
        config_file = os.path.join(base_dir, os.path.join("etc", "vSphere.ini"))
        config = ConfigParser()
        config.read(config_file, encoding="utf-8")
        for section in config.sections():
            cfg = dict(config.items(section))
            try:
                si = SmartConnectNoSSL(host=section, port=cfg["port"], user=cfg["user"], pwd=cfg["passwd"])
            except Exception as e:
                print("Login Error:" + cfg["info"])
                continue
            else:
                print("Login Success:" + cfg["info"])
                # run(si, section)
                run_vm(si, section, cfg)
    except Exception as err:
        print(err)
    finally:
        exit()