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 regitster_to_DB(self):
user = self.user.text() # user对象文本框返回取得的值，文本框内容只要输入，就有返回值。随时调用，随时返回
passwd = self.passwd.text()
confirm = self.confirm.text()
# 首先进行合法性判断 暂时不做正则判断
if user == '' or passwd == '' or confirm == '':
print('输入不能为空') # 命令行输出
print(
QMessageBox.warning(None, '输入错误', '帐号密码不能为空', QMessageBox.Yes,
QMessageBox.Yes))
elif passwd != confirm:
print('两次密码输入不一致')
print(
QMessageBox.warning(None, 'notice', '密码输入不一致', QMessageBox.Yes,
QMessageBox.Yes))
else:
# 确认输入无误后连接数据库并且保存信息
mm = Mysql_manager('localhost', 'root', 'root', 3306, 'milkbottle')
with mm:
# 不需要指定使用数据库？
sql = 'insert into user_info values(%s,%s,%s)'
mm.cur.execute(sql, (user, passwd, 1)) # 最后一个参数用作其他判断
# with语句在结束时自动调用exit方法（该方法自动commit和关闭）
print('注册成功')
print(
QMessageBox.warning(None, '注册成功', '账号密码写入到数据库',
QMessageBox.Yes, QMessageBox.Yes))
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 add_em(self):
# 首先建立数据库连接。实例数据库操作类,属性没必要保存，一般变量即可
name = self.rm.is_name()
dept = self.rm.is_dept()
ip = self.rm.is_ip()
mac = self.rm.is_mac()
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s)'
mm.cur.execute(sql,(name,dept,ip,mac))
print(f'{name}的信息添加成功')
def del_em(self): # 在这里顺便测试try语法
print('支持通过姓名删除相关信息。')
name = self.rm.is_name()
mm = Mysql_manager('localhost','root','root',3306,'milkbottle') # 实例化并不会连接数据库，enter方法才调用连接
with mm:
sql = 'delete from em_info where em_name = %s'
mm.cur.execute(sql,name)
if mm.cur.rowcount == 0:
print('没有相关员工信息')
else:
print('信息删除成功')
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 export_excel(self,path):
self.wb = Workbook()
self.ws = self.wb.active
# 先输出表头
self.ws.cell(row = 1,column = 1).value = '姓名'
self.ws.cell(row = 1,column = 2).value = '部门'
self.ws.cell(row = 1,column = 3).value = 'IP地址'
self.ws.cell(row = 1,column = 4).value = 'MAC地址'
mm = Mysql_manager()
with mm:
result = mm.db_show()
num = 2 # 计数递增使用
for i in result:
self.ws.cell(row = num,column = 1).value = i[0]
self.ws.cell(row = num,column = 2).value = i[1]
self.ws.cell(row = num,column = 3).value = i[2]
self.ws.cell(row = num,column = 4).value = i[3]
num += 1
#最后记得需要保存
print(f'数据成功导出到{path}')
self.wb.save(path)
def del_clicked(self):
name = self.user_lineEdit.text()
ip = self.ip_lineEdit.text()
if name == '' and ip == '':
print('请至少输入一个条件')
reply = QMessageBox.about(self, '提示', '至少输入一个条件来删除')
else:
# 正则判断
mm = Mysql_manager()
rem = Re_manager()
name = rem.is_name(name)
ip = rem.is_ip(ip)
if name == None and ip == None:
print('输入格式不正确')
reply = QMessageBox.about(self, '提示', '输入格式不正确')
elif name == None: # 这里开始至少一个不为0
with mm:
sql = 'delete from em_info where em_ip = %s'
mm.db_exe(sql, ip)
if mm.cur.rowcount == 0:
print('没有该IP相关信息')
reply = QMessageBox.about(self, '提示', '没有该IP的相关信息')
else:
print('删除成功')
reply = QMessageBox.about(self, '提示', f'{ip}的相关信息已删除')
elif ip == None:
with mm:
sql = 'delete from em_info where em_name = %s'
mm.db_exe(sql, name)
if mm.cur.rowcount == 0:
print('没有该员工信息')
reply = QMessageBox.about(self, '提示', '数据库没有该员工信息')
else:
print('删除成功')
reply = QMessageBox.about(self, '提示',
f'{name}的相关信息已删除')
else: # 最后一种情况，两个文本框都有内容
with mm:
sql = 'select * from em_info where em_name = %s and em_ip = %s'
mm.db_exe(sql, (name, ip))
if mm.cur.rowcount == 0:
print('没有相关信息')
reply = QMessageBox.about(self, '提示', '没有相关信息')
else:
print('删除成功')
reply = QMessageBox.about(self, '提示', '信息删除成功')
# 无论如何，清空文本框
self.user_lineEdit.setText('')
self.ip_lineEdit.setText('')
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 del_em(self):
name = input('请输入员工姓名：')
name = self.rem.is_name(name)
if name == None:
print('输入格式不正确')
else:
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm:
sql = 'delete from em_info where em_name = %s'
mm.cur.execute(sql,name)
if mm.cur.rowcount == 0: # 代表上条语句执行后受影响条数。如果影响行数为1.代表成功
print('没有当前员工信息')
else:
print('当前员工信息删除成功')
def import_excel(self,path):
# 表格对象
self.wb = load_workbook(path)
# 表单（子表）对象
self.ws = self.wb.active
# 建立数据库连接
mm = Mysql_manager()
sql = 'insert into em_info values(%s,%s,%s,%s)'
with mm:
# 循环读出，循环写入
row_num = self.ws.max_row # 获取子表行数
print('测试')
print(row_num)
# 注意表格计数是从1开始，不是0
for i in range(2,row_num+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
mm.db_exe(sql,(name,dept,ip,mac))
print('数据成功导入到数据库')
def show_em(self):
# 首先从数据库读取相关文件,注意考虑是否需要类变量还是临时变量
mm = Mysql_manager()
with mm:
result = mm.query_db() # 返回值对应fetchall
row_num = mm.cur.rowcount # 数据库实例还在内存。所以self.cur.rowcount还在内存，可以调用
# 建立数据模型 这里需要得到qtsql
self.model = QtGui.QStandardItemModel(row_num, 4) # 建立二维数据模型
# 建立数据模型的水平表头
title = ['姓名', '部门', 'IP', 'MAC']
self.model.setHorizontalHeaderLabels(title)
# 将数据模型和显示视图进行绑定
self.show_tableview.setModel(self.model)
#下面这里设置列宽必须在设置model以后，否则实际不会生效
self.show_tableview.setColumnWidth(0, 170)
self.show_tableview.setColumnWidth(1, 170)
self.show_tableview.setColumnWidth(2, 170)
self.show_tableview.setColumnWidth(3, 170)
# 循环方式实例每个元素数据，绑定过。实例的数据自动同步到view
# result 是个二维元组集合
num = 0 # 循环递增使用
for i in result:
# i就是一维元组了
item0 = QtGui.QStandardItem(i[0])
item1 = QtGui.QStandardItem(i[1])
item2 = QtGui.QStandardItem(i[2])
item3 = QtGui.QStandardItem(i[3])
# 四个实例元素分别赋值给对应位置元素
self.model.setItem(num, 0, item0)
self.model.setItem(num, 1, item1)
self.model.setItem(num, 2, item2)
self.model.setItem(num, 3, item3)
# 每次循环完item0-3重置，重新赋值了
num += 1
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 modify_em(self):
name = self.rm.is_name()
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm:
sql = 'select * from em_info where em_name = %s'
mm.cur.execute(sql,name)
if mm.cur.rowcount == 0:
print('没有当前员工信息')
else:
dept = self.rm.is_dept()
ip = self.rm.is_ip()
mac = self.rm.is_mac()
sql = 'update em_info set em_dept = %s,em_ip = %s,em_mac = %s where em_name = %s'
mm.cur.execute(sql,(dept,ip,mac,name)) # 注意参数顺序
print(f'{name}的个人信息更新成功')
def show_em(self): # 这里验证fetchall的用法。就是个二维元组（列表），双循环打印出来
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm:
sql = 'select * from em_info'
mm.cur.execute(sql)
# 先打印表头
print('姓名'.ljust(8),end = '\t')
print('部门'.ljust(8),end = '\t')
print('ip'.ljust(8),end = '\t')
print('mac'.ljust(8))
result = mm.cur.fetchall()
for i in result:
for j in i:
print(j.ljust(8),end = '\t')
print()
def modify_em(self): # 注意用的都是临时变量
name = input('请输入准备修改的员工的姓名：')
name = self.rem.is_name(name)
dept = input('部门：')
dept = self.rem.is_dept(dept)
ip = input('ip：')
ip = self.rem.is_ip(ip)
mac = input('mac：')
mac = self.rem.is_mac(mac)
if dept == None or ip == None or mac == None or name == None:
print('需要修改的信息格式不正确。')
else:
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm: # 判断是否存在和修改一条过。通过rowcount判断是否修改了数据
sql = 'update em_info set em_dept = %s,em_ip = %s,em_mac = %s where em_name = %s'
mm.cur.execute(sql,(dept,ip,mac,name))
print('信息修改成功')
def setupUi(self):
# MVC 模式 mode添加数据源 viewer qtableview实例 通过controller控制
self.model = QStandardItemModel(3, 4)
title = ['姓名', '部门', 'IP', 'MAC']
self.model.setHorizontalHeaderLabels(title) # 设置表格抬头
self.tableview = QTableView() # 设置viewer
self.tableview.horizontalHeader().setStretchLastSection(True)
# self.tableview.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 将M和C进行关联
self.tableview.setModel(self.model)
# 给表格添加数据（给model添加数据）
mm = Mysql_manager('localhost', 'root', 'root', 3306, 'milkbottle')
with mm:
sql = 'select * from em_info'
mm.cur.execute(sql)
result = mm.cur.fetchall()
# for i in result:
# print(i[2])
m = 0
for i in result:
item0 = QStandardItem(i[0]) # 分别取得姓名 部门 ip mac
item1 = QStandardItem(i[1])
item2 = QStandardItem(i[2])
item3 = QStandardItem(i[3])
self.model.setItem(m, 0, item0)
self.model.setItem(m, 1, item1)
self.model.setItem(m, 2, item2)
self.model.setItem(m, 3, item3)
m += 1
# 设置布局 不加这三行不显示列表控件
self.layout = QVBoxLayout()
self.layout.addWidget(self.tableview)
self.setLayout(self.layout)
self.resize(600, 600)
self.show()
def import_excel(self):
# 首先判断需要导入的表格是否存在。防止后面报错
if os.path.exists('template.xlsx'):
# 首先获取表格对象
# 一次添加，最后commit
mm = Mysql_manager('localhost', 'root', 'root', 3306, 'milkbottle')
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s)'
wb = load_workbook('template.xlsx')
# 然后获取工作单子表
ws = wb.active
# 获取工作单行数
row_num = ws.max_row # 属性，不是函数max_row 第一行为表头
for i in range(2, row_num + 1): # 表格是从1开始，而不是0
name = ws.cell(row=i, column=1).value
dept = ws.cell(row=i, column=2).value
ip = ws.cell(row=i, column=3).value
mac = ws.cell(row=i, column=4).value
mm.cur.execute(sql, (name, dept, ip, mac))
print('从表格导入信息成功')
else:
print('请在指定目录存放template.xlsx文件')
def export_excel(self):
wb = Workbook()
ws = wb.active
# 输出表头
ws.cell(row=1, column=1).value = '姓名'
ws.cell(row=1, column=2).value = '部门'
ws.cell(row=1, column=3).value = 'ip'
ws.cell(row=1, column=4).value = 'mac'
mm = Mysql_manager('localhost', 'root', 'root', 3306, 'milkbottle')
with mm:
sql = 'select * from em_info'
mm.cur.execute(sql)
result = mm.cur.fetchall()
num = 2 # 从第二行开始添加
for i in result:
# 每一个二维元组第一次拆包就是一个一元元组
ws.cell(row=num, column=1).value = i[0]
ws.cell(row=num, column=2).value = i[1]
ws.cell(row=num, column=3).value = i[2]
ws.cell(row=num, column=4).value = i[3]
num += 1
print('数据导出成功')
wb.save('backup.xlsx')
def show_singel(self, result): # 这里的参数就是发射的返回值
# 对接收的数据做显示处理
# 建立数据模型
print('跳转到主窗口显示单个函数成功')
mm = Mysql_manager()
with mm:
self.model = QtGui.QStandardItemModel(len(result), 4)
title = ['姓名', '部门', 'IP', 'MAC']
self.model.setHorizontalHeaderLabels(title)
self.tableView.setModel(self.model)
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])
self.model.setItem(num, 0, item0)
self.model.setItem(num, 1, item1)
self.model.setItem(num, 2, item2)
self.model.setItem(num, 3, item3)
num += 1
def import_excel(self):
# 导入数据库,先判断是否存在待导入文件
if os.path.exists('template.xlsx'):
# 建立表格对象
wb = load_workbook('template.xlsx')
# 获取表单对象
ws = wb.active
# 获取表格行数,注意后面没有括号
row_num = ws.max_row
# 实例数据库操作类。建立连接
mm = Mysql_manager('localhost', 'root', 'root', 3306, 'milkbottle')
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s)'
# 表格是1开始计数。第二行开始是需要的数据
for i in range(2, row_num + 1):
name = ws.cell(row=i, column=1).value
dept = ws.cell(row=i, column=2).value
ip = ws.cell(row=i, column=3).value
mac = ws.cell(row=i, column=4).value
mm.cur.execute(sql, (name, dept, ip, mac))
print('数据导入成功')
else:
print('没有找到表格文件')
def query_em(self):
name = input('请输入需要查询的员工姓名：')
name = self.rem.is_name(name)
if name == None:
print('输入格式不正确')
else:
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm:
sql = 'select * from em_info where em_name = %s'
mm.cur.execute(sql,name)
result = mm.cur.fetchall()
if mm.cur.rowcount == 0:
print('员工信息不存在')
else:
# 多个查询值
#先打印抬头
print('姓名'.ljust(8),end = '\t')
print('部门'.ljust(8),end = '\t')
print('ip'.ljust(8),end = '\t')
print('mac'.ljust(8))
for i in result:
for j in i:
print(j.ljust(8),end = '\t')
print()
def add_em(self):
name = input('请输入员工姓名：')
dept = input('请输入员工部门：')
ip = input('请输入员工IP：')
mac = input('请输入员工MAC：')
# 传入正则类做比较
name = self.rem.is_name(name)
dept = self.rem.is_dept(dept)
ip = self.rem.is_ip(ip)
mac = self.rem.is_mac(mac)
if name == None or dept == None or ip == None or mac == None:
print('输入格式不正确。返回主菜单') # 如果想返回子菜单，需要添加子循环
print(name)
print(dept)
print(ip)
print(mac)
else:
mm = Mysql_manager('localhost','root','root',3306,'milkbottle')
with mm:
sql = 'insert into em_info values(%s,%s,%s,%s)'
mm.cur.execute(sql,(name,dept,ip,mac)) # 第二个参数一个元祖，字符串都行，多个就用元祖
print(f'{name}的个人信息添加成功')
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)
# 测试python 查询数据库的返回值格式
from Mysql_manager import *
mm = Mysql_manager('localhost', 'root', 'root', 3306, 'milkbottle')
with mm:
# mm.cur.execute('use milkbottle')
# mm.cur.execute('select user_passwd from user_manager where user_name = "user2"')
# result = mm.cur.fetchone()
# print(result[0])
sql = "delete from em_info where em_name = '咔咔'"
# sql = 'select * from em_info'
mm.cur.execute(sql)
print(mm.cur.rowcount)
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('交换机信息更新成功')
