def __init__(self, db_path, parent=None):
super(Add_Table_UI, self).__init__(parent)
self.db_path = db_path
self.sf = Sqlite_Func()
h_layout = QHBoxLayout()
label_table_name = QLabel("表名字:")
self.lineEdit_table_name = QLineEdit()
# 水平放置
h_layout.addWidget(label_table_name)
h_layout.addWidget(self.lineEdit_table_name)
btn_ok = QPushButton()
btn_ok.setText("OK")
v_layout = QVBoxLayout()
# 垂直放置
v_layout.addLayout(h_layout)
v_layout.addWidget(btn_ok)
self.setLayout(v_layout)
btn_ok.clicked.connect(self.create_table)
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setupUi(self)
self.sf = Sqlite_Func()
self.opsql = Operate_Sql()
self.timer_camera_test = QtCore.QTimer() # qt计数器
self.timer_camera_face = QtCore.QTimer() # qt计数器
self.openSqlite = Sqlite_UI() # 数据库对象
self.slot_init()
self.combobox_face_init() # 初始化下拉列表:人脸数据表
self.combobox_checkWork_init() # 初始化下拉列表:考勤数据表
self.photoNum = 0 # 照片计数
self.CAM_NUM = 0
self.pNum = 0 # 照片计数器
self.photo_transmission = 0 # 图片传输变量
self.frame_out = 0
# 启动Facenet模块
self.face = face_recognition.face()
self.init_db()
def __init__(self, db_path, table, field, parent=None):
super(Add_Data_UI, self).__init__(parent)
self.db_path = db_path
self.table = table
self.field = field
self.sf = Sqlite_Func()
v_label_layout = QVBoxLayout()
v_layout = QVBoxLayout()
v_lineEdit_layout = QVBoxLayout()
h_layout = QHBoxLayout()
self.lineEdit_list = []
self.field_len = len(field)
db_type = str(db_path).split('/')
print("db type:", db_type[len(db_type) - 1].upper())
if db_type[len(db_type) - 1].upper() == self.sf.DB_TYPE_CHECKWORK:
self.field_len = len(field) - 1
for i in range(self.field_len):
label = QLabel()
label.setText("{}:".format(str(field[i]).upper()))
v_label_layout.addWidget(label)
lineEdit = QLineEdit()
self.lineEdit_list.append(lineEdit)
v_lineEdit_layout.addWidget(lineEdit)
if db_type[len(db_type) - 1].upper() == self.sf.DB_TYPE_CHECKWORK:
# 对于考勤表 flag不允许手动写入
label = QLabel()
label.setText("{}:".format(str(field[self.field_len]).upper()))
v_label_layout.addWidget(label)
lineEdit_flag = QLineEdit()
lineEdit_flag.setReadOnly(True)
lineEdit_flag.setText("0")
self.lineEdit_list.append(lineEdit_flag)
v_lineEdit_layout.addWidget(lineEdit_flag)
h_layout.addLayout(v_label_layout)
h_layout.addLayout(v_lineEdit_layout)
btn_ok = QPushButton()
btn_ok.setText("确定")
v_layout.addLayout(h_layout)
v_layout.addWidget(btn_ok)
self.setLayout(v_layout)
self.setupUi(self)
btn_ok.clicked.connect(self.insert_data)
def __init__(self, parent=None):
super(Sqlite_UI, self).__init__(parent)
self.setupUi(self)
# 隐藏两个组件：因为我不想写了
self.label_cmd.hide()
self.lineEdit_cmd.hide()
# slot init
self.slot_init()
self.sf = Sqlite_Func()
# 数据库路径
self.db_path = ""
# 选择的表名
self.table = ""
# 所有数据表
self.table_list = []
# 当前表所有字段
self.field_list = []
# 创建的按钮字段列表
self.btn_field_list = []
# 被勾选的字段
self.select_field_list = []
# 当前行列
self.len_row = 0
self.len_col = 0
self.model = QStandardItemModel()
# 数据库类型
self.db_type = ""
def computing_emb():
with tf.Graph().as_default():
with tf.Session() as sess:
# opsql = sqlite3_op.Operate_Sql()
sqlite = Sqlite_Func()
model = '../20170512-110547/'
emb_img = '../emb_img'
# 加载facenet模型
facenet.load_model(model)
images_placeholder = tf.get_default_graph().get_tensor_by_name("input:0")
embeddings = tf.get_default_graph().get_tensor_by_name("embeddings:0")
phase_train_placeholder = tf.get_default_graph().get_tensor_by_name("phase_train:0")
image = []
nrof_images = 0
global compare_emb, compare_num, all_obj_name
all_obj_name = []
for i in os.listdir(emb_img):
all_obj_name.append(i)
img = imageio.imread(os.path.join(emb_img, i))
prewhitened = facenet.prewhiten(img) # 预白化去除冗余信息
image.append(prewhitened)
nrof_images = nrof_images + 1
images = np.stack(image) # 沿着新轴连接数组的序列。
# 计算对比图片embadding，embdadding是一个128维的张量
compare_emb = sess.run(embeddings, feed_dict={images_placeholder: images, phase_train_placeholder: False})
compare_num = len(compare_emb)
print('compare_emb len:', len(compare_emb[0]))
print("pre_embadding计算完成")
for i in os.listdir(emb_img):
#拆分表名和id号
index = 0
info = i.split("#")
print("info:{}".format(info))
table_name=info[0]
id = info[1].split(".")
ID=id[0]
# opsql.insert_emb(info[0], id[0], compare_emb[index])
sqlite.update_face_emb(sqlite.DB_STUDENTFACE_PATH, table_name, ID, compare_emb[index])
index += 1
# 移除已经计算过的image
for f in os.listdir(emb_img):
pass
os.remove(os.path.join(emb_img, f))
class Add_Table_UI(QDialog, Ui_Dialog_Add_Table):
signal_status = pyqtSignal()
def __init__(self, db_path, parent=None):
super(Add_Table_UI, self).__init__(parent)
self.db_path = db_path
self.sf = Sqlite_Func()
h_layout = QHBoxLayout()
label_table_name = QLabel("表名字:")
self.lineEdit_table_name = QLineEdit()
# 水平放置
h_layout.addWidget(label_table_name)
h_layout.addWidget(self.lineEdit_table_name)
btn_ok = QPushButton()
btn_ok.setText("OK")
v_layout = QVBoxLayout()
# 垂直放置
v_layout.addLayout(h_layout)
v_layout.addWidget(btn_ok)
self.setLayout(v_layout)
btn_ok.clicked.connect(self.create_table)
def create_table(self):
sstr = self.db_path.split('/')
self.db_type = sstr[len(sstr) - 1]
print("db_type:", self.db_type)
print("创建新表")
if self.lineEdit_table_name == "":
print("表名字为空")
else:
self.sf.create_table(self.db_path, self.lineEdit_table_name.text(), str(self.db_type).upper())
status = 1
self.signal_status.emit()
self.close()
def test(self, s):
print("s:", s)
def __init__(self):
self.init_mtcnn()
self.train = False
self.sqlite = Sqlite_Func()
class face():
def __init__(self):
self.init_mtcnn()
self.train = False
self.sqlite = Sqlite_Func()
# 初始化MTCNN
def init_mtcnn(self):
print('初始化MTCNN')
with tf.Graph().as_default():
gpu_options = tf.GPUOptions(per_process_gpu_memory_fraction=1.0)
sess = tf.Session(config=tf.ConfigProto(
gpu_options=gpu_options, log_device_placement=False))
with sess.as_default():
global pnet, rnet, onet
pnet, rnet, onet = align.detect_face.create_mtcnn(sess, None)
# 提前计算pre_embadding
def init_pre_embdading(slef):
print('初始化 Facenet')
with tf.Graph().as_default():
with tf.Session() as sess:
model = '../20170512-110547/'
facenet.load_model(model)
images_placeholder = tf.get_default_graph().get_tensor_by_name(
"input:0")
embeddings = tf.get_default_graph().get_tensor_by_name(
"embeddings:0")
phase_train_placeholder = tf.get_default_graph(
).get_tensor_by_name("phase_train:0")
image = []
nrof_images = 0
global compare_emb, compare_num, all_obj_name
emb_dir = '../emb_img'
all_obj_name = []
for i in os.listdir(emb_dir):
all_obj_name.append(i)
img = imageio.imread(os.path.join(emb_dir, i))
print('img.shape:', img.shape)
prewhitened = facenet.prewhiten(img) # 预白化去除冗余信息
image.append(prewhitened)
nrof_images = nrof_images + 1
# global compare_emb, compare_num
images = np.stack(image) # 沿着新轴连接数组的序列。
feed_dict = {
images_placeholder: images,
phase_train_placeholder: False
}
# 计算对比图片embadding，embdadding是一个128维的张量
compare_emb = sess.run(embeddings, feed_dict=feed_dict)
print('compare_emb:', compare_emb)
print('compare_emb_shape:', compare_emb.shape)
compare_num = len(compare_emb)
print("pre_embadding计算完成")
return compare_emb, compare_num, all_obj_name # 数据库embadding，人数，目录标签
def main(self, face, checkwork):
with tf.Graph().as_default():
with tf.Session() as sess:
model = '../20170512-110547/'
facenet.load_model(model)
images_placeholder = tf.get_default_graph().get_tensor_by_name(
"input:0")
embeddings = tf.get_default_graph().get_tensor_by_name(
"embeddings:0")
phase_train_placeholder = tf.get_default_graph(
).get_tensor_by_name("phase_train:0")
'''
id:学号
compare_emb:特征值
compare_num:数据库中的数据条数
'''
# 找到当表主键
face_key_idx, face_key = self.sqlite.find_primary_key(
self.sqlite.DB_STUDENTFACE_PATH, face)
# cw_key_idx,cw_key=self.sqlite.find_primary_key(self.sqlite.DB_STUDENTCHECKWORK_PATH,checkwork)
# 从数据库获取人脸数据
cmd = self.sqlite.auto_select(face)
rows = self.sqlite.executeCMD(self.sqlite.DB_STUDENTFACE_PATH,
cmd)
id = []
num = len(rows)
emb_idx = len(rows[0]) - 1
compare_num = num
compare_emb = np.zeros([num, 128])
for lineIndex in range(num):
row = rows[lineIndex] # 获取某一行的数据,类型是tuple
id.append(row[face_key_idx]) # 获取id
emb_str = row[emb_idx] # 获取一个组数据中的emb数据
if emb_str is None:
compare_emb[lineIndex] = np.full((1, 128), 10)
else:
str_list = emb_str.split(' ') # 以空格分割字符串
if len(str_list) < 10:
continue
for i in range(128):
compare_emb[lineIndex][i] = float(
str_list[i]) # 'list转ndarray:'，str->float
capture = cv2.VideoCapture(0)
cv2.namedWindow("face recognition", 1)
while True:
ret, frame = capture.read()
frame = cv2.flip(frame, 1)
rgb_frame = cv2.cvtColor(frame, cv2.COLOR_BGR2RGB)
# 识别框
cv2.putText(frame,
'Identification Box', (200, 90),
cv2.FONT_HERSHEY_COMPLEX_SMALL,
1, (0, 0, 255),
thickness=2,
lineType=1)
cv2.rectangle(frame, (150, 100), (490, 380),
(165, 245, 25), 2)
BOX = [150, 100, 490, 380]
# 获取视频流中的最大人脸 判断标识 bounding_box crop_image
mark, bounding_box, crop_image = self.load_and_align_data(
rgb_frame, 160)
'''
范围限制
'''
if mark:
if bounding_box[0] < 75:
mark = False
# print('left')
if bounding_box[2] > 565:
mark = False
# print('right')
if mark:
# print('计算视频帧的embadding')
emb = sess.run(embeddings,
feed_dict={
images_placeholder: crop_image,
phase_train_placeholder: False
})
pre_person_num = len(emb)
find_obj = []
# print('识别到的人数:', pre_person_num)
cv2.putText(frame,
'Press esc to exit', (10, 30),
cv2.FONT_HERSHEY_COMPLEX_SMALL,
1, (0, 0, 255),
thickness=1,
lineType=1)
# 逐一对比
for i in range(pre_person_num): # 为bounding_box 匹配标签
dist_list = [] # 距离列表
for j in range(compare_num):
# 求误差(欧氏距离)
dist = np.sqrt(
np.sum(
np.square(
np.subtract(
emb[i, :],
compare_emb[j, :]))))
dist_list.append(dist)
# 求视频帧和对比图直接最小的差值，即表示为最相似的图片
min_value = min(dist_list)
# print("最小差值：", min_value)
if min_value > 0.65:
find_obj.append('Unknow')
else:
dist_index = dist_list.index(min_value)
find_obj.append(id[dist_index])
# 在frame上绘制边框和文字
cv2.rectangle(frame,
(bounding_box[0], bounding_box[1]),
(bounding_box[2], bounding_box[3]),
(0, 255, 0), 1, 8, 0)
cv2.putText(frame,
str(find_obj[0]),
(bounding_box[0], bounding_box[1]),
cv2.FONT_HERSHEY_COMPLEX_SMALL,
1, (0, 0, 255),
thickness=2,
lineType=2)
# 将学号插入到选择的考勤表中
if find_obj[0] != "Unknow":
self.sqlite.update_checkwork(
self.sqlite.DB_STUDENTCHECKWORK_PATH, checkwork,
find_obj[0])
cv2.imshow('face recognition', frame)
key = cv2.waitKey(3)
if key == 27:
break
capture.release()
cv2.destroyWindow("face recognition")
def load_and_align_data(self, img, image_size):
minsize = 20
threshold = [0.6, 0.7, 0.7]
factor = 0.709
bounding_boxes, _ = align.detect_face.detect_face(
img, minsize, pnet, rnet, onet, threshold, factor)
Index = [] # 序列
Area = [] # 面积
Position = [] # 坐标
# print('len(bounding_boxes):', len(bounding_boxes))
# 如果未发现目标 直接返回
if len(bounding_boxes) < 1:
# print('没有发现人脸')
return False, 0, 0
for i, face_position in enumerate(bounding_boxes):
face_position = face_position.astype(int)
w = face_position[2] - face_position[0]
h = face_position[3] - face_position[1]
S = w * h
# print('第:', i)
# print('w:', w)
# print('h:', h)
Index.append(i)
Area.append(S)
Position.append(face_position)
max_face_position = max_face(Area, Position)
# print('bbox:', (max_face_position[0], max_face_position[1]),
# (max_face_position[2], max_face_position[3]))
# 裁剪
temp_crop = img[max_face_position[1]:max_face_position[3],
max_face_position[0]:max_face_position[2], :]
if max_face_position[0] < 75: # 左边
return False, 0, 0
if max_face_position[2] > 565: # 右边
return False, 0, 0
if max_face_position[1] < 75: # 上面
return False, 0, 0
aligned = cv2.resize(temp_crop, (image_size, image_size),
interpolation=cv2.INTER_CUBIC)
face_out = facenet.prewhiten(aligned)
crop_image = []
crop_image.append(np.stack(face_out))
return True, max_face_position, crop_image # mark标记位置，回归边框，切割图片
class MainWindow(QMainWindow, Ui_Face_Recognition_window):
# 构造函数
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setupUi(self)
self.sf = Sqlite_Func()
self.opsql = Operate_Sql()
self.timer_camera_test = QtCore.QTimer() # qt计数器
self.timer_camera_face = QtCore.QTimer() # qt计数器
self.openSqlite = Sqlite_UI() # 数据库对象
self.slot_init()
self.combobox_face_init() # 初始化下拉列表:人脸数据表
self.combobox_checkWork_init() # 初始化下拉列表:考勤数据表
self.photoNum = 0 # 照片计数
self.CAM_NUM = 0
self.pNum = 0 # 照片计数器
self.photo_transmission = 0 # 图片传输变量
self.frame_out = 0
# 启动Facenet模块
self.face = face_recognition.face()
self.init_db()
def init_db(self):
if os.path.exists("../DB/StudentFaceDB.db") is False:
os.mkdir("../DB/StudentFaceDB.db")
if os.path.exists("../DB/StudentCheckWorkDB.db.db")is False:
os.mkdir("../DB/StudentCheckWorkDB.db.db")
# 槽初始化
def slot_init(self):
self.timer_camera_test.timeout.connect(self.show_frame)
self.btn_openCamera.clicked.connect(self.opencamera)
self.btn_takePhoto.clicked.connect(self.take_photo)
self.btn_refresh.clicked.connect(self.refresh)
self.btn_train.clicked.connect(self.train)
self.btn_recogniton.clicked.connect(self.open_recognition_camera)
self.btn_sqlite.clicked.connect(self.open_sqlite)
def open_sqlite(self):
self.openSqlite.show()
def opencamera(self):
if self.timer_camera_test.isActive() == False:
self.cap = cv2.VideoCapture(0)
flag = self.cap.open(self.CAM_NUM)
if flag is None:
msg = QtWidgets.QMessageBox.warning(self, u"Warning", u"摄像头无法打开!",
buttons=QtWidgets.QMessageBox.Ok,
defaultButton=QtWidgets.QMessageBox.Ok)
self.btn_openCamera.setText("关闭摄像头")
self.timer_camera_test.start(25)
else:
self.btn_openCamera.setText(u"打开摄像头")
self.timer_camera_test.stop()
self.lab_frame.setText(u"无图像输入")
self.cap.release()
def show_frame(self):
ret, frame = self.cap.read()
if frame is None:
return
frame = cv2.flip(frame, 1)
frame = cv2.resize(frame, (640, 480))
self.photo_transmission = frame
frame = cv2.cvtColor(frame, cv2.COLOR_BGR2RGB)
lable = cv2.putText(frame, '-->Camera OK', (10, 30), cv2.FONT_HERSHEY_COMPLEX_SMALL, 1, (0, 255, 0),
thickness=1, lineType=1)
showFrame = QtGui.QImage(frame.data, frame.shape[1], frame.shape[0], QtGui.QImage.Format_RGB888)
self.lab_frame.setPixmap(QtGui.QPixmap.fromImage(showFrame))
# 拍照
def take_photo(self):
'''
1、从数据库中读取所有文件名
2、从文件名中选择文件目录作为照片存储地址
3、调用拍照程序对当前画面进行拍照
4、更新拍照数量
'''
# 如果摄像头没有打开
if self.btn_openCamera.text() != '关闭摄像头':
msg = QtWidgets.QMessageBox.warning(self, u"Warning", u"请打开摄像头!",
buttons=QtWidgets.QMessageBox.Ok,
defaultButton=QtWidgets.QMessageBox.Ok)
else:
face = self.comboBox_face.currentText()
id = self.comboBox_id.currentText()
if id == '' or id is None:
msg = QtWidgets.QMessageBox.warning(self, u"Warning", u"请选择学号!",
buttons=QtWidgets.QMessageBox.Ok,
defaultButton=QtWidgets.QMessageBox.Ok)
else:
name = '{CLASS}#{id}'.format(CLASS=face, id=id)
name = '../src_img/{name}.jpg'.format(name=name)
print(name)
cv2.imwrite(name, self.photo_transmission)
# 考勤表初始化
def combobox_checkWork_init(self, checktable=None):
self.comboBox_checkWork.clear()
ret = self.sf.check_table(self.sf.DB_STUDENTCHECKWORK_PATH)
print("DB:{}\ntables:{}\ntable_nmu:{}".format(self.sf.DB_STUDENTCHECKWORK_PATH, ret, len(ret)))
readlines = len(ret)
lineindex = 0
while lineindex < readlines:
self.comboBox_checkWork.addItem(ret[lineindex])
lineindex += 1
if checktable != '' or checktable is None:
self.comboBox_checkWork.setCurrentText(checktable)
print('combobox_checkWork_init done\n')
# 获取人脸数据表
def combobox_face_init(self, checked_table=None):
self.comboBox_face.clear()
ret = self.sf.check_table(self.sf.DB_STUDENTFACE_PATH)
table_nmu = len(ret)
print("DB:{}\ntables:{}\ntable_nmu:{}".format(self.sf.DB_STUDENTFACE_PATH, ret, len(ret)))
readlines = table_nmu
lineindex = 0
while lineindex < readlines:
self.comboBox_face.addItem(ret[lineindex])
lineindex += 1
if checked_table != '' or checked_table is None:
self.comboBox_face.setCurrentText(checked_table)
print('combobox_face_init done\n')
# 初始化id列表
self.combobox_id_init(self.comboBox_face.currentText())
def combobox_id_init(self, table):
# 读取人脸数据库中所有id
field = []
field.append("id")
cmd = self.sf.auto_select(table,field)
print("cmd-->",cmd)
ret = self.sf.executeCMD(self.sf.DB_STUDENTFACE_PATH, cmd)
print("ret:", ret)
self.comboBox_id.clear()
print(len(ret))
for i in range(len(ret)):
self.comboBox_id.addItem(str(ret[i][0]))
# 刷新显示数据库并且显示id号
def refresh(self):
# 获取当前选中表明
checked_face = self.comboBox_face.currentText()
checked_cw = self.comboBox_checkWork.currentText()
print("当前选中：{},{}".format(checked_face, checked_cw))
self.combobox_face_init(checked_table=checked_face)
self.combobox_checkWork_init(checktable=checked_cw)
def train(self):
ret = QMessageBox.question(self, "Train", "训练过程中，画面无法更新,训练时间随机器性能决定", QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
get_face.detection()
# 打开识别摄像头
def open_recognition_camera(self):
if self.btn_openCamera.text() == '关闭摄像头':
msg = QtWidgets.QMessageBox.warning(self, u"警告", u"请先关闭摄像头!",
buttons=QtWidgets.QMessageBox.Ok,
defaultButton=QtWidgets.QMessageBox.Ok)
else:
ret = QMessageBox.question(self, "Train", "1、启动时间根据设备性能强弱决定\n\n2、程序启动后按下esc退出检测窗口",
QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
print('开启摄像头')
face = self.comboBox_face.currentText()
cw = self.comboBox_checkWork.currentText()
self.face.main(face, cw)
class Add_Data_UI(QDialog, Ui_Dialog_Add_Data):
signal_status = pyqtSignal()
def __init__(self, db_path, table, field, parent=None):
super(Add_Data_UI, self).__init__(parent)
self.db_path = db_path
self.table = table
self.field = field
self.sf = Sqlite_Func()
v_label_layout = QVBoxLayout()
v_layout = QVBoxLayout()
v_lineEdit_layout = QVBoxLayout()
h_layout = QHBoxLayout()
self.lineEdit_list = []
self.field_len = len(field)
db_type = str(db_path).split('/')
print("db type:", db_type[len(db_type) - 1].upper())
if db_type[len(db_type) - 1].upper() == self.sf.DB_TYPE_CHECKWORK:
self.field_len = len(field) - 1
for i in range(self.field_len):
label = QLabel()
label.setText("{}:".format(str(field[i]).upper()))
v_label_layout.addWidget(label)
lineEdit = QLineEdit()
self.lineEdit_list.append(lineEdit)
v_lineEdit_layout.addWidget(lineEdit)
if db_type[len(db_type) - 1].upper() == self.sf.DB_TYPE_CHECKWORK:
# 对于考勤表 flag不允许手动写入
label = QLabel()
label.setText("{}:".format(str(field[self.field_len]).upper()))
v_label_layout.addWidget(label)
lineEdit_flag = QLineEdit()
lineEdit_flag.setReadOnly(True)
lineEdit_flag.setText("0")
self.lineEdit_list.append(lineEdit_flag)
v_lineEdit_layout.addWidget(lineEdit_flag)
h_layout.addLayout(v_label_layout)
h_layout.addLayout(v_lineEdit_layout)
btn_ok = QPushButton()
btn_ok.setText("确定")
v_layout.addLayout(h_layout)
v_layout.addWidget(btn_ok)
self.setLayout(v_layout)
self.setupUi(self)
btn_ok.clicked.connect(self.insert_data)
def insert_data(self):
lineEdit_data = []
for i in range(len(self.lineEdit_list)):
lineEdit_data.append(self.lineEdit_list[i].text())
# 插入数据库
ret = self.sf.insert(self.db_path, self.table, lineEdit_data)
if ret == -1:
QMessageBox.about(self, "Error", "有数据为空")
if ret == -2:
QMessageBox.about(self, "Error", "主键重复")
self.signal_status.emit()
self.close()
class Sqlite_UI(QtWidgets.QMainWindow, Ui_SqliteMainWindow):
def __init__(self, parent=None):
super(Sqlite_UI, self).__init__(parent)
self.setupUi(self)
# 隐藏两个组件：因为我不想写了
self.label_cmd.hide()
self.lineEdit_cmd.hide()
# slot init
self.slot_init()
self.sf = Sqlite_Func()
# 数据库路径
self.db_path = ""
# 选择的表名
self.table = ""
# 所有数据表
self.table_list = []
# 当前表所有字段
self.field_list = []
# 创建的按钮字段列表
self.btn_field_list = []
# 被勾选的字段
self.select_field_list = []
# 当前行列
self.len_row = 0
self.len_col = 0
self.model = QStandardItemModel()
# 数据库类型
self.db_type = ""
def slot_init(self):
print("slot init...")
self.actionOpen_File.triggered.connect(self.open_db)
self.radioButton_all.clicked.connect(self.selectAll_radiobtn)
self.radioButton_notall.clicked.connect(self.selectNotAll_radiobtn)
self.pushButton_query.clicked.connect(self.query)
self.pushButton_update.clicked.connect(self.update_data)
self.pushButton_del.clicked.connect(self.delete_data)
self.pushButton_add.clicked.connect(self.add_data)
self.pushButton_delTable.clicked.connect(self.del_table)
self.pushButton_newTable.clicked.connect(self.add_table)
def open_db(self):
print("打开文件")
self.db_path, file_type = QFileDialog.getOpenFileName(self, "select db files", "",
"*.db;;*.png;;All Files(*)")
print("文件路径:{}\n文件类型:{}\n".format(self.db_path, file_type))
self.table_list = self.sf.check_table(self.db_path)
print("当前数据库含有表：", self.table_list)
# 设置窗口名字
QDialog.setWindowTitle(self, self.db_path)
sstr = self.db_path.split('/')
print(sstr)
self.groupBox_table_field.setTitle("数据库：{}".format(sstr[len(sstr) - 1]))
self.db_type = sstr[len(sstr) - 1]
self.create_radiobox_table()
# 创建数据库表选项
def create_radiobox_table(self):
self.count = 0
self.btn_layer = QWidget()
for i, data in enumerate(self.table_list):
self.count += 1
self.btn = QtWidgets.QRadioButton(self.btn_layer)
self.btn.setText(str(data))
self.btn.clicked.connect(partial(self.create_checkbox_field, self.btn.text(), False))
self.btn.move(10, i * 60)
self.btn_layer.setMinimumSize(250, self.count * 60)
self.scrollArea_table.setWidget(self.btn_layer)
self.scrollArea_table.setHorizontalScrollBarPolicy(Qt.ScrollBarAlwaysOff)
# 创建字段表
def create_checkbox_field(self, table, ischeck):
self.btn_field_list.clear()
self.count = 0
self.table = table
print("选择了{}表".format(str(table)))
ret = self.sf.check_field(self.db_path, table)
self.field_list = ret
print("当前表含有{}字段:".format(ret))
self.btn_layer = QWidget()
for i, data in enumerate(ret):
self.count += 1
self.btn = QtWidgets.QCheckBox(self.btn_layer)
self.btn.setText("{}".format(str(data)))
self.btn.setChecked(ischeck)
self.btn.move(10, i * 60)
self.btn_field_list.append(self.btn)
self.btn_layer.setMinimumSize(250, self.count * 60)
self.scrollArea_field.setWidget(self.btn_layer)
self.scrollArea_field.setHorizontalScrollBarPolicy(Qt.ScrollBarAlwaysOff)
def selectAll_radiobtn(self):
if self.btn_field_list == []:
print("没有选择表")
else:
print("全选字段")
self.create_checkbox_field(self.table, True)
self.select_field_list.clear()
for btn in self.btn_field_list:
self.select_field_list.append(btn.text())
def selectNotAll_radiobtn(self):
if self.btn_field_list == []:
print("没有选择表")
else:
print("全选字段")
self.create_checkbox_field(self.table, False)
self.select_field_list.clear()
# param:字段，内容
def show_table(self, fields, data):
print("显示表内容")
print("字段:{}\n数据:{}".format(fields, data))
# 行数
# 列数
self.len_row = len(data)
self.len_col = len(fields)
print("row:{},col:{}".format(self.len_row, self.len_col))
# self.model = QStandardItemModel(self.len_row, self.len_col, self)
self.model.setRowCount(self.len_row)
self.model.setColumnCount(self.len_col)
for row in range(self.len_row):
for col in range(len(data[0])):
item = QStandardItem("{}".format(data[row][col]))
self.model.setItem(row, col, item)
self.tableView_content.setModel(self.model)
self.tableView_content.horizontalHeader().setStretchLastSection(True)
self.tableView_content.setEditTriggers(QAbstractItemView.DoubleClicked)
self.tableView_content.clicked.connect(lambda x: print(self.tableView_content.currentIndex().data()))
# 通过按键更新数据到数据库
def update_data(self):
print(self.btn_field_list)
if self.btn_field_list == []:
print("没有选择表")
else:
update_data = []
if self.len_row == 0:
print("没有数据")
return
# 读取新数据
for i in range(self.len_row):
update_data_item = []
for j in range(self.len_col):
if self.model.item(i, j).text() != None:
update_data_item.append(self.model.item(i, j).text())
update_data.append(update_data_item)
print("update data", update_data)
if len(update_data[0]) != len(self.field_list):
print("数据不足")
return
# 查找主键
i, key = self.sf.find_primary_key(self.db_path, self.table)
# 更新数据
ret = self.sf.update(self.db_path, self.table, self.field_list, update_data, i)
if ret == -1:
QMessageBox.about(self, "Error", "有数据为空")
if ret==-2:
print("更新异常")
if ret == -3:
print("主键被修改，停止更新")
QMessageBox.about(self,"Error","主键被修改")
else:
print("更新完成\n")
# 查询
def query(self):
self.select_field_list.clear()
for btn in self.btn_field_list:
print(btn.isChecked())
if btn.isChecked() == True:
self.select_field_list.append(btn.text())
print("self.select_field_list:", self.select_field_list)
if self.select_field_list != []:
# 根据所选字段和表构建查询语句：select field1,field2,...,fieldN from table
str_sql = self.sf.auto_select(self.table, self.select_field_list)
ret = self.sf.executeCMD(self.db_path, str_sql)
print(ret)
for i, data in enumerate(ret):
print("{}->{}\n".format(i, data))
# 打印数据
self.show_table(self.select_field_list, ret)
if self.select_field_list == []:
self.tableView_content.setModel(self.model.clear())
# 进行删除操作需要选中所有字段
def delete_data(self):
if self.table == "":
print("未选择表")
return
if self.select_field_list == []:
print("未选择字段")
return
if self.tableView_content.currentIndex().row() == -1:
print("未选择数据")
return
else:
del_row = self.tableView_content.currentIndex().row()
print("选择{}行".format(del_row))
# 主键index和获取主键
key_idx, key = self.sf.find_primary_key(self.db_path, self.table)
del_data = []
# 读取当前行所有数据
for i in range(self.len_col):
if self.model.item(del_row, i).text() != None:
del_data.append(self.model.item(del_row, i).text())
if len(del_data) != len(self.field_list):
print("数据不全")
return
print("del data:", del_data)
self.sf.delete(self.db_path, self.table, key, key_idx, del_data)
self.query()
def add_data(self):
if self.db_path == "":
print("未选择数据库")
return
if self.table == "":
print("未选择表")
return
if self.select_field_list == []:
print("未选择字段")
return
print("添加数据")
self.add_data_ui = Add_Data_UI(self.db_path, self.table, self.field_list)
self.add_data_ui.show()
self.add_data_ui.signal_status.connect(self.query)
# self.query()
def flash_table(self):
# 刷新数据库表
self.table_list = self.sf.check_table(self.db_path)
print("当前数据库含有表：", self.table_list)
self.create_radiobox_table()
def add_table(self):
if self.db_path == "":
print("没有选择数据库")
else:
self.add_table_ui = Add_Table_UI(self.db_path)
self.add_table_ui.show()
# 子窗口链接主窗口函数
self.add_table_ui.signal_status.connect(self.flash_table)
def del_table(self):
if self.table == "":
print("没有选择表")
return
print("当前选择表:{}".format(self.table))
ret = QMessageBox.question(self, "delete table", "sure to delete this table?", QMessageBox.Yes | QMessageBox.No,
QMessageBox.No)
if ret == QMessageBox.Yes:
self.sf.delete_table(self.db_path, self.table)
self.tableView_content.setModel(self.model.clear())
# 刷新数据库表
self.table_list = self.sf.check_table(self.db_path)
print("当前数据库含有表：", self.table_list)
self.create_radiobox_table()