# -*- coding: UTF-8 -*-
import os
import pymysql
import xlwings as xw
#任务名称
job_name = "test1"
root_dir = os.path.abspath('.')
write_excel_path = root_dir + "\\结果.csv"
write_excel_book = xw.Book()
sht = write_excel_book.sheets('sheet1')
#打开数据库连接
db = pymysql.connect("172.0.0.1","root","root","database")
#使用cursor()方法创建一个游标对象 cursor
cursor = db.cursor()
sql_data = "SELECT res.vendor,ct.type_name,res.series,res.model,res.major_version,res.minor_version,data.policy_name,data.device_id FROM config res LEFT JOIN config_type ct ON res.res_type_id = ct.type_id LEFT JOIN data data ON res.resource_config_id = data.device_id WHERE data.job_name = " + '\''+ str(job_name)+ '\''+ "GROUP BY res.resource_config_id;"
#使用execute()方法执行SQL查询
try:
cursor.execute(sql_data)
results = cursor.fetchall()
#数据
data = []
#规则名称
policy_data = []
#设备ID
device_data = []
for result in results:
data.append(result)
policy_data.append(results[6])
device_data.append(result[7])
#巡检项规则数据
for i in range(len(device_data)):
sql_policy = "select exp.rule_name from exception_rule exp where exp.job_name ="+'\''+ str(job_name)+ '\''+ "and exp.device_id =" '\''+ str(device_data[i])+ '\''
cursor.execute(sql_policy)
results = cursor.fetchall()
rule_data_results = []
for result in results:
rule_data_results.append(str(result[0]))
rule_data.append(list(data[i])+rule_data_results)
#写到excel里
for each_col in range(len(rule_data)):
col = 'A' + str(each_col +1)
sht.range(col).value = rule_data[each_col]
write_excel_boo.save(write_excel_path)
except Exception as e:
db.close()