Python+PyQt如何实现数据库表格动态增删改

魁首哥

作者

Python+PyQt如何实现数据库表格动态增删改

小编给大家分享一下Python+PyQt如何实现数据库表格动态增删改，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

题目描述

本次实验为连接数据库的实验，并对数据库进行一些简单的操作，要实现的基本功能如下所示，要能连接并展现数据库里的数据，能够实现插入功能。

拓展;

实现按学号查找学生信息功能
实现清空数据功能
实现保存数据功能
实现右键菜单功能

解题思路/算法分析/问题及解决

本次实验可主要分为两个部分，即数据库连接操作部分和数据可视化操作界面部分。

数据库连接部分采用python的pymysql库对数据库进行连接操作。

数据可视化部分采用tableWidget控件进行表格化的呈现，并通过相应的控件交互来实现功能。TableWidget的主要方法如下表所示：

实验代码

数据库连接

defdb_connect(self):self.db=pymysql.connect(host='localhost',user='root',password='Zwq197166',port=3306,database='test')

可视化界面操作部分

definser_row(self,row,sid,name,sex,address):sid_item=QTableWidgetItem(sid)name_item=QTableWidgetItem(name)sex_item=QTableWidgetItem(sex)address_item=QTableWidgetItem(address)self.tableWidget.insertRow(row)self.tableWidget.setItem(row,0,sid_item)self.tableWidget.setItem(row,1,name_item)self.tableWidget.setItem(row,2,sex_item)self.tableWidget.setItem(row,3,address_item)@pyqtSlot()defon_button_load_clicked(self):ifself.button_save.isEnabled():r=QMessageBox.warning(self,"警告","是否覆盖当前表格数据",QMessageBox.Yes|QMessageBox.No,QMessageBox.Yes)ifr==QMessageBox.No:returnself.tableWidget.setRowCount(0)self.tableWidget.clearContents()self.db_connect()cursor=self.db.cursor()sql="select*frommy_student;"try:cursor.execute(sql)results=cursor.fetchall()for(sid,name,sex,address)inresults:print(sid,name,sex,address)row=self.tableWidget.rowCount()#print(row)self.inser_row(row,sid,name,sex,address)data[sid]=[name,sex,address]except:print("unabletofetchdata")self.db.close()self.button_save.setEnabled(True)print("load")@pyqtSlot()defon_button_add_clicked(self):di=inputDialog()ok=di.exec_()ifnotok:returnname=di.line_name.text()sid=di.line_id.text()sex=di.line_sex.text()address=di.line_address.text()print(name,sid)print(type(address))data[sid]=[name,sex,address]self.inser_row(self.tableWidget.rowCount(),sid,name,sex,address)print(data)print("add")#self.tableWidget.insertRow(self.tableWidget.rowCount()-1)self.button_save.setEnabled(True)@pyqtSlot()defon_button_save_clicked(self):print(data)self.db_connect()cursor=self.db.cursor()try:sql="deletefrommy_student;"cursor.execute(sql)#self.db.commit()forkey,valueindata.items():sql="insertintomy_student(sid,name,sex,address)values('{sid}','{name}','{sex}','{address}');".format(sid=key,name=value[0],sex=value[1],address=value[2])print(sql)cursor.execute(sql)self.db.commit()self.db.close()print("save")self.button_save.setEnabled(False)except:QMessageBox.critical(self,"错误","数据格式有误，请检查")@pyqtSlot()defon_button_clear_clicked(self):self.tableWidget.setRowCount(0)self.tableWidget.clearContents()data.clear()self.line_id.clear()self.button_save.setEnabled(True)@pyqtSlot()defon_button_search_clicked(self):sid=self.line_id.text()ifnotsid:QMessageBox.critical(self,"警告","请输入一个学号！")returnprint(sid)ifsidindata:search=INFO(sid)search.exec_()#print("search")else:QMessageBox.critical(self,"错误","该学号不存在！")@pyqtSlot(QTableWidgetItem)defon_tableWidget_itemActivated(self,item):"""按住Enter键时，当前选中的单元格向下"""row=self.tableWidget.row(item)column=self.tableWidget.column(item)totalrow=self.tableWidget.rowCount()ifrow+1<totalrow:row=self.tableWidget.row(item)+1self.tableWidget.setCurrentCell(row,column)elifrow+2==totalrow:row=totalrow-1self.tableWidget.setCurrentCell(row,column)@pyqtSlot(int,int)defon_tableWidget_cellDoubleClicked(self,row,column):id=self.tableWidget.item(row,0).text()di=inputDialog(sid=id)ok=di.exec_()ifnotok:returnname=di.line_name.text()sid=di.line_id.text()sex=di.line_sex.text()address=di.line_address.text()print("before:",id)print("after:",sid)self.tableWidget.item(row,0).setText(sid)self.tableWidget.item(row,1).setText(name)self.tableWidget.item(row,2).setText(sex)self.tableWidget.item(row,3).setText(address)data[sid]=[name,sex,address]ifid!=sid:deldata[id]self.button_save.setEnabled(True)defcloseEvent(self,event):ifself.button_save.isEnabled():r=QMessageBox.warning(self,"警告","你还有操作没保存，现在保存下？",QMessageBox.Yes|QMessageBox.No,QMessageBox.Yes)ifr==QMessageBox.No:event.accept()else:event.ignore()defcontext_menu(self,pos):pop_menu=QMenu()change_new_event=pop_menu.addAction("修改行")delete_event=pop_menu.addAction("删除行")action=pop_menu.exec_(self.tableWidget.mapToGlobal(pos))ifaction==change_new_event:item=self.tableWidget.selectedItems()row=item[0].row()id=self.tableWidget.item(row,0).text()di=inputDialog(sid=id)ok=di.exec_()ifnotok:returnname=di.line_name.text()sid=di.line_id.text()sex=di.line_sex.text()address=di.line_address.text()print("before:",id)print("after:",sid)self.tableWidget.item(row,0).setText(sid)self.tableWidget.item(row,1).setText(name)self.tableWidget.item(row,2).setText(sex)self.tableWidget.item(row,3).setText(address)data[sid]=[name,sex,address]ifid!=sid:deldata[id]self.button_save.setEnabled(True)elifaction==delete_event:r=QMessageBox.warning(self,"注意","删除可不能恢复了哦！",QMessageBox.Yes|QMessageBox.No,QMessageBox.No)ifr==QMessageBox.No:returnitems=self.tableWidget.selectedItems()ifitems:selected_rows=[]foriinitems:row=i.row()ifrownotinselected_rows:selected_rows.append(row)selected_rows=sorted(selected_rows,reverse=True)forrinselected_rows:sid=self.tableWidget.item(r,0).text()deldata[sid]self.tableWidget.removeRow(r)self.button_save.setEnabled(True)classinputDialog(QDialog,Ui_Dialog_input):def__init__(self,sid=None):super(inputDialog,self).__init__()self.setupUi(self)self.sid=sidself.buttonBox.accepted.connect(self.check)ifsid:self.line_id.setText(sid)self.line_name.setText(data[sid][0])self.line_sex.setText(data[sid][1])self.line_address.setText(data[sid][2])defcheck(self):sid=self.line_id.text()name=self.line_name.text()ifsidindataandself.sidnotindata:r=QMessageBox.warning(self,"警告","该学号已存在!",QMessageBox.Ok)returnifnotsid:r=QMessageBox.warning(self,"警告","学号为必填项!",QMessageBox.Ok)returnifnotname:r=QMessageBox.warning(self,"警告","姓名为必填项!",QMessageBox.Ok)returnself.accept()#print('miss')classINFO(QDialog,Ui_Dialog_info):def__init__(self,id:str):super(INFO,self).__init__()self.setupUi(self)self.line_id.setText(id)self.line_name.setText(data[id][0])self.line_sex.setText(data[id][1])self.line_address.setText(data[id][2])@pyqtSlot()defon_button_confirm_clicked(self):#print(1)self.close()