增删改查,最注重要是删和改。
1.主机表,应用表,主机应用关系表
主机表
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ip地址',
`env` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '环境',
`system` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '系统类型',
`application` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '应用',
`scu` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '具体用途',
PRIMARY KEY (`id`),
UNIQUE KEY `ip` (`ip`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='最先存入的所有主机表';
主机应用关系表
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ipid` int(11) DEFAULT NULL,
`applyid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=916 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
应用表
DROP TABLE IF EXISTS `pub_datadictionary`;
CREATE TABLE `pub_datadictionary` (
`dd_no` varchar(38) COLLATE utf8mb4_unicode_ci NOT NULL,
`dd_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '字典项代码',
`dd_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '字典项名称',
`dd_desc` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '描述',
`is_lock` char(1) COLLATE utf8mb4_unicode_ci DEFAULT 'N' COMMENT '是否锁定:Y-锁定,N未锁定',
PRIMARY KEY (`dd_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `pub_dditem`;
CREATE TABLE `pub_dditem` (
`item_no` varchar(38) COLLATE utf8mb4_unicode_ci NOT NULL,
`item_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '常量值',
`item_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '常量名称',
`item_order` int(11) DEFAULT '999' COMMENT '排序值',
`dd_no` varchar(38) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '所属数据字典项',
`is_lock` char(1) COLLATE utf8mb4_unicode_ci DEFAULT 'N' COMMENT '是否锁定:Y-锁定,N未锁定',
`is_sync` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT 'N' COMMENT '是否同步',
`parentno` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`itemdesc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`item_no`),
KEY `IDX_160_ITEMCODE` (`item_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
查询的sql
SELECT
d.item_name
FROM
(
SELECT
b.applyid
FROM
b
JOIN a ON b.ipid = a.id
WHERE
a.ip = '172.x.x.x'
) AS c
JOIN pub_dditem d ON c.applyid = d.item_no;
a
1 172.1.1.1 test Cloud mysql 数据库
2 172.1.1.2 test Cloud tomcat a+b+c,jenkins
b
空
pub_dditem
148 mysql mysql 46 100 N N 0
149 tomcat a 47 101 N N 0
150 tomcat a 48 101 N N 0
151 tomcat a 49 101 N N 0
update_hosts_apply.xls
172.1.1.1 mysql
172.1.1.2 tomcat(crm+workflow+service)
python小脚本初始化(仅仅是插入)
# !/usr/bin/env python
# coding:utf-8
import json
import urllib2
from urllib2 import URLError
import sys
import xlrd
import MySQLdb as mdb
def conn(ip,user,password,database):
con = mdb.connect(ip,user,password,database)
cur = con.cursor()
return cur
def select(cur,sql):
cur.execute(sql)
return cur.fetchall()
def insert(cur,xls):
workbook = xlrd.open_workbook(xls)
for row in xrange(workbook.sheets()[0].nrows):
ip = workbook.sheets()[0].cell(row, 0).value
applys = workbook.sheets()[0].cell(row, 1).value
for apply in applys.split(','):
if 'tomcat' in apply:
tomcat_apply = apply.split('(')[1].split(')')[0].split('+')
for ztomcat_apply in tomcat_apply:
sql = "select item_no from pub_dditem where item_code='tomcat' and item_name='%s' " % (
ztomcat_apply)
sql1 = "select id from a where ip='%s'" % (ip)
for r in select(cur, sql):
item_no = r[0]
for r in select(cur, sql1):
id = r[0]
sql2 = "insert into b (ipid,applyid) values(%d,%d)" % (int(id), int(item_no))
sql3 = "select * from b where ipid = '%s' and applyid = '%s'" % (id, item_no)
flag = cur.execute(sql3)
if flag == 0L:
cur.execute(sql2)
con.commit()
else:
sql = "select item_no from pub_dditem where item_name='%s' " % (apply)
sql1 = "select id from a where ip='%s'" % (ip)
for r in select(cur, sql):
item_no = r[0]
for r in select(cur, sql1):
id = r[0]
sql2 = "insert into b (ipid,applyid) values(%d,%d)" % (int(id), int(item_no))
sql3 = "select * from b where ipid = '%s' and applyid = '%s'" % (id, item_no)
flag = cur.execute(sql3)
if flag == 0L:
cur.execute(sql2)
con.commit()
if __name__ == "__main__":
con = mdb.connect('db', 'root', 'password', 'cmdb')
cur = con.cursor()
insert(cur,'update_hosts_apply.xls')
cur.close()
还有的问题:
1.初始化数据一定要准确,不然执行到b表会重复
2.录入的xls也要准确,比如出现写错应用的,那么b表也会重复
本文转自 liqius 51CTO博客,原文链接:http://blog.51cto.com/szgb17/2074648,如需转载请自行联系原作者