def select_table_show(self):
if not self.show_sqlTable.selectedItems():
return
self.select_table = self.show_sqlTable.selectedItems()[0].text()
# print(select_table)
try:
conn, cursor = DataManageUI.connect_to_sql()
if not DataRecordUI.table_exists(cursor, self.select_table):
raise FileNotFoundError
sql_select = 'SELECT * FROM `%s`' % self.select_table
cursor.execute(sql_select)
conn.commit()
stu_data = cursor.fetchall()
attendance_cnt = 0 # 出席人数计数
if len(stu_data[0]) != self.StuCheckTable.columnCount():
text = 'Error!'
informativeText = '<b>表格格式不正确，请重新选择正确的签到表格。</b>'
DataRecordUI.callDialog(QMessageBox.Critical, text,
informativeText, QMessageBox.Ok)
return
while self.StuCheckTable.rowCount() > 0:
self.StuCheckTable.removeRow(0)
for row_index, row_data in enumerate(stu_data):
self.StuCheckTable.insertRow(row_index) # 插入行
if row_data[2] == 1:
attendance_cnt += 1
for col_index, col_data in enumerate(row_data): # 插入列
self.StuCheckTable.setItem(row_index, col_index,
QTableWidgetItem(
str(col_data))) # 设置单元格文本
self.export_pushButton.setEnabled(True)
self.DelpushButton.setEnabled(True)
attendance_rate = attendance_cnt / len(stu_data) * 100
if 90 > attendance_rate >= 60:
self.attendance_label.setText(
'<b>出勤率：{}%</b>'.format(attendance_rate))
elif attendance_rate < 60:
self.attendance_label.setText(
'<b>出勤率：<font color=red>{}%</font></b>'.format(
attendance_rate))
elif 100 > attendance_rate >= 90:
self.attendance_label.setText(
'<b>出勤率：<font color=green>{}%</font></b>'.format(
attendance_rate))
else:
self.attendance_label.setText(
'<b>出勤率：<font color=blue>{}%</font></b>'.format(
attendance_rate))
except FileNotFoundError:
logging.error('系统找不到数据库表{}'.format(self.select_table))
except Exception as e:
print(e)
logging.error('读取数据库异常，无法完成数据库查询')
else:
cursor.close()
conn.close()
def queryUser(self):
# 获取输入框学号
select_data = dict()
select_data['stu_id'] = self.querystuIDLineEdit.text().strip()
select_data['cn_name'] = self.queryNameLineEdit.text().strip()
select_data['en_name'] = self.queryenNameLineEdit.text().strip()
select_data['major'] = self.queryMajorLineEdit.text().strip()
select_data['grade'] = self.queryGradeLineEdit.text().strip()
select_data['class'] = self.queryClassLineEdit.text().strip()
select_data['province'] = self.queryProvinceLineEdit.text().strip()
select_data['nation'] = self.queryNationLineEdit.text().strip()
# print(select_data)
conn, cursor = self.connect_to_sql()
try:
select_sql = 'SELECT * FROM users WHERE 1=1'
for key, value in select_data.items():
if value is not '':
if self.enable_like_select:
select_sql += ' AND %s LIKE "%%%s%%"' % (key, value)
else:
select_sql += ' AND %s LIKE "%s"' % (key, value)
# print(select_sql)
cursor.execute(select_sql)
ret = cursor.fetchall()
if not ret:
raise RecordNotFound
self.print_to_table(ret)
except RecordNotFound:
self.queryUserButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error：此用户不存在')
logging.warning('用户不存在{}'.format(str(select_data)))
text = 'Error!'
informativeText = '<b>此用户不存在。</b>'
DataRecordUI.callDialog(QMessageBox.Critical, text,
informativeText, QMessageBox.Ok)
except Exception as e:
print(e)
logging.error('读取数据库异常，无法查询到{}的用户信息'.format(str(select_data)))
self.queryUserButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error：读取数据库异常，查询失败')
else:
# 查询结果显示
self.logQueue.put('查询成功！')
self.queryUserButton.setIcon(QIcon('./icons/success.png'))
self.deleteUserButton.setEnabled(True) # 删除按钮启用
finally:
cursor.close()
conn.close()
def export_to_excel(self):
if not os.path.isdir('./export_excel'): # 导出结果存储目录
os.makedirs('./export_excel')
save_path = os.path.join('./export_excel', self.select_table + '.xls')
head_list = ['学号', '姓名', '是否出勤', '出勤时间']
xls = ExcelWrite.Workbook() # 创建Excel控制对象
sheet = xls.add_sheet("Sheet1") # 创建被写入的表格sheet1
style = XFStyle()
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = 0x16 # 设置其模式单元格背景色
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern
for col in range(len(head_list)): # 写入首行信息，为表头，表示列名
sheet.write(0, col, head_list[col], style)
sheet.col(col).width = 4240
try:
# 连接数据库读取数据
conn, cursor = DataManageUI.connect_to_sql()
sql = 'select * from `%s`' % self.select_table
cursor.execute(sql)
row = 0
stu_data = cursor.fetchall()
for stu_info in stu_data: # 遍历数据库中每行信息，一行表示一部电影的所有信息
stu_info = list(stu_info)
if stu_info[3]:
stu_info[3] = stu_info[3].strftime('%Y/%m/%d %H:%M:%S')
row = row + 1 # 第0行为表头，不添加数据，因此从第一列开始写入
for col in range(len(stu_info)): # 对于一行信息进行遍历，分别存入每列
sheet.write(row, col, stu_info[col])
xls.save(save_path) # 写入完成，存储
cursor.close()
conn.close()
text = 'Success!'
informativeText = '<b>课程{}签到表 导出成功! 目标路径：./export_excel</b>'.format(
self.select_table)
DataRecordUI.callDialog(QMessageBox.Information, text,
informativeText, QMessageBox.Ok)
except Exception as e:
print(e)
text = 'Error!'
informativeText = '<b>导出失败!</b>'
DataRecordUI.callDialog(QMessageBox.Critical, text,
informativeText, QMessageBox.Ok)
def cell_change(self, row, col):
try:
conn, cursor = self.connect_to_sql()
if not DataRecordUI.table_exists(cursor, self.database):
raise FileNotFoundError
item = self.tableWidget.item(row, col)
stu_id = self.tableWidget.item(row, 0).text()
after_change_txt = item.text()
select_sql = 'SELECT * FROM users WHERE stu_id=%s' % stu_id
cursor.execute(select_sql)
ret = cursor.fetchall()
if not ret:
raise RecordNotFound
else:
# print(ret[0])
before_change_txt = ret[0][col]
text = '确定将原数据<font color=blue> {} </font>修改为<font color=green> {} </font> 吗？<font color=red>该操作不可逆！</font>'.format(
before_change_txt, after_change_txt)
informativeText = '<b>是否继续？</b>'
ret = DataManageUI.callDialog(QMessageBox.Warning, text,
informativeText,
QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
update_sql = 'UPDATE users SET %s="%s" WHERE stu_id=%s' % (
self.sql_name_map[col], after_change_txt, stu_id)
cursor.execute(update_sql)
self.logQueue.put('修改成功！')
else:
if self.CellChangeButton.text() == '禁用编辑':
self.tableWidget.cellChanged.disconnect() # 表格变化时禁用修改监听
self.tableWidget.setItem(
row, col, QTableWidgetItem(str(before_change_txt)))
if self.CellChangeButton.text() == '禁用编辑':
self.enable_write_table()
# 如果不严格限制修改操作，将无限循环递归触发单元格变动逻辑，直到允许提交
except FileNotFoundError:
logging.error('系统找不到数据库表{}'.format(self.database))
self.isDbReady = False
self.initDbButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error：未发现数据库，你可能未进行人脸采集')
except Exception as e:
print(e)
logging.error('读取数据库异常，无法完成数据库初始化')
self.isDbReady = False
self.initDbButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error：读取数据库异常，初始化/刷新数据库失败')
else:
cursor.close()
conn.commit() # 修改手动commit提交
conn.close()
def loginFunc_record(self, event): # 登录
conn, cur = self.connect_sql()
if conn is None:
return
username, password = self.username.GetValue(), self.password.GetValue()
if username == "" or password == "":
wx.MessageBox('用户名密码不能为空', caption="错误提示")
return
try:
sql = 'select * from pyuser where user_name="%s"' % username
cur.execute(sql)
conn.commit()
user = cur.fetchone()
if user is None:
wx.MessageBox('用户不存在', caption="错误提示")
self.username.Clear()
self.password.Clear()
return
except:
wx.MessageBox('系统错误', caption="错误提示")
return
if username == user[0] and password == user[1]:
wx.MessageBox("登陆成功", caption="登陆成功")
self.Show(False)
logging.config.fileConfig('./config/logging.cfg')
app = QApplication(sys.argv)
window = DataRecordUI()
window.show()
sys.exit(app.exec())
else:
wx.MessageBox('用户名或者密码错误', caption="错误提示")
self.username.Clear()
self.password.Clear()
return
def initDb(self):
try:
conn, cursor = self.connect_to_sql() # 连接数据库
if not DataRecordUI.table_exists(cursor, self.database):
raise FileNotFoundError
cursor.execute('SELECT * FROM users')
conn.commit()
stu_data = cursor.fetchall()
# print(stu_data)
self.print_to_table(stu_data) # 输出到表格界面
cursor.execute('SELECT Count(*) FROM users') # 学生计数
result = cursor.fetchone()
dbUserCount = result[0]
except FileNotFoundError:
logging.error('系统找不到数据库表{}'.format(self.database))
self.isDbReady = False
self.initDbButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error：未发现数据库，你可能未进行人脸采集')
except Exception as e:
print(e)
logging.error('读取数据库异常，无法完成数据库初始化')
self.isDbReady = False
self.initDbButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error：读取数据库异常，初始化/刷新数据库失败')
else:
cursor.close()
conn.close()
# 人数显示
self.dbUserCountLcdNum.display(dbUserCount)
if not self.isDbReady:
self.isDbReady = True
self.logQueue.put(
'Success：数据库初始化完成，发现用户数：{}'.format(dbUserCount))
self.initDbButton.setText('刷新数据库') # 改变按钮文本
self.initDbButton.setIcon(QIcon('./icons/success.png'))
self.trainButton.setToolTip('')
self.trainButton.setEnabled(True) # 启用LBPH训练按钮
self.queryUserButton.setToolTip('')
self.queryUserButton.setEnabled(True) # 启用查询按钮
self.CellChangeButton.setToolTip('')
self.CellChangeButton.setEnabled(True) # 启用编辑开关
self.deleteUserButton.setToolTip('')
self.ExportExcelpushButton.setEnabled(True) # 启用导出表格按钮
self.dlibButton.setToolTip('')
self.dlibButton.setEnabled(True) # 启用dlib训练按钮
else:
self.logQueue.put(
'Success：刷新数据库成功，发现用户数：{}'.format(dbUserCount))
def del_table(self):
self.select_table = self.show_sqlTable.selectedItems()[0].text()
text = '确定<font color=blue> 删除 </font>表格<font color=blue> {} </font> 吗？<font color=red>该操作不可逆！</font>'.format(
self.select_table)
informativeText = '<b>是否继续？</b>'
ret = DataManageUI.callDialog(QMessageBox.Warning, text,
informativeText,
QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
sql_del_table = 'DROP TABLE `%s`' % self.select_table
try:
conn, cursor = DataManageUI.connect_to_sql()
if not DataRecordUI.table_exists(cursor, self.select_table):
raise FileNotFoundError
cursor.execute(sql_del_table)
conn.commit()
text = 'Success!'
informativeText = '<b>{} 签到表 已删除！</b>'.format(
self.select_table)
DataRecordUI.callDialog(QMessageBox.Information, text,
informativeText, QMessageBox.Ok)
except FileNotFoundError:
logging.error('系统找不到数据库表{}'.format(self.select_table))
except Exception as e:
print(e)
text = 'Error!'
informativeText = '<b>无法删除!</b>'
DataRecordUI.callDialog(QMessageBox.Critical, text,
informativeText, QMessageBox.Ok)
else:
cursor.close()
conn.close()
self.fresh_table_list()