-
Notifications
You must be signed in to change notification settings - Fork 0
/
dataManage.py
896 lines (787 loc) · 40.9 KB
/
dataManage.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
#!/usr/bin/env python3
# Author: kuronekonano <[email protected]>
import csv
import re
import cv2
import dlib
import numpy as np
import pymysql
import xlwt as ExcelWrite
from xlwt import Borders, XFStyle, Pattern
from PyQt5.QtCore import pyqtSignal, QThread, Qt, QObject
from PyQt5.QtGui import QIcon, QTextCursor
from PyQt5.QtWidgets import QApplication, QWidget, QMessageBox, QTableWidgetItem, QAbstractItemView, QProgressBar, \
QDialog, QHeaderView
from PyQt5.uic import loadUi
import logging
import logging.config
import os
import shutil
import sys
import threading
import multiprocessing
from datetime import datetime
from dataRecord import DataRecordUI
haar_face_cascade = cv2.CascadeClassifier('./haarcascades/haarcascade_frontalface_default.xml') # 加载分类器
predictor_5 = dlib.shape_predictor('./shape_predictor_5_face_landmarks.dat') # 5特征点模型
predictor_68 = dlib.shape_predictor('./shape_predictor_68_face_landmarks.dat') # 68特征点模型
face_rec = dlib.face_recognition_model_v1("dlib_face_recognition_resnet_model_v1.dat") # 人脸识别器模型
dlib_detector = dlib.get_frontal_face_detector() # dlib 人脸检测器
# 自定义数据库记录不存在异常
class RecordNotFound(Exception):
pass
class DataManageUI(QWidget):
logQueue = multiprocessing.Queue() # 日志队列
receiveLogSignal = pyqtSignal(str) # 日志信号
sql_name_map = ('stu_id', 'face_id', 'cn_name', 'en_name', 'major', 'grade', 'class', 'sex', 'province', 'nation',
'last_attendance_time', 'total_attendance_times', 'create_time') # 数据库字段映射
def __init__(self):
super(DataManageUI, self).__init__()
loadUi('./ui/DataManage.ui', self)
self.setWindowIcon(QIcon('./icons/icon.png'))
# self.setFixedSize(1511, 941) # 加上后就规定了窗口大小,且不可缩放
# 设置tableWidget只读,不允许修改
self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
# 表头自动排序√
# self.tableWidget.setSortingEnabled(True)
# 表格双色√
self.tableWidget.setAlternatingRowColors(True)
# 表头自动伸缩×
# self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 取消表格内框线条×
# self.tableWidget.setFrameStyle(QFrame.NoFrame)
# 默认选择整行√
# self.tableWidget.setSelectionBehavior(1)
# 选择多行开关√
# self.tableWidget.setSelectionMode(QAbstractItemView.ExtendedSelection)
# 数据库
self.database = 'users'
self.datasets = './datasets'
self.isDbReady = False
self.current_select = set() # 选中内容存储器
# 连接数据库
self.initDbButton.clicked.connect(self.initDb)
# 按住鼠标拖动选择,但是单击选择无效,弃用
# self.tableWidget.itemEntered.connect(self.enable_delete_button)
# 获取当前选中的元素,单击选择,按住Ctrl多选,按住shift连续选
self.tableWidget.itemClicked.connect(self.enable_delete_button)
# 用户管理
self.queryUserButton.clicked.connect(self.queryUser)
self.deleteUserButton.clicked.connect(self.deleteUser)
self.CellChangeButton.clicked.connect(self.modify_line) # 启用/禁用编辑功能
self.ExportExcelpushButton.clicked.connect(self.check_table)
# 直方图均衡化
self.isEqualizeHistEnabled = False
self.equalizeHistCheckBox.stateChanged.connect(
lambda: self.enableEqualizeHist(self.equalizeHistCheckBox))
# 训练人脸数据
self.trainButton.clicked.connect(self.train)
self.dlibButton.clicked.connect(self.train_by_dlib)
# 系统日志
self.receiveLogSignal.connect(lambda log: self.logOutput(log))
self.logOutputThread = threading.Thread(target=self.receiveLog, daemon=True)
self.logOutputThread.start()
# 模糊查询开关
self.enable_like_select = False
self.LikeSelectCheckBox.stateChanged.connect(
lambda: self.is_like_select(self.LikeSelectCheckBox))
# 模糊查询开关
def is_like_select(self, like_select_checkbox):
if like_select_checkbox.isChecked():
self.enable_like_select = True
else:
self.enable_like_select = False
def check_table(self):
self.export_excel_dialog = ExportExcelDialog()
self.export_excel_dialog.exec()
# 数据修改提交数据库
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()
# print(txt)
# 数据库连接
@staticmethod
def connect_to_sql():
conn = pymysql.connect(host='localhost',
user='root',
password='970922',
db='mytest',
port=3306,
charset='utf8')
cursor = conn.cursor()
return conn, cursor
# 单元格编辑开关
def modify_line(self):
if self.CellChangeButton.text() == '禁用编辑':
self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.CellChangeButton.setText('启用编辑')
self.enable_select_table()
else:
self.deleteUserButton.setEnabled(False)
self.tableWidget.setEditTriggers(QAbstractItemView.DoubleClicked)
self.CellChangeButton.setText('禁用编辑')
self.enable_write_table()
# 启用编辑时学号和faceID禁止修改
def enable_write_table(self):
row_count = self.tableWidget.rowCount()
for row in range(row_count):
self.tableWidget.item(row, 0).setFlags(Qt.ItemIsEnabled)
self.tableWidget.item(row, 1).setFlags(Qt.ItemIsEnabled)
self.tableWidget.cellChanged.connect(self.cell_change) # 输出结束后重新关联修改监听
# 禁用编辑时要将禁止选择的属性修改回来
def enable_select_table(self):
self.tableWidget.cellChanged.disconnect()
row_count = self.tableWidget.rowCount()
for row in range(row_count):
self.tableWidget.item(row, 0).setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled)
self.tableWidget.item(row, 1).setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled)
# 一个大坑,所有的setflags枚举类型单独用都是相反的效果,只有用位运算合用才能达到理想的效果
# 是否执行直方图均衡化
def enableEqualizeHist(self, equalizeHistCheckBox):
if equalizeHistCheckBox.isChecked():
self.isEqualizeHistEnabled = True
else:
self.isEqualizeHistEnabled = False
# 筛选选中数据
def enable_delete_button(self, item):
self.current_select.clear()
select_items = self.tableWidget.selectedItems()[::self.tableWidget.columnCount()] # 取出所有选择数据中的学号
self.current_select.update(
map(lambda x: x.text(), select_items)) # 更新学号信息到set集合中,因为用的是map映射整个list的内容,因此用update而不是add
# print(self.current_select)
if self.current_select and self.CellChangeButton.text() != '禁用编辑':
self.deleteUserButton.setEnabled(True)
else:
self.deleteUserButton.setEnabled(False)
# 输出结果至界面表格
def print_to_table(self, stu_data):
if self.CellChangeButton.text() == '禁用编辑':
self.tableWidget.cellChanged.disconnect() # 表格变化时禁用修改监听
# 刷新前清空tableWidget
while self.tableWidget.rowCount() > 0:
self.tableWidget.removeRow(0)
# self.tableWidget.setRowCount(0) # 通过直接设定行数清空表格
# self.tableWidget.clearContents() # 删除包含元素,不包括表头,会留下行数
for row_index, row_data in enumerate(stu_data):
self.tableWidget.insertRow(row_index) # 插入行
for col_index, col_data in enumerate(row_data): # 插入列
self.tableWidget.setItem(row_index, col_index, QTableWidgetItem(str(col_data))) # 设置单元格文本
attendance_rate = round(row_data[11]/(row_data[10] if row_data[10] != 0 else 1), 5) * 100
self.tableWidget.setItem(row_index, len(row_data), QTableWidgetItem(str(attendance_rate) + '%')) # 设置单元格文本
if self.CellChangeButton.text() == '禁用编辑':
self.enable_write_table()
# 初始化/刷新数据库
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 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 deleteUser(self):
del_user = tuple(self.current_select)
if len(del_user) == 1:
str_del_user = '(' + str(del_user[0]) + ')' # 元组只有一个元素的时候会多逗号
else:
str_del_user = str(del_user)
text = '已选择{}个用户。从数据库中删除选中用户,同时删除相应人脸数据,<font color=red>该操作不可逆!</font>'.format(len(del_user))
informativeText = '<b>是否继续?</b>'
ret = DataManageUI.callDialog(QMessageBox.Warning, text, informativeText, QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
conn, cursor = self.connect_to_sql()
del_sql = 'DELETE FROM users WHERE stu_id IN %s' % str_del_user
# print(del_sql)
try:
cursor.execute(del_sql)
except Exception as e:
print(e)
cursor.close()
logging.error('无法从数据库中删除{}'.format(del_user))
self.deleteUserButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error:读写数据库异常,删除失败')
else:
cursor.close()
conn.commit() # 删除手动commit提交
for stu_id in del_user:
if os.path.exists('{}/stu_{}'.format(self.datasets, stu_id)):
try:
shutil.rmtree('{}/stu_{}'.format(self.datasets, stu_id))
except Exception as e:
logging.error('系统无法删除删除{}/stu_{}'.format(self.datasets, stu_id))
self.logQueue.put('Error:删除人脸数据失败,请手动删除{}/stu_{}目录'.format(self.datasets, stu_id))
text = '你已成功删除{}个用户记录。'.format(len(del_user))
informativeText = '<b>请在右侧菜单重新训练人脸数据。</b>'
DataManageUI.callDialog(QMessageBox.Information, text, informativeText, QMessageBox.Ok)
# 删除用户后重新读取数据库
self.initDb()
self.deleteUserButton.setIcon(QIcon('./icons/success.png'))
self.deleteUserButton.setEnabled(False)
self.queryUserButton.setIcon(QIcon())
finally:
conn.close()
# dlib人脸特征提取
def train_by_dlib(self):
try:
if not os.path.isdir(self.datasets):
raise FileNotFoundError
text = '系统将开始提取人脸特征,界面会暂停响应一段时间,完成后会弹出提示。'
informativeText = '<b>训练过程请勿进行其它操作,是否继续?</b>'
ret = DataManageUI.callDialog(QMessageBox.Question, text, informativeText,
QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
progress_bar = ActionsTrainByDlib(self)
except FileNotFoundError:
logging.error('系统找不到人脸数据目录{}'.format(self.datasets))
self.dlibButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('未发现人脸数据目录{},你可能未进行人脸采集'.format(self.datasets))
except Exception as e:
logging.error('遍历人脸库出现异常,训练人脸数据失败')
self.dlibButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error:遍历人脸库出现异常,训练失败')
else:
text = '<font color=green><b>Success!</b></font> 系统已生成./dlib_128D_csv/features_all.csv'
informativeText = '<b>人脸特征提取完成!</b>'
DataManageUI.callDialog(QMessageBox.Information, text, informativeText, QMessageBox.Ok)
self.dlibButton.setIcon(QIcon('./icons/success.png'))
self.logQueue.put('Success:dlib人脸数据特征提取完成')
self.initDb()
# 训练人脸数据
# Reference:https://github.com/informramiz/opencv-face-recognition-python
def train(self):
try:
if not os.path.isdir(self.datasets):
raise FileNotFoundError
text = '系统将开始训练人脸数据,界面会暂停响应一段时间,完成后会弹出提示。'
informativeText = '<b>训练过程请勿进行其它操作,是否继续?</b>'
ret = DataManageUI.callDialog(QMessageBox.Question, text, informativeText,
QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
progress_bar = ActionsTrainByLBPH(self)
except FileNotFoundError:
logging.error('系统找不到人脸数据目录{}'.format(self.datasets))
self.trainButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('未发现人脸数据目录{},你可能未进行人脸采集'.format(self.datasets))
except Exception as e:
logging.error('遍历人脸库出现异常,训练人脸数据失败')
self.trainButton.setIcon(QIcon('./icons/error.png'))
self.logQueue.put('Error:遍历人脸库出现异常,训练失败')
else:
text = '<font color=green><b>Success!</b></font> 系统已生成./recognizer/trainingData.yml'
informativeText = '<b>人脸数据训练完成!</b>'
DataManageUI.callDialog(QMessageBox.Information, text, informativeText, QMessageBox.Ok)
self.trainButton.setIcon(QIcon('./icons/success.png'))
self.logQueue.put('Success:人脸数据训练完成')
self.initDb()
# 系统日志服务常驻,接收并处理系统日志
def receiveLog(self):
while True:
data = self.logQueue.get()
if data:
self.receiveLogSignal.emit(data)
# LOG输出
def logOutput(self, log):
# 加入时间前缀
time = datetime.now().strftime('[%Y/%m/%d %H:%M:%S]')
log = time + ' ' + log + '\n'
self.logTextEdit.moveCursor(QTextCursor.End) # 日志光标挪至底部
self.logTextEdit.insertPlainText(log) # 插入日志消息
self.logTextEdit.ensureCursorVisible() # 自动滚屏
# 系统对话框
@staticmethod
def callDialog(icon, text, informativeText, standardButtons, defaultButton=None):
msg = QMessageBox()
msg.setWindowIcon(QIcon('./icons/icon.png'))
msg.setWindowTitle('人脸识别考勤管理系统 - DataManage')
msg.setIcon(icon)
msg.setText(text)
msg.setInformativeText(informativeText)
msg.setStandardButtons(standardButtons)
if defaultButton:
msg.setDefaultButton(defaultButton)
return msg.exec()
# LBPH进度条
class ActionsTrainByLBPH(QDialog):
"""
Simple dialog that consists of a Progress Bar and a Button.
Clicking on the button results in the start of a timer and
updates the progress bar.
"""
def __init__(self, datamanage):
super(ActionsTrainByLBPH, self).__init__()
self.data_manage = datamanage
self.initUI()
def initUI(self):
self.setWindowTitle('正在训练模型...')
self.progress = QProgressBar(self)
self.progress.setGeometry(0, 0, 300, 25)
self.progress.setMaximum(100)
self.train_data = TrainData(self.data_manage) # 导入图片线程实例
self.train_data.progress_bar_signal.connect(self.onCountChanged) # 信号槽函数绑定
self.train_data.start()
self.exec()
# 注意此处有坑,进度条对话框应该使用exec()事件循环而不是show(),使用show()与QThread时会导致对话框无法完全结束,后续语句无法执行
def onCountChanged(self, value):
self.progress.setValue(value)
if value >= 100:
self.close()
# 关于训练过程独立出线程执行,并使用进度条更新的方法
# 因为训练过程比较耗时的是检测脸与读取图片数据集,并且只有此处是循环每个人的遍历
# 因此将进度条主要展现的是读取数据,而不是训练数据,训练数据耗时相对不多,因此完全结束后只占进度条的1%
# 为实现训练函数的整个过程,将读取数据,训练数据,脸部检测 的处理函数全部挪到该线程中执行
# LBPH模型训练
class TrainData(QThread):
progress_bar_signal = pyqtSignal(float)
def __init__(self, data_manage):
super(TrainData, self).__init__()
self.data_manage = data_manage
def run(self) -> None:
face_recognizer = cv2.face.LBPHFaceRecognizer_create()
if not os.path.exists('./recognizer'):
os.makedirs('./recognizer')
faces, labels = self.prepareTrainingData(self.data_manage.datasets) # 准备图片数据
face_recognizer.train(faces, np.array(labels))
face_recognizer.save('./recognizer/trainingData.yml')
self.progress_bar_signal.emit(100)
# 准备图片数据,参数为数据集路径
def prepareTrainingData(self, data_folder_path):
dirs = os.listdir(data_folder_path) # 返回指定的文件夹包含的文件或文件夹的名字的列表
faces = []
labels = []
face_id = 1
conn, cursor = DataManageUI.connect_to_sql()
people_count = len(dirs)
# 遍历人脸库
for index, dir_name in enumerate(dirs):
bar = index / people_count * 100
self.progress_bar_signal.emit(bar)
if not dir_name.startswith('stu_'): # 跳过不合法的图片集
continue
stu_id = dir_name.replace('stu_', '') # 获取图片集对应的学号
try:
cursor.execute('SELECT * FROM users WHERE stu_id=%s', (stu_id,)) # 根据学号查询学生信息
ret = cursor.fetchall()
if not ret:
raise RecordNotFound # 在try里raise错误类型,在except里再处理
cursor.execute('UPDATE users SET face_id=%s WHERE stu_id=%s', (face_id, stu_id,)) # 对可以训练的人脸设置face_id
except RecordNotFound:
logging.warning('数据库中找不到学号为{}的用户记录'.format(stu_id))
DataManageUI.logQueue.put('发现学号为{}的人脸数据,但数据库中找不到相应记录,已忽略'.format(stu_id))
continue
subject_dir_path = os.path.join(data_folder_path, dir_name) # 子目录
subject_images_names = os.listdir(subject_dir_path) # 获取所有图片名
for image_name in subject_images_names:
if image_name.startswith('.'): # 忽略隐藏文件
continue
image_path = os.path.join(subject_dir_path, image_name)
image = cv2.imread(image_path) # 读取图片
face, rect = self.detectFace(image) # 探测人脸,返回
if face is not None:
faces.append(face) # 检测到的脸放入list
labels.append(face_id) # face_id放入标签,同一个人的脸同一个face_id
face_id = face_id + 1
cursor.close()
conn.commit()
conn.close()
return faces, labels
# haar检测人脸
def detectFace(self, img):
gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY) # 灰度图
if self.data_manage.isEqualizeHistEnabled: # 直方均衡化
gray = cv2.equalizeHist(gray)
faces = haar_face_cascade.detectMultiScale(gray, scaleFactor=1.3, minNeighbors=5, minSize=(90, 90)) # 抠脸
if len(faces) == 0:
return None, None
(x, y, w, h) = faces[0]
return gray[y:y + w, x:x + h], faces[0]
# LBPH进度条
class ActionsTrainByDlib(QDialog):
"""
Simple dialog that consists of a Progress Bar and a Button.
Clicking on the button results in the start of a timer and
updates the progress bar.
"""
def __init__(self, datamanage):
super(ActionsTrainByDlib, self).__init__()
self.data_manage = datamanage
self.initUI()
def initUI(self):
self.setWindowTitle('正在训练模型...')
self.progress = QProgressBar(self)
self.progress.setGeometry(0, 0, 300, 25)
self.progress.setMaximum(100)
self.train_data_by_dlib = TrainDataByDlib(self.data_manage) # 导入图片线程实例
self.train_data_by_dlib.progress_bar_signal.connect(self.onCountChanged) # 信号槽函数绑定
self.train_data_by_dlib.start()
self.exec() # 消息循环
# 注意此处有坑,进度条对话框应该使用exec()事件循环而不是show(),使用show()与QThread时会导致对话框无法完全结束,后续语句无法执行
def onCountChanged(self, value):
self.progress.setValue(value)
if value >= 100:
self.close()
# Dlib特征点提取
class TrainDataByDlib(QThread):
progress_bar_signal = pyqtSignal(float)
def __init__(self, data_manage):
super(TrainDataByDlib, self).__init__()
self.data_manage = data_manage
def run(self) -> None:
if not os.path.exists('./dlib_128D_csv'):
os.makedirs('./dlib_128D_csv')
data_folder_path = self.data_manage.datasets # 准备图片数据
dirs = os.listdir(data_folder_path) # 返回指定的文件夹包含的文件或文件夹的名字的列表
face_id = 1
conn, cursor = DataManageUI.connect_to_sql()
people_count = len(dirs)
# 遍历人脸库
with open('./dlib_128D_csv/features_all.csv', 'w', newline="") as csvfile:
writer = csv.writer(csvfile)
for index, dir_name in enumerate(dirs):
bar = index / people_count * 100
self.progress_bar_signal.emit(bar)
if not dir_name.startswith('stu_'): # 跳过不合法命名的图片集
continue
stu_id = dir_name.replace('stu_', '') # 获取图片集对应的学号
try:
cursor.execute('SELECT * FROM users WHERE stu_id=%s', (stu_id,)) # 根据学号查询学生信息
ret = cursor.fetchall()
if not ret:
raise RecordNotFound # 在try里raise错误类型,在except里再处理
cursor.execute('UPDATE users SET face_id=%s WHERE stu_id=%s',
(face_id, stu_id,)) # 对可以训练的人脸设置face_id
except RecordNotFound:
logging.warning('数据库中找不到学号为{}的用户记录'.format(stu_id))
DataManageUI.logQueue.put('发现学号为{}的人脸数据,但数据库中找不到相应记录,已忽略'.format(stu_id))
continue
subject_dir_path = os.path.join(data_folder_path, dir_name) # 子目录
subject_images_names = os.listdir(subject_dir_path) # 获取所有图片名
one_person_all_features = []
for image_name in subject_images_names:
if image_name.startswith('.'): # 忽略隐藏文件
continue
image_path = os.path.join(subject_dir_path, image_name)
face_features = self.cal_128D_features(image_path) # 探测人脸,返回
if face_features == 0:
continue
one_person_all_features.append(face_features)
if one_person_all_features:
features_mean = np.array(one_person_all_features).mean(axis=0)
else:
features_mean = np.zeros(128, dtype=int, order='C')
face_id_list = np.full(128, face_id, dtype=int, order='C')
writer.writerow(face_id_list)
writer.writerow(features_mean)
face_id = face_id + 1
cursor.close()
conn.commit()
conn.close()
self.progress_bar_signal.emit(100)
# 检测人脸
def cal_128D_features(self, img_path):
img = cv2.imread(img_path) # 读取图片
faces = dlib_detector(img, 1) # 抠脸
if len(faces) == 0:
print("No Face!")
return 0
shape = predictor_68(img, faces[0]) # 提取脸部特征点
features = face_rec.compute_face_descriptor(img, shape) # 计算特征值
return features
class ExportExcelDialog(QDialog):
def __init__(self):
super(ExportExcelDialog, self).__init__()
loadUi('./ui/export_excel.ui', self)
self.setWindowIcon(QIcon('./icons/icon.png'))
# 只读,不允许修改
self.show_sqlTable.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.StuCheckTable.setEditTriggers(QAbstractItemView.NoEditTriggers)
# 表头自动伸缩×
self.show_sqlTable.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.StuCheckTable.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.select_table_pushButton.clicked.connect(self.select_table_show)
self.export_pushButton.clicked.connect(self.export_to_excel)
self.DelpushButton.clicked.connect(self.del_table)
self.fresh_table_list()
# 刷新数据库表格
def fresh_table_list(self):
conn, cursor = DataManageUI.connect_to_sql()
table_list = self.get_sql_table(cursor)
cursor.close()
conn.commit()
conn.close()
self.print_sql_tablelist(table_list)
# 获取数据库表名
@staticmethod
def get_sql_table(cursor):
sql = "show tables;"
cursor.execute(sql)
tables = [cursor.fetchall()]
table_list = re.findall('(\'.*?\')', str(tables))
table_list = [re.sub("'", '', each) for each in table_list]
return table_list
# 输出表名到表格中
def print_sql_tablelist(self, table_list):
while self.show_sqlTable.rowCount() > 0:
self.show_sqlTable.removeRow(0)
for row_index, row_data in enumerate(table_list):
self.show_sqlTable.insertRow(row_index) # 插入行
self.show_sqlTable.setItem(row_index, 0, QTableWidgetItem(str(row_data))) # 设置单元格文本
# 选择表格并预览内容
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 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 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()
if __name__ == '__main__':
logging.config.fileConfig('./config/logging.cfg')
app = QApplication(sys.argv)
window = DataManageUI()
window.show()
sys.exit(app.exec())