def modify_clicked(self):
# 数据已经合法性判断过。
name = self.user_lineEdit.text()
dept = self.dept_lineEdit.text()
ip = self.ip_lineEdit.text()
mac = self.mac_lineEdit.text()
room = self.room_lineEdit.text()
switch = self.switch_lineEdit.text()
port = self.port_lineEdit.text()
if dept == '' or ip == '' or mac == '' or room == '' or switch == '' or port == '':
print('信息不完整')
reply = QMessageBox.about(self, '提示', '请完善员工信息')
else:
rem = Re_manager()
dept = rem.is_dept(dept)
ip = rem.is_ip(ip)
mac = rem.is_mac(mac)
room = rem.is_room(room)
switch = rem.is_switch(switch)
port = rem.is_port(port)
if dept == None or ip == None or mac == None or room == None \
or switch == None or port == None:
print('格式不符合')
reply = QMessageBox.about(self, '提示', '请检查格式是否符合要求')
else:
mm = Mysql_manager()
with mm:
sql = 'update em_info set em_dept = %s,em_ip = %s,em_mac = %s,em_room = %s,em_switch = %s, \
em_port = %s where em_name = %s'
mm.exe_db(sql, (dept, ip, mac, room, switch, port, name))
print('信息更新成功')
reply = QMessageBox.about(self, '提示', f'{name}的信息更新成功')
self.close()
def register_clicked(self):
print('准备注册')
# 先作空文本判断
user = self.user_lineEdit.text()
passwd = self.passwd_lineEdit.text()
confirm = self.confirm_lineEdit.text()
if user == '' or passwd == '' or confirm == '':
print('账号密码不能为空')
reply = QMessageBox.about(self, '注意', '账号密码不能为空')
elif passwd != confirm:
print('密码重复输入不一致')
reply = QMessageBox.about(self, '注意', '密码重复输入不一致')
else:
rem = Re_manager()
user = rem.is_user(user)
passwd = rem.is_passwd(passwd)
if user == None or passwd == None:
print('账号或密码不符合要求')
reply = QMessageBox.about(self, '注意', '账号或密码不符合要求')
else:
# 所有基本条件符合，将注册信息写入注册表
mm = Mysql_manager()
with mm:
sql = 'insert into user_info values(%s,%s)'
mm.exe_db(sql, (user, passwd))
print('账号注册成功')
reply = QMessageBox.about(self, '注意', '账号注册成功')
self.user_lineEdit.setText('')
self.passwd_lineEdit.setText('')
self.confirm_lineEdit.setText('')
def del_clicked(self):
# 已经从鼠标选中获取相应数值，不用做合法性判断
name = self.user_lineEdit.text()
mm = Mysql_manager()
with mm:
sql = 'delete from em_info where em_name = %s'
mm.exe_db(sql, name)
print('信息删除成功')
reply = QMessageBox.about(self, '提示', f'{name}的个人信息删除成功')
# 删除成功，直接调用取消退出
self.cancel_clicked()
def goto_add_confirm(self): # 防止重名，中间加上add标签
# 首先从lineEdit获取文本内容
name = self.user_lineEdit.text()
dept = self.dept_lineEdit.text()
ip = self.ip_lineEdit.text()
mac = self.mac_lineEdit.text()
params = (name, dept, ip, mac)
# 建立数据库连接
mm = Mysql_manager()
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s)'
mm.exe_db(sql, params)
print(mm.cur.rowcount) # 判断是否添加成功
def goto_register_register(self):
name = self.account_lineEdit.text()
passwd = self.passwd_lineEdit.text()
confirm = self.confirm_lineEdit.text()
print('*')
print(name, passwd)
if name == '' or passwd == '' or confirm == '':
reply = QtWidgets.QMessageBox.about(self, '提示', '输入内容不完整')
elif passwd != confirm:
reply = QtWidgets.QMessageBox.about(self, '提示', '密码输入不一致')
else:
# 写入数据库
mm = Mysql_manager()
with mm:
sql = 'insert into user_info values(%s,%s,%s)'
mm.exe_db(sql, (name, passwd, 1))
def goto_query_confirm(self):
name = self.user_lineEdit.text()
ip = self.ip_lineEdit.text()
# 先获取到值，然后通过自定义信号发送到父窗口
if name == '' and ip == '':
print('内容为空')
reply = QtWidgets.QMessageBox.about(self, '提示', '请输入姓名或者IP进行查询')
elif name == '':
mm = Mysql_manager()
with mm:
sql = 'select * from em_info where em_ip = %s'
result = mm.exe_db(sql, ip)
self.my_signal.emit(result) # 发射自定义信号
elif ip == '':
mm = Mysql_manager()
with mm:
sql = 'select * from em_info where em_name = %s'
result = mm.exe_db(sql, name)
self.my_signal.emit(result)
def goto_del_confirm(self):
name = self.user_lineEdit.text()
ip = self.ip_lineEdit.text()
if name == '' and ip == '':
reply = QtWidgets.QMessageBox.about(self, '提示', '请输入用户名或者IP地址查询')
elif name == '': # 此时通过IP查询来删除
mm = Mysql_manager()
with mm:
sql = 'delete from em_info where em_ip = %s'
result = mm.exe_db(sql, ip)
# 要么没有当前IP，要么直接被删除
if mm.cur.rowcount == 0: # 注意有嵌套判断语句
reply = QtWidgets.QMessageBox.about(
self, '提示', '没有当前IP地址信息')
print('不进行操作。没有该IP')
else:
# 无需在view输出
reply = QtWidgets.QMessageBox.about(
self, '提示', f'{ip}的相关信息删除成功')
elif ip == '':
mm = Mysql_manager()
with mm:
sql = 'delete from em_info where em_name = %s'
result = mm.exe_db(sql, name)
if mm.cur.rowcount == 0:
reply = QtWidgets.QMessageBox.about(self, '提示', '没有当前员工信息')
else:
reply = QtWidgets.QMessageBox.about(
self, '提示', f'{name}的信息删除成功')
else: # name和ip都有输入
mm = Mysql_manager()
with mm:
sql = 'delete from em_info where em_name = %s and em_ip = %s'
result = mm.exe_db(sql, (name, ip))
if mm.cur.rowcount == 0:
reply = QtWidgets.QMessageBox.about(self, '提示', '没有找到相关信息')
else:
reply = QtWidgets.QMessageBox.about(
self, '提示', f'姓名：{name},IP：{ip}的相关信息被删除')
def login_clicked(self):
user = self.user_lineEdit.text()
passwd = self.passwd_lineEdit.text()
if user == '' or passwd == '':
print('账号密码不能为空')
reply = QMessageBox.about(self, '注意', '账号密码不能为空')
else:
# 应该不需要判断格式，直接通过数据库查询
mm = Mysql_manager()
with mm:
sql = 'select * from user_info where user_name = %s and user_passwd = %s'
mm.exe_db(sql, (user, passwd))
if mm.cur.rowcount == 0:
print('账号密码错误')
reply = QMessageBox.about(self, '注意', '账号密码错误')
# 账号密码错误的情况下也需要重置文本框
else:
print('登录成功')
self.em = Em_manager()
self.close()
self.em.show()
def import_excel(self,path):
# path 通过其他方法获取并传递进来
# 从表格导入数据到数据库
# 先从打开表格,获取当前子表
self.wb = load_workbook(path)
self.ws = self.wb.active
# 使用for循环读出相应数据
# 首先获取表行数
row_num = self.ws.max_row #都是属性，不是方法 使用了修饰器的方法
mm = Mysql_manager()
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s,%s,%s,%s)'
for i in range(2,row_num+1): # 表格是从1开始计数，去掉抬头
name = self.ws.cell(row = i,column = 1).value
dept = self.ws.cell(row = i,column = 2).value
ip = self.ws.cell(row = i,column = 3).value
mac = self.ws.cell(row = i,column = 4).value
room = self.ws.cell(row = i,column = 5).value
switch = self.ws.cell(row = i,column = 6).value
port = self.ws.cell(row = i,column = 7).value
mm.exe_db(sql,(name,dept,ip,mac,room,switch,port))
print('数据导入成功')
def add_clicked(self):
# 首先获取文本框的值，进行基本的合法性判断
name = self.user_lineEdit.text()
dept = self.dept_lineEdit.text()
ip = self.ip_lineEdit.text()
mac = self.mac_lineEdit.text()
room = self.room_lineEdit.text()
switch = self.switch_lineEdit.text()
port = self.port_lineEdit.text()
if name == '' or dept == '' or ip == '' or mac == '' \
or room == '' or switch == '' or port == '':
print('请完善员工信息')
reply = QMessageBox.about(self, '提示', '请先完善个人信息')
else:
rem = Re_manager()
name = rem.is_name(name)
dept = rem.is_dept(dept)
ip = rem.is_ip(ip)
mac = rem.is_mac(mac)
room = rem.is_room(room)
switch = rem.is_switch(switch)
port = rem.is_port(port)
if name == None or dept == None or ip == None or mac == None \
or room == None or switch == None or port == None:
print('请检查输入格式')
reply = QMessageBox.about(self, '提示', '请检查输入格式')
else:
# 符合要求，开始写入数据到数据库
mm = Mysql_manager()
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s,%s,%s,%s)'
mm.exe_db(sql, (name, dept, ip, mac, room, switch, port))
print('更新成功')
reply = QMessageBox.about(self, '提示', '员工信息更新成功')
# 无论是格式失败还是添加成功，清空员工信息框,调用重置函数即可
self.reset_clicked()
def goto_login_login(self): # 跳转到登录类的登录按钮
# 首先要进行数值判断
name = self.account_lineEdit.text()
passwd = self.passwd_lineEdit.text()
mm = Mysql_manager()
with mm:
sql = 'select * from user_info where user_name = %s and user_passwd = %s'
result = mm.exe_db(sql, (name, passwd))
print('----')
print(result)
if len(result) == 0:
reply = QtWidgets.QMessageBox.about(self, '提示', '帐号或者密码错误')
else:
self.em = Em_manager() # 这里必须用self，不然函数运行完，自动清除
self.em.show()
login.close() # 输入错误的情况下不关闭登录框
def query_clicked(self): # 显示当个查询结果
text = self.query_lineEdit.text()
if text == '':
print('请输入查询内容1')
reply = QMessageBox.about(self, '提示', '请输入内容再进行查询')
else:
rem = Re_manager()
name = rem.is_name(text)
ip = rem.is_ip(text)
if name == None and ip == None:
print('请输入正确的姓名或者IP地址')
reply = QMessageBox.about(self, '提示', '请输入正确的姓名或者IP地址')
elif ip == None:
# 通过姓名查询
mm = Mysql_manager()
with mm:
sql = 'select * from em_info where em_name = %s'
result = mm.exe_db(sql, name)
if mm.cur.rowcount == 0:
print('没有任何内容')
reply = QMessageBox.about(self, '提示', '没有查询到任何内容')
self.query_lineEdit.setText('') # 查不到内容时，文本框清空
else:
# 重新建立数据模型与视图绑定
# 注意不要与类变量冲突，好像冲突也没事。每次调用又会覆盖
self.model = QStandardItemModel(mm.cur.rowcount, 7)
# 数据模型抬头
title = [
'姓名', '部门', 'IP地址', 'MAC地址', '房间号', '交换机地址',
'交换机端口'
]
self.model.setHorizontalHeaderLabels(title)
# 无论查询结果是几个，返回都是二维元组
num = 0
for i in result:
item0 = QtGui.QStandardItem(i[0])
item1 = QtGui.QStandardItem(i[1])
item2 = QtGui.QStandardItem(i[2])
item3 = QtGui.QStandardItem(i[3])
item4 = QtGui.QStandardItem(str(i[4]))
item5 = QtGui.QStandardItem(i[5])
item6 = QtGui.QStandardItem(str(i[6]))
self.model.setItem(num, 0, item0)
self.model.setItem(num, 1, item1)
self.model.setItem(num, 2, item2)
self.model.setItem(num, 3, item3)
self.model.setItem(num, 4, item4)
self.model.setItem(num, 5, item5)
self.model.setItem(num, 6, item6)
num += 1
self.tableView.setModel(self.model)
else:
# 通过IP地址查询
mm = Mysql_manager()
with mm:
sql = 'select * from em_info where em_ip = %s'
result = mm.exe_db(sql, ip)
if mm.cur.rowcount == 0:
print('无法查询到内容')
reply = QMessageBox.about(self, '提示', '无法查询到任何IP内容')
self.query_lineEdit.setText('')
else:
self.model = QStandardItemModel(mm.cur.rowcount, 7)
# 数据模型抬头
title = [
'姓名', '部门', 'IP地址', 'MAC地址', '房间号', '交换机地址',
'交换机端口'
]
self.model.setHorizontalHeaderLabels(title)
num = 0
for i in result:
item0 = QtGui.QStandardItem(i[0])
item1 = QtGui.QStandardItem(i[1])
item2 = QtGui.QStandardItem(i[2])
item3 = QtGui.QStandardItem(i[3])
item4 = QtGui.QStandardItem(i[4])
item5 = QtGui.QStandardItem(i[5])
item6 = QtGui.QStandardItem(i[6])
self.model.setItem(num, 0, item0)
self.model.setItem(num, 1, item1)
self.model.setItem(num, 2, item2)
self.model.setItem(num, 3, item3)
self.model.setItem(num, 4, item4)
self.model.setItem(num, 5, item5)
self.model.setItem(num, 6, item6)
num += 1
self.tableView.setModel(self.model)
def get_sw_info(self):
# 先删除核心交换机
if '172.31.64.1' in self.sw_list:
self.sw_list.remove('172.31.64.1')
flag = 0 # 最多循环三次 列表为空，自动跳出
while flag < 3:
for ip in self.sw_list:
try:
# 使用transport多个命令操作 固定写法
# 首先获取交换机名称
ts = paramiko.Transport(ip, 22)
ts.connect(username='******', password='******')
ssh = paramiko.SSHClient()
ssh._transport = ts
cmd = 'display cur | in sysname'
stdin, stdout, stderr = ssh.exec_command(cmd)
# 将结果分解成列表
# 取得sysname的下标，下标+1就是交换机名称
result = stdout.read().decode().split()
num = 0
for i in result:
if i == 'sysname':
break # 跳出循环num不再累加
else:
num += 1
name = result[num+1]
print(f'交换机名称{name}')
# 获取交换机名称和版本
ts = paramiko.Transport(ip, 22)
ts.connect(username='******', password='******')
ssh = paramiko.SSHClient()
ssh._transport = ts
cmd = 'display version' # 注意大小写
stdin, stdout, stderr = ssh.exec_command(cmd)
result = stdout.read().decode()
if 'H3C' in result:
brand = 'H3C'
else:
brand = 'Huawei'
version = result.split('Version')[1].split()[0]
print(f'交换机品牌{brand}')
print(f'交换机版本{version}')
# 将IP,名称，品牌，版本写入数据库
mm = Mysql_manager()
sql = 'insert ignore into jiguan_switch values(0,%s,%s,%s,%s)'
with mm:
mm.exe_db(sql, (name, ip, brand, version))
self.sw_list.remove(ip)
except:
print('连接当前服务器失败')
if self.sw_list == []:
break
flag += 1
if self.sw_list:
print(f'下列交换机无法连接上{self.sw_list}')
else:
print('交换机信息更新成功')