class DatabaseQueryView(QTableView): def __init__(self, db, query, parent=None): super().__init__(parent) self.db = db self.query = query self._init_context_menu() self._init_model() def _init_context_menu(self): self.setContextMenuPolicy(Qt.CustomContextMenu) self.customContextMenuRequested.connect(self._on_context_menu) def _init_model(self): self.model = QSqlQueryModel() self.model.setQuery(QSqlQuery(self.query, self.db)) self.setModel(self.model) def set_query(self, query): self.query = query self._init_model() def set_header_labels(self, labels): for idx, label in enumerate(labels): self.model.setHeaderData(idx + 1, Qt.Horizontal, label) def _on_context_menu(self, pos): _context_menu = QMenu(self) _refresh_action = QAction("刷新") _context_menu.addAction(_refresh_action) action = _context_menu.exec_(self.viewport().mapToGlobal(pos)) if action == _refresh_action: self.set_query(self.query)
def attractionView(self): model = QSqlQueryModel() model.setQuery('SELECT id, name FROM t_attraction') model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Attraction_List.setModel(model) pass
def _createModel(): oi_get_query = QSqlQuery() oi_get_query.prepare("""select symbol ,desc ,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2) ,globex_volume ,volume ,open_interest ,change from oi_reports where symbol = "EUR" order by cast(oi_date as int) desc ; """) #oi_get_query.addBindValue(symbol) oi_get_query.exec() queryModel = QSqlQueryModel() queryModel.setQuery(oi_get_query) headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change'] for columnIndex, header in enumerate(headers): queryModel.setHeaderData(columnIndex, Qt.Horizontal, header) #queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol') #queryModel.setHeaderData(1, Qt.Horizontal, 'Description') #queryModel.setHeaderData(2, Qt.Horizontal, 'Date') #queryModel.setHeaderData(3, Qt.Horizontal, 'Globex') #queryModel.setHeaderData(4, Qt.Horizontal, 'Volume') #queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest') #queryModel.setHeaderData(6, Qt.Horizontal, 'Change') return queryModel
def competitorView(self): model = QSqlQueryModel() model.setQuery('SELECT id, name FROM t_competitor') model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Competitor_List.setModel(model) pass
def btn3f(self): conn = sqlite3.connect('company.db') curs = conn.cursor() curs.execute("select name from sqlite_master where type='table'") tabs = QTabWidget() table_name = 'stock_price' table_model = QSqlQueryModel() for i, column_data in enumerate(curs.description): table_model.setHeaderData(i, Qt.Horizontal, column_data[0]) table_model.setQuery(f"select * from {table_name}") table_view = QTableView() table_view.setModel(table_model) tabs.addTab(table_view, table_name) screen = QWidget() content = QVBoxLayout() line1 = QHBoxLayout() line1.addWidget(tabs) content.addLayout(line1) self.k = popup3() self.k.setGeometry(800, 350, 400, 200) self.k.setWindowTitle("Stock Prices") self.k.setLayout(content) self.k.show()
class TabTableNonEditable(InvertedTable): """an inverted table presenting a QSqlQueryModel """ def __init__(self, log, db, tab_nr, query, headers = None, myfilter = ""): super().__init__(log, db) self.nr = tab_nr self.filter = myfilter self.headers = headers self.query = query self.create_model() self.invert_model() self.add_headers() def create_model(self): """creates the table model """ q = QSqlQuery(self.query + " " + self.filter) self.model = QSqlQueryModel() q.exec_(self.query) self.model.setQuery(q) def add_headers(self): """adds headers """ if self.headers: for i in self.headers: column = self.headers[i] self.model.setHeaderData(i, Qt.Horizontal, column, Qt.DisplayRole) def refresh(self, myfilter = ""): """refreshes the displayed data after data changes in the model """ if myfilter: self.filter = myfilter self.model.setQuery(self.query + " where " + self.filter)
class GeneralResultModel(): def __init__(self, query, label): self.model = QSqlQueryModel() self.query = query self.model.setQuery(self.query) self.model.setHeaderData(0, Qt.Horizontal, label) def select(self): self.model.setQuery(self.query)
def songProperties(self): model = QSqlQueryModel() model.setQuery('SELECT t_artist.name FROM fk_song_artist ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE fk_song_artist.id_song = ' + str(self.tableView_Song_List.currentIndex().sibling( self.tableView_Song_List.currentIndex().row(), 0).data())) model.setHeaderData(0, Qt.Horizontal, "Artist Name") self.tableView_Song_Properties.setModel(model) pass
def getList(self, table, header, where='1=1', page=1, pagesize=100): model = QSqlQueryModel() # model.setTable(table) model.setQuery( 'select * from %s where %s limit %s , %s' % (table, where, (page - 1) * pagesize, pagesize), self.db) i = 0 for h in header: model.setHeaderData(i, Qt.Horizontal, h) i += 1 return model
def get_model(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('sports.db') self.db.open() model = QSqlQueryModel() query_str = """ select name, count(name) as counts from domains group by name order by counts desc """ model.setQuery(query_str, db=self.db) model.setHeaderData(0, Qt.Horizontal, "Word") model.setHeaderData(1, Qt.Horizontal, "Count") return model
def asc(ui,sqlCommand): q = QSqlQuery() q.prepare(sqlCommand) # q.prepare('insert into buff1 (id,name) value(1,"shit")') print("执行一次asc():sql语句为:"+sqlCommand) q.exec() model = QSqlQueryModel() model.setQuery(q) #设置表头 modelDic = ['id','物品名','挂刀比例','倒货收益率','倒货收益','steam底价', 'buff底价','buff在售数','buff求购数','buff求购价','磨损','品类','物品种类','steam商城链接','上次更新时间'] for i in range(0,14): model.setHeaderData(i, Qt.Orientation(1),modelDic[i]) ui.tableView.setModel(model)
def songView(self): model = QSqlQueryModel() model.setQuery('SELECT t_song.id, t_song.name, t_song.file, t_song_type.name, t_anime.name, t_anime_serie.name ' 'FROM t_song ' 'INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id') model.setHeaderData(0, Qt.Horizontal, "Song ID") model.setHeaderData(1, Qt.Horizontal, "Song Name") model.setHeaderData(2, Qt.Horizontal, "Song File") model.setHeaderData(3, Qt.Horizontal, "Song Type") model.setHeaderData(4, Qt.Horizontal, "Anime Name") model.setHeaderData(5, Qt.Horizontal, "Anime Serie") self.tableView_Song_List.setModel(model) pass
class HospitalData(QWidget): def __init__(self, parent=None): super(HospitalData, self).__init__(parent) # Declare Database Connections self.db = None # Layout Manager self.layout = QVBoxLayout() # Query Model self.queryModel = QSqlQueryModel() # Table View self.tableView = QTableView() self.tableView.setModel(self.queryModel) self.initUI() self.initializedModel() def initUI(self): self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) self.layout.addWidget(self.tableView) self.setLayout(self.layout) self.setWindowTitle("HospitalData") self.resize(1300, 600) def initializedModel(self): self.db = QSqlDatabase.addDatabase("QSQLITE") self.db.setDatabaseName("project.db") if not self.db.open(): return False self.queryModel.setHeaderData(0, Qt.Horizontal, "Hospital Name") self.queryModel.setHeaderData(1, Qt.Horizontal, "Address") self.queryModel.setHeaderData(3, Qt.Horizontal, "City") self.queryModel.setHeaderData(3, Qt.Horizontal, "State") self.queryModel.setHeaderData(3, Qt.Horizontal, "Contact Details") # Get all the records of the table sql = "SELECT * FROM hospital" self.queryModel.setQuery(sql, self.db) self.totalRecordCount = self.queryModel.rowCount() def closeEvent(self, event): self.db.close()
def reloadGameCompetitors(self, id_attraction): model_Game_Competitors = QSqlQueryModel() model_Game_Competitors.setQuery('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ON ' 't_point.id_competitor = t_competitor.id ' 'WHERE t_point.id_competitor_take = 1 ' 'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL ' 'SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ' 'ON t_point.id_competitor_take = t_competitor.id ' 'WHERE t_point.id_competitor_take != 1 ' 'AND t_point.id_attraction = ' + id_attraction + ') ' 'GROUP BY name ORDER BY point DESC') model_Game_Competitors.setHeaderData(0, Qt.Horizontal, "Points") model_Game_Competitors.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Game_Competitors.setModel(model_Game_Competitors) pass
def selectComponentButtonEvent(self): """ hsj 搜索产品的相关组件 :return: """ # 判断复选框是否只选中一个 a = self.isCorrect() if a == 0: return select_num = self.queryModel.selectNum() db = openDB() queryModel = QSqlQueryModel() query = QSqlQuery() sql = "SELECT * FROM T_ProductComponent_New WHERE ProductID = '%s'" % ( self.queryModel.data_list[select_num][0]) # print(sql) query.exec(sql) if not query.next(): QMessageBox.information(QDialog(), "提示", "该产品无其他组件!", QMessageBox.Yes, QMessageBox.Yes) return queryModel.setQuery( "SELECT * FROM T_ProductComponent_New WHERE ProductID = '%s'" % (self.queryModel.data_list[select_num][0])) headerRow = [ "组件ID", "产品ID", "组件代号", "组件名称", "父节点ID", "组件类型", "组件数量", "寿命类型", "寿命", "据到期提醒", "最初维保时间", "维保间隔", "距维保提醒" ] for i in range(len(headerRow)): queryModel.setHeaderData(i, Qt.Horizontal, headerRow[i]) form = QDialog() tableView = QTableView() tableView.setModel(queryModel) tableView.show() tableView.horizontalHeader().setStretchLastSection(True) layout = QVBoxLayout() layout.addWidget(tableView) form.setLayout(layout) form.showMaximized() form.exec()
class Ui_MainWindow(object): def setupUi(self, MainWindow): MainWindow.setObjectName("MainWindow") MainWindow.resize(800, 600) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget) self.verticalLayout.setObjectName("verticalLayout") self.tv = tableView = QtWidgets.QTableView(self.centralwidget) self.tv.setObjectName("tableView") self.verticalLayout.addWidget(self.tv) MainWindow.setCentralWidget(self.centralwidget) self.model = None db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("test.db") if db.open(): print("open DB success.") query = QSqlQuery() query.prepare("insert into user ('nom','prenom' )values ('Bauer', 'Jack')") # query.exec_("create table person(id int primary key, name varchar(20), address varchar(30))") query.exec() # query.exec_("insert into user values('Jack', 'shanghai')") # query.exec_("insert into user values('Alex', 'chengdu')") # query.prepare("SELECT * FROM user") # query.exec() self.model = QSqlQueryModel() self.model.setQuery("SELECT * FROM user LIMIT 10,10") self.model.setHeaderData(0,QtCore.Qt.Horizontal,"Nom") self.model.setHeaderData(1,QtCore.Qt.Horizontal,"Prénom") self.model.setHeaderData(2,QtCore.Qt.Horizontal,"ID") # self.tv.setHorizontalHeaderLabels(QString("Nom;Prénom;ID").split(";")) # self.model.setHeaderData(1, Qt.Horizontal, "Prénom") self.tv.setModel(self.model) self.model.setQuery("SELECT * FROM user LIMIT 5") # self.tv.setModel(self.model) db.close() self.retranslateUi(MainWindow) QtCore.QMetaObject.connectSlotsByName(MainWindow) def retranslateUi(self, MainWindow): _translate = QtCore.QCoreApplication.translate MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
def gameProperties(self): model = QSqlQueryModel() model.setQuery('SELECT t_song.name, t_song_type.name, t_anime.name, t_anime_serie.name, t_artist.name ' 'FROM t_song INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id ' 'INNER JOIN fk_song_artist ON t_song.id = fk_song_artist.id_song ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE t_song.id = ' + str(self.tableView_Game_List.currentIndex().sibling( self.tableView_Game_List.currentIndex().row(), 3).data())) model.setHeaderData(0, Qt.Horizontal, "Song Name") model.setHeaderData(1, Qt.Horizontal, "Song Type") model.setHeaderData(2, Qt.Horizontal, "Anime Name") model.setHeaderData(3, Qt.Horizontal, "Anime Serie") model.setHeaderData(4, Qt.Horizontal, "Artist Name") self.tableView_Game_Properties.setModel(model) pass
class MainWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.setGeometry(100, 100, 400, 400) self.setWindowTitle("Database GUI App") self.createDbConnection() self.createEmployeeTable() # self.addTestData() self.createCentralWidget() def createDbConnection(self): self.con = QSqlDatabase.addDatabase("QSQLITE") self.con.setDatabaseName("test2.sqlite") if not self.con.open(): QMessageBox.critical(None, "Database Error!", f"{con.lastError().databaseText()}") sys.exit() def checkIfTableExists(self, tableName): tables = self.con.tables() return tableName in tables def createEmployeeTable(self): if not self.checkIfTableExists("employee"): queryString = """ CREATE TABLE employee ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name VARCHAR(40) NOT NULL, job VARCHAR(50), email VARCHAR(40) NOT NULL, phone VARCHAR(20), age INTEGER ) """ query = QSqlQuery() query.exec(queryString) def addTestData(self): data = [ ("Harrison", "Senior Back-End Developer", "*****@*****.**", "0799204524", 22), ("Joe", "Senior Front-End Developer", "*****@*****.**", "0799001654", 21), ("Lara", "Project Manager", "*****@*****.**", "0710204524", 25), ("David", "Data Analyst", "*****@*****.**", "0799204500", 24), ("Jane", "Senior Python Developer", "*****@*****.**", "0799200520", 23), ] query = QSqlQuery() query.prepare(""" INSERT INTO employee ( name, job, email, phone, age ) VALUES (?, ?, ?, ?, ?) """) for name, job, email, phone, age in data: query.addBindValue(name) query.addBindValue(job) query.addBindValue(email) query.addBindValue(phone) query.addBindValue(age) query.exec() query.finish() def createCentralWidget(self): self.model = QSqlQueryModel(self) self.model.setQuery( "SELECT id, name, job, email,phone,age FROM employee") self.model.setHeaderData(0, Qt.Horizontal, "ID") self.model.setHeaderData(1, Qt.Horizontal, "Name") self.model.setHeaderData(2, Qt.Horizontal, "Job") self.model.setHeaderData(3, Qt.Horizontal, "Email") self.model.setHeaderData(4, Qt.Horizontal, "Phone") self.model.setHeaderData(5, Qt.Horizontal, "Age") self.view = QTableView() self.view.setModel(self.model) # self.view.setColumnCount(6) # self.view.setHorizontalHeaderLabels(["ID", "Name", "Job", "Email","Phone","Age"]) # employeesData = self.fetchEmployees() # while employeesData.next(): # rows = self.view.rowCount() # self.view.setRowCount(rows+1) # self.view.setItem(rows,0,QTableWidgetItem(str(employeesData.value(0)))) # self.view.setItem(rows,1,QTableWidgetItem(employeesData.value(1))) # self.view.setItem(rows,2,QTableWidgetItem(employeesData.value(2))) # self.view.setItem(rows,3,QTableWidgetItem(employeesData.value(3))) # self.view.setItem(rows,4,QTableWidgetItem(employeesData.value(4))) # self.view.setItem(rows,5,QTableWidgetItem(str(employeesData.value(5)))) self.view.resizeColumnsToContents() self.setCentralWidget(self.view) def fetchEmployees(self): self.employeesQuery = QSqlQuery() self.employeesQuery.exec( "SELECT id, name, job, email,phone,age FROM employee") return self.employeesQuery
class LogDialog(QDialog): """ 日志弹窗类 """ def __init__(self, parent=None): super(LogDialog, self).__init__(parent) self.setWindowTitle("打卡日志") self.setWindowModality(Qt.ApplicationModal) # 隐藏父窗口 self.setFixedSize(600, 480) self.table = None self.button_export = None self.model = None self.file = None self.load_data() self.log_dialog() def log_dialog(self): """ 日志弹窗 :return: """ self.table = QTableView(self) self.table.resize(600, 400) self.table.setModel(self.model) self.table.setEditTriggers(QTableView.NoEditTriggers) # 设置表单不可编辑 self.table.setSelectionMode(QTableView.NoSelection) # 设置表单不可选中 self.table.resizeColumnsToContents() # 列根据内容调整大小 self.table.resizeRowsToContents() # 行根据内容调整大小 self.table.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) # 表单自适应 self.button_export = QPushButton("导出日志", self) self.button_export.clicked.connect(self.export_xls) self.button_export.move(220, 415) def load_data(self): """ 使用自带的QSqlQueryModel方法进行数据库查询 :return: """ db = QSqlDatabase.addDatabase("QSQLITE") # 选着数据库类型 db.setDatabaseName("./sys_db.db") db.open() self.model = QSqlQueryModel() self.model.setQuery( """select tb1.id,tb1.sname,tb2.clcokdate,tb2.clocktime,tb2.latetime from staff_tb as tb1 join logcat_tb as tb2 on tb1.id = tb2.id""") self.model.setHeaderData(0, Qt.Horizontal, "ID") self.model.setHeaderData(1, Qt.Horizontal, "姓名") self.model.setHeaderData(2, Qt.Horizontal, "打卡日期") self.model.setHeaderData(3, Qt.Horizontal, "打卡时间") self.model.setHeaderData(4, Qt.Horizontal, "迟到时长") def export_xls(self): self.file = xlwt.Workbook(encoding="utf-8") log = load_logcat() sheet = self.file.add_sheet(u"日志") row0 = [u"ID", u"姓名", u"打卡日期", u"打卡时间", u"迟到时长"] for i in range(len(row0)): sheet.write(0, i, row0[i]) for i in range(len(log)): for j in range(len(log[i])): print(log[i][j]) sheet.write(i + 1, j, log[i][j]) cu_time = time.strftime(u'%Y-%m-%d', time.localtime(time.time())) self.file.save("./" + cu_time + "日志.xls")
class VentanaDetalleRegistro(QMainWindow): def __init__(self, id, parent=None): super(VentanaDetalleRegistro, self).__init__(parent) loadUi("ventanas\DetalleVenta.ui", self) self.id = id delegateFloat = InitialDelegate(2, self.tableView) self.tableView.setItemDelegateForColumn(4, delegateFloat) self.tableView.setItemDelegateForColumn(5, delegateFloat) self.modelo = QSqlQueryModel() self.tableView.setModel(self.modelo) self.tableView.setDragDropOverwriteMode(False) self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.tableView.setTextElideMode(Qt.ElideRight) self.tableView.setWordWrap(False) self.tableView.setSortingEnabled(False) self.tableView.horizontalHeader().setDefaultAlignment(Qt.AlignHCenter | Qt.AlignVCenter | Qt.AlignCenter) self.tableView.horizontalHeader().setHighlightSections(False) self.tableView.verticalHeader().setSectionResizeMode( QHeaderView.ResizeToContents) self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.verticalHeader().setVisible(False) self.tableView.setAlternatingRowColors(True) self.tableView.horizontalHeader().setFont( QFont("Franklin Gothic Book", 9, QFont.Bold)) self.query = QSqlQuery(db=dba) self.query.prepare( "SELECT idConcepto, Refaccion.nombre, Concepto.cantidad, Refaccion.uniMedida, Refaccion.precio, importe FROM Concepto " "INNER JOIN Refaccion ON Refaccion.idRefaccion = Concepto.refaccion " "INNER JOIN Venta ON Venta.numVenta = Concepto.numVenta " "WHERE Venta.numVenta = :numVenta") self.actualizarQuery() self.modelo.setHeaderData(0, Qt.Horizontal, "Id concepto") self.modelo.setHeaderData(1, Qt.Horizontal, "Nombre") self.modelo.setHeaderData(2, Qt.Horizontal, "Cantidad") self.modelo.setHeaderData(3, Qt.Horizontal, "Unidad de\nmedida") self.modelo.setHeaderData(4, Qt.Horizontal, "Precio unitario") self.modelo.setHeaderData(5, Qt.Horizontal, "Importe") def actualizarQuery(self): numVenta = self.id self.query.bindValue(":numVenta", numVenta) self.query.exec_() self.modelo.setQuery(self.query)
class DataGrid(QWidget): def __init__(self): super().__init__() self.setWindowTitle("分页查询例子") self.resize(750,300) # 查询模型 self.queryModel = None # 数据表 self.tableView = None # 总数页文本 self.totalPageLabel = None # 当前页文本 self.currentPageLabel = None # 转到页输入框 self.switchPageLineEdit = None # 前一页按钮 self.prevButton = None # 后一页按钮 self.nextButton = None # 转到页按钮 self.switchPageButton = None # 当前页 self.currentPage = 0 # 总页数 self.totalPage = 0 # 总记录数 self.totalRecrodCount = 0 # 每页显示记录数 self.PageRecordCount = 5 self.db = None self.initUI() def initUI(self): # 创建窗口 self.createWindow() # 设置表格 self.setTableView() # 信号槽连接 self.prevButton.clicked.connect(self.onPrevButtonClick ) self.nextButton.clicked.connect(self.onNextButtonClick ) self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick ) def closeEvent(self, event): # 关闭数据库 self.db.close() # 创建窗口 def createWindow(self): # 操作布局 operatorLayout = QHBoxLayout() self.prevButton = QPushButton("前一页") self.nextButton = QPushButton("后一页") self.switchPageButton = QPushButton("Go") self.switchPageLineEdit = QLineEdit() self.switchPageLineEdit.setText(str(self.currentPage)) self.switchPageLineEdit.setFixedWidth(40) switchPage = QLabel("转到第") page = QLabel("页") operatorLayout.addWidget(self.prevButton) operatorLayout.addWidget(self.nextButton) operatorLayout.addWidget(switchPage) operatorLayout.addWidget(self.switchPageLineEdit) operatorLayout.addWidget(page) operatorLayout.addWidget(self.switchPageButton) operatorLayout.addWidget( QSplitter()) # 状态布局 statusLayout = QHBoxLayout() self.totalPageLabel = QLabel() self.totalPageLabel.setFixedWidth(70) self.currentPageLabel = QLabel() self.currentPageLabel.setFixedWidth(70) self.totalRecordLabel = QLabel() self.totalRecordLabel.setFixedWidth(70) statusLayout.addWidget(self.totalPageLabel) statusLayout.addWidget(self.currentPageLabel) statusLayout.addWidget( QSplitter() ) statusLayout.addWidget(self.totalRecordLabel) # 设置表格属性 self.tableView = QTableView() # 表格宽度的自适应调整 self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) # 创建界面 mainLayout = QVBoxLayout(self); mainLayout.addLayout(operatorLayout); mainLayout.addWidget(self.tableView); mainLayout.addLayout(statusLayout); self.setLayout(mainLayout) # 设置表格 def setTableView(self): print('*** step2 SetTableView' ) self.db = QSqlDatabase.addDatabase('QSQLITE') # 设置数据库名称 self.db.setDatabaseName('./db/database.db') # 打开数据库 self.db.open() # 声明查询模型 self.queryModel = QSqlQueryModel(self) # 设置当前页 self.currentPage = 1; # 得到总记录数 self.totalRecrodCount = self.getTotalRecordCount() # 得到总页数 self.totalPage = self.getPageCount() # 刷新状态 self.updateStatus() # 设置总页数文本 self.setTotalPageLabel() # 设置总记录数 self.setTotalRecordLabel() # 记录查询 self.recordQuery(0) # 设置模型 self.tableView.setModel(self.queryModel) print('totalRecrodCount=' + str(self.totalRecrodCount) ) print('totalPage=' + str(self.totalPage) ) # 设置表格表头 self.queryModel.setHeaderData(0,Qt.Horizontal,"编号") self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名") self.queryModel.setHeaderData(2,Qt.Horizontal,"性别") self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄") self.queryModel.setHeaderData(4,Qt.Horizontal,"院系") # 得到记录数 def getTotalRecordCount(self): self.queryModel.setQuery("select * from student") rowCount = self.queryModel.rowCount() print('rowCount=' + str(rowCount) ) return rowCount # 得到页数 def getPageCount(self): if self.totalRecrodCount % self.PageRecordCount == 0 : return (self.totalRecrodCount / self.PageRecordCount ) else : return (self.totalRecrodCount / self.PageRecordCount + 1) # 记录查询 def recordQuery(self, limitIndex ): szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) ) print('query sql=' + szQuery ) self.queryModel.setQuery(szQuery) # 刷新状态 def updateStatus(self): szCurrentText = ("当前第%d页" % self.currentPage ) self.currentPageLabel.setText( szCurrentText ) #设置按钮是否可用 if self.currentPage == 1 : self.prevButton.setEnabled( False ) self.nextButton.setEnabled( True ) elif self.currentPage == self.totalPage : self.prevButton.setEnabled( True ) self.nextButton.setEnabled( False ) else : self.prevButton.setEnabled( True ) self.nextButton.setEnabled( True ) # 设置总数页文本 def setTotalPageLabel(self): szPageCountText = ("总共%d页" % self.totalPage ) self.totalPageLabel.setText(szPageCountText) # 设置总记录数 def setTotalRecordLabel(self): szTotalRecordText = ("共%d条" % self.totalRecrodCount ) print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText ) self.totalRecordLabel.setText(szTotalRecordText) # 前一页按钮按下 def onPrevButtonClick(self): print('*** onPrevButtonClick '); limitIndex = (self.currentPage - 2) * self.PageRecordCount self.recordQuery( limitIndex) self.currentPage -= 1 self.updateStatus() # 后一页按钮按下 def onNextButtonClick(self): print('*** onNextButtonClick '); limitIndex = self.currentPage * self.PageRecordCount self.recordQuery( limitIndex) self.currentPage += 1 self.updateStatus() # 转到页按钮按下 def onSwitchPageButtonClick(self): # 得到输入字符串 szText = self.switchPageLineEdit.text() #数字正则表达式 pattern = re.compile(r'^[0-9]+$') match = pattern.match(szText) # 判断是否为数字 if not match : QMessageBox.information(self, "提示", "请输入数字" ) return # 是否为空 if szText == '' : QMessageBox.information(self, "提示" , "请输入跳转页面" ) return #得到页数 pageIndex = int(szText) #判断是否有指定页 if pageIndex > self.totalPage or pageIndex < 1 : QMessageBox.information(self, "提示", "没有指定的页面,请重新输入" ) return #得到查询起始行号 limitIndex = (pageIndex-1) * self.PageRecordCount #记录查询 self.recordQuery(limitIndex) #设置当前页 self.currentPage = pageIndex #刷新状态 self.updateStatus()
class QmyMainWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) #调用父类构造函数,创建窗体 self.ui = Ui_MainWindow() #创建UI对象 self.ui.setupUi(self) #构造UI界面 self.setCentralWidget(self.ui.splitter) # tableView显示属性设置 self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) ## self.ui.tableView.resizeColumnsToContents() ## ==============自定义功能函数============ def __getFieldNames(self): ##获取所有字段名称 emptyRec = self.qryModel.record() #获取空记录,只有字段名 self.fldNum = {} #字段名与序号的字典 for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) def __openTable(self): ##查询数据 self.qryModel = QSqlQueryModel(self) self.qryModel.setQuery( '''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo''' ) if self.qryModel.lastError().isValid(): QMessageBox.critical( self, "错误", "数据表查询错误,错误信息\n" + self.qryModel.lastError().text()) return self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount()) self.__getFieldNames() #获取字段名和序号 ##设置字段显示名,直接使用序号 self.qryModel.setHeaderData(0, Qt.Horizontal, "工号") self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.qryModel.setHeaderData(2, Qt.Horizontal, "性别") self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期") self.qryModel.setHeaderData(4, Qt.Horizontal, "省份") self.qryModel.setHeaderData(5, Qt.Horizontal, "部门") self.qryModel.setHeaderData(6, Qt.Horizontal, "工资") ## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号") ## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名") ## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别") ## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期") ## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份") ## self.qryModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门") ## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资") ##创建界面组件与数据模型的字段之间的数据映射 self.mapper = QDataWidgetMapper() self.mapper.setModel(self.qryModel) #设置数据模型 ## self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit) ##界面组件与qryModel的具体字段之间的联系 ## self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"]) ## self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"]) ## self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"]) ## self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"]) ## self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"] ) ## self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"] ) ## self.mapper.addMapping(self.ui.dbSpinSalary,self.fldNum["Salary"] ) self.mapper.addMapping(self.ui.dbSpinEmpNo, 0) self.mapper.addMapping(self.ui.dbEditName, 1) self.mapper.addMapping(self.ui.dbComboSex, 2) self.mapper.addMapping(self.ui.dbEditBirth, 3) self.mapper.addMapping(self.ui.dbComboProvince, 4) self.mapper.addMapping(self.ui.dbComboDep, 5) self.mapper.addMapping(self.ui.dbSpinSalary, 6) self.mapper.toFirst() #移动到首记录 self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型 self.selModel.currentRowChanged.connect( self.do_currentRowChanged) #选择行变化时 self.ui.tableView.setModel(self.qryModel) #设置数据模型 self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型 self.ui.actOpenDB.setEnabled(False) def __refreshTableView(self): ##刷新tableView显示 index = self.mapper.currentIndex() curIndex = self.qryModel.index(index, 1) #QModelIndex self.selModel.clearSelection() #清空选择项 self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) ## ==========由connectSlotsByName() 自动连接的槽函数================== @pyqtSlot() ##“打开数据库”按钮 def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return #打开数据库 self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动 self.DB.setDatabaseName(dbFilename) #设置数据库名称 ## DB.setHostName() ## DB.setUserName() ## DB.setPassword() if self.DB.open(): #打开数据库 self.__openTable() #打开数据表 else: QMessageBox.warning(self, "错误", "打开数据库失败") @pyqtSlot() ##首记录 def on_actRecFirst_triggered(self): self.mapper.toFirst() self.__refreshTableView() @pyqtSlot() ##前一记录 def on_actRecPrevious_triggered(self): self.mapper.toPrevious() self.__refreshTableView() @pyqtSlot() ##后一条记录 def on_actRecNext_triggered(self): self.mapper.toNext() self.__refreshTableView() @pyqtSlot() ##最后一条记录 def on_actRecLast_triggered(self): self.mapper.toLast() self.__refreshTableView() ## =============自定义槽函数=============================== def do_currentRowChanged(self, current, previous): ##记录移动时触发 if (current.isValid() == False): self.ui.dbLabPhoto.clear() #清除图片显示 return self.mapper.setCurrentIndex(current.row()) #更新数据映射的行号 first = (current.row() == 0) #是否首记录 last = (current.row() == self.qryModel.rowCount() - 1) #是否尾记录 self.ui.actRecFirst.setEnabled(not first) #更新使能状态 self.ui.actRecPrevious.setEnabled(not first) self.ui.actRecNext.setEnabled(not last) self.ui.actRecLast.setEnabled(not last) curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型 empNo = curRec.value("EmpNo") #不需要加 toInt()函数 query = QSqlQuery(self.DB) query.prepare( '''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''') query.bindValue(":ID", empNo) ## if not query.exec_(): #注意,在PyQt5.11.2之前的版本里只能使用exec_()函数 if not query.exec( ): #注意,在PyQt5.11.2添加了遗漏的overload型exec()函数,在PyQt5.11.2里没问题了 QMessageBox.critical(self, "错误", "执行SQL语句错误\n" + query.lastError().text()) return else: query.first() picData = query.value("Photo") if (picData == None): #图片字段内容为空 self.ui.dbLabPhoto.clear() else: #显示照片 pic = QPixmap() pic.loadFromData(picData) W = self.ui.dbLabPhoto.size().width() self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W)) memoData = query.value("Memo") #显示备注 self.ui.dbEditMemo.setPlainText(memoData)
class WarehouseController(QObject): def __init__(self, list, table, addButton, editButton, deleteButton, dbase): super().__init__() self.list = list self.table = table self.addButton = addButton self.editButton = editButton self.deleteButton = deleteButton self.dbase = dbase self.shopModel = QSqlTableModel(db = dbase) self.shopModel.setTable("shop") self.shopModel.select() self.list.setModel(ComplexListModel(self.shopModel, "{name}")) self.list.selectionModel().currentChanged.connect(self.listSelectionChanged) self._checkPrivileges() if self.only_select: self.addButton.setEnabled(False) self.deleteButton.setEnabled(False) self.addButton.clicked.connect(self.addButtonClicked) self.editButton.clicked.connect(self.editButtonClicked) self.deleteButton.clicked.connect(self.deleteButtonClicked) def _checkPrivileges(self): query = QSqlQuery("SHOW GRANTS") only_select = None table_pattern = "`{}`".format("shop_detail").lower() while query.next(): s = query.value(0).lower() if table_pattern in s: if "select" in s and only_select is None: only_select = True else: only_select = False self.only_select = bool(only_select) def listSelectionChanged(self, cur, prev): if not cur.isValid(): return self.table.setModel(None) else: self.setShopIndex(cur.row()) def setShopIndex(self, row): record = self.shopModel.record(row) self.detailModel = QSqlQueryModel() query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \ FROM shop_detail INNER JOIN detail \ ON shop_detail.detail_id = detail.id \ WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id")) self.detailModel.setQuery(query) self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование") self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество") self.table.setModel(self.detailModel) self.table.hideColumn(0) self.table.resizeColumnsToContents() self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged) if not self.detailModel.query().isActive(): print(self.detailModel.lastError().text()) self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) def tableSelectionChanged(self, cur, prev): if self.only_select: return if cur.isValid(): self.deleteButton.setEnabled(True) self.editButton.setEnabled(True) else: self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) self.addButton.setEnabled(True) def addButtonClicked(self): shop = self.shopModel.record(self.list.currentIndex().row()) query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \ FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \ WHERE shop_id={}))".format(shop.value("id"))) details = {} while query.next(): details[query.value("name")] = query.value("id") if not details: return QMessageBox.warning(None, "Ошибка добавления", "Не удалось добавить новый товар на склад: все возможные товары уже добавлены.") choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:", list(details.keys()), 0, False) if not ok: return qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1) if not ok: return detail_id = details[choice] shop_id = shop.value("id") query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \ VALUES ({}, {}, {})".format(shop_id, detail_id, qnt)) if not query.isActive(): print(query.lastError().text()) self.setShopIndex(self.list.currentIndex().row()) self.table.selectionModel().clearSelection() def editButtonClicked(self): detail = self.detailModel.record(self.table.currentIndex().row()) qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", detail.value("qnt"), 0) if not ok: return shop = self.shopModel.record(self.list.currentIndex().row()) if qnt > 0: query = QSqlQuery("UPDATE shop_detail SET quantity={} \ WHERE shop_id={} AND detail_id={}".format(qnt, shop.value("id"), detail.value("id"))) else: query = QSqlQuery("DELETE FROM shop_detail WHERE \ shop_id={} AND detail_id={} LIMIT 1".format( shop.value("id"), detail.value("id"))) if not query.isActive(): print(query.lastError().text()) self.setShopIndex(self.list.currentIndex().row()) def deleteButtonClicked(self): if not self.table.currentIndex().isValid(): return detail = self.detailModel.record(self.table.currentIndex().row()) shop = self.shopModel.record(self.list.currentIndex().row()) query = QSqlQuery("DELETE FROM shop_detail WHERE \ shop_id={} AND detail_id={} LIMIT 1".format( shop.value("id"), detail.value("id"))) if not query.isActive(): print(query.lastError().text()) self.setShopIndex(self.list.currentIndex().row()) def update(self): cur = self.list.currentIndex() if cur.isValid(): row = cur.row() else: row = 0 self.shopModel.select() self.list.reset() self.selectRow(row) def selectRow(self, row): self.list.selectionModel().clearSelection() self.list.selectionModel().setCurrentIndex( self.shopModel.index(row, 0), QItemSelectionModel.Select)
class MainWindow(QWidget): def __init__(self, token: str): connection = sqlite3.connect("users.sqlite") query = """ SELECT privileges.privilege FROM privileges JOIN users ON privileges.username = users.username WHERE users.username = ?; """ self.privileges = set( _[0] for _ in connection.execute(query, (token, )).fetchall()) connection.close() super().__init__() self.setWindowTitle("CSM") tabs = QTabWidget() if "SELECT" in self.privileges: db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("data.sqlite") db.open() if any(privilege in self.privileges for privilege in ("UPDATE", "INSERT", "DELETE")): self.people_model = QSqlTableModel() self.people_model.setTable("people") if "UPDATE" in self.privileges: self.people_model.setEditStrategy( QSqlTableModel.OnFieldChange) self.people_model.select() self.addresses_model = QSqlTableModel() self.addresses_model.setTable("addresses") if "UPDATE" in self.privileges: self.addresses_model.setEditStrategy( QSqlTableModel.OnFieldChange) self.addresses_model.select() else: self.people_model = QSqlQueryModel() self.people_model.setQuery("SELECT * FROM people;") self.addresses_model = QSqlQueryModel() self.addresses_model.setQuery("SELECT * FROM addresses;") self.people_model.setHeaderData(1, Qt.Horizontal, "Full name") self.people_model.setHeaderData(2, Qt.Horizontal, "Telephone") self.addresses_model.setHeaderData(1, Qt.Horizontal, "Person id") self.addresses_model.setHeaderData(2, Qt.Horizontal, "Street") self.addresses_model.setHeaderData(3, Qt.Horizontal, "City") self.addresses_model.setHeaderData(4, Qt.Horizontal, "State") self.people_table = QTableView() self.people_table.setModel(self.people_model) if "UPDATE" not in self.privileges: self.people_table.setEditTriggers( QAbstractItemView.NoEditTriggers) self.people_table.horizontalHeader().setSectionResizeMode( 0, QHeaderView.ResizeToContents) self.people_table.horizontalHeader().setSectionResizeMode( 1, QHeaderView.Stretch) self.people_table.horizontalHeader().setSectionResizeMode( 2, QHeaderView.Stretch) self.people_table.setSizeAdjustPolicy( QAbstractScrollArea.AdjustToContents) self.people_table.resizeColumnsToContents() self.addresses_table = QTableView() self.addresses_table.setModel(self.addresses_model) if "UPDATE" not in self.privileges: self.addresses_table.setEditTriggers( QAbstractItemView.NoEditTriggers) self.addresses_table.hideColumn(0) self.addresses_table.horizontalHeader().setSectionResizeMode( 1, QHeaderView.ResizeToContents) self.addresses_table.horizontalHeader().setSectionResizeMode( 2, QHeaderView.Stretch) self.addresses_table.horizontalHeader().setSectionResizeMode( 3, QHeaderView.Stretch) self.addresses_table.horizontalHeader().setSectionResizeMode( 4, QHeaderView.Stretch) self.addresses_table.setSizeAdjustPolicy( QAbstractScrollArea.AdjustToContents) self.addresses_table.resizeColumnsToContents() self.people_table.setMinimumWidth(self.people_table.width()) self.addresses_table.setMinimumWidth(self.addresses_table.width()) tabs.addTab(self.people_table, "People") tabs.addTab(self.addresses_table, "Addresses") tabs.currentChanged.connect(self.tab_switch_handler) HLayout = QHBoxLayout(self) HLayout.addWidget(tabs) self.VLayout = QVBoxLayout() self.VLayout.setAlignment(Qt.AlignTop) token_label = QLabel(token) self.VLayout.addWidget(token_label) if "INSERT" in self.privileges: self.insert_stack = QStackedWidget() self.people_insert = people_InsertWidget() self.people_insert.submit_button.clicked.connect( self.insert_people_handler) self.addresses_insert = addresses_InsertWidget() self.addresses_insert.submit_button.clicked.connect( self.insert_address_handler) self.insert_stack.addWidget(self.people_insert) self.insert_stack.addWidget(self.addresses_insert) self.insert_stack.setCurrentWidget(self.people_insert) self.insert_stack.setFixedSize(self.insert_stack.sizeHint()) self.VLayout.addWidget(self.insert_stack) self.VLayout.addSpacing(10) if "DELETE" in self.privileges: self.delete_stack = QStackedWidget() self.people_delete = QPushButton("DELETE FROM people") self.people_delete.clicked.connect(self.delete_people_handler) self.address_delete = QPushButton("DELETE FROM addresses") self.address_delete.clicked.connect(self.delete_address_handler) self.delete_stack.addWidget(self.people_delete) self.delete_stack.addWidget(self.address_delete) self.delete_stack.setCurrentWidget(self.people_delete) self.delete_stack.setFixedSize(self.delete_stack.sizeHint()) self.VLayout.addWidget(self.delete_stack) HLayout.addLayout(self.VLayout) #self.resize(self.sizeHint()) def tab_switch_handler(self, tab: int): if tab == 0: if "INSERT" in self.privileges: self.insert_stack.setCurrentWidget(self.people_insert) if "DELETE" in self.privileges: self.delete_stack.setCurrentWidget(self.people_delete) elif tab == 1: if "INSERT" in self.privileges: self.insert_stack.setCurrentWidget(self.addresses_insert) if "DELETE" in self.privileges: self.delete_stack.setCurrentWidget(self.address_delete) else: QMessageBox.warning(self, "Error", "Something went wrong!") def insert_people_handler(self): full_name = self.people_insert.full_name.text() telephone = self.people_insert.telephone.text() record = self.people_model.record() record.setGenerated("id", True) record.setValue("full_name", full_name) record.setValue("telephone", telephone) if self.people_model.insertRecord(-1, record): self.people_model.select() else: QMessageBox.warning( self, "Error", "Cannot insert {} {}".format(full_name, telephone)) def insert_address_handler(self): user_id = int(self.addresses_insert.user_id.text()) street = self.addresses_insert.street.text() city = self.addresses_insert.city.text() state = self.addresses_insert.state.text() record = self.addresses_model.record() record.setGenerated("id", True) record.setValue("user_id", user_id) record.setValue("street", street) record.setValue("city", city) record.setValue("state", state) if self.addresses_model.insertRecord(-1, record): self.addresses_model.select() else: QMessageBox.warning( self, "Error", "Cannot insert {} {} {} {}".format(user_id, street, city, state)) def delete_people_handler(self): rows = self.people_table.selectionModel().selectedRows() if len(rows) == 1: if not self.people_model.removeRow(rows[0].row()): QMessageBox.warning( self, "Error", "Cannot delete row {}".format(rows[0].row() + 1)) else: self.people_model.select() else: QMessageBox.warning(self, "Error", "Only 1 row can be deleted at a time") def delete_address_handler(self): rows = self.addresses_table.selectionModel().selectedRows() if len(rows) == 1: if not self.addresses_model.removeRow(rows[0].row()): QMessageBox.warning( self, "Error", "Cannot delete row {}".format(rows[0].row() + 1)) else: self.addresses_model.select() else: QMessageBox.warning(self, "Error", "Only 1 row can be deleted at a time")
class ProjectAlleles(FilterableTable): """a widget to display all alleles of one project, with their most important data """ changed_allele = pyqtSignal(str, int, str) change_view = pyqtSignal(int) def __init__(self, log, mydb): super().__init__(log, mydb, add_color_proxy = (4,5)) self.proxy.setFilterKeyColumn(2) self.filter_cb.setCurrentIndex(2) self.header_lbl.setText("Alleles:") self.table.verticalHeader().hide() self.table.customContextMenuRequested.connect(self.open_menu) self.project = "" self.filter(self.project) def create_model(self): """creates the table model """ self.log.debug("Creating the table model...") self.model = QSqlQueryModel() q = QSqlQuery() query = """SELECT project_name, project_nr, (sample_id_int || ' #' || allele_nr || ' (' || gene || ')'), local_name, allele_status, lab_status, sample_id_int, allele_nr FROM alleles order by project_nr """ q.exec_(query) self.check_error(q) self.model.setQuery(q) self.model.setHeaderData(1, Qt.Horizontal, "Nr") self.model.setHeaderData(2, Qt.Horizontal, "Target Allele") self.model.setHeaderData(3, Qt.Horizontal, "Allele Name") self.model.setHeaderData(4, Qt.Horizontal, "Allele Status") self.model.setHeaderData(5, Qt.Horizontal, "Lab Status") self.log.debug("\t=> Done!") def filter(self, project): self.project = project self.proxy.layoutAboutToBeChanged.emit() self.proxy.setFilterKeyColumn(0) self.proxy.setFilterFixedString(project) self.proxy.layoutChanged.emit() for col in [0, 6, 7]: self.table.hideColumn(col) @pyqtSlot() def refresh(self): """refreshes the table's content """ self.log.debug("\tRefreshing ProjectView's allele list...") self.model.setQuery(self.model.query().lastQuery()) @pyqtSlot(QPoint) def open_menu(self, pos): """provides a context menu """ try: menu = QMenu() open_allele_act = menu.addAction("Open Allele View") action = menu.exec_(self.table.mapToGlobal(pos)) if action: row = self.table.indexAt(pos).row() sample = self.proxy.data(self.proxy.index(row, 6)) allele_nr = int(self.proxy.data(self.proxy.index(row, 7))) if action == open_allele_act: self.changed_allele.emit(sample, allele_nr, self.project) self.change_view.emit(4) self.log.debug("ProjectAlleles emitted changed_allele to {} #{} ({}) & change_view to AlleleView".format(sample, allele_nr, self.project)) except Exception as E: self.log.exception(E)
class QmyMainWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) #调用父类构造函数,创建窗体 self.ui = Ui_MainWindow() #创建UI对象 self.ui.setupUi(self) #构造UI界面 self.setCentralWidget(self.ui.tableView) # tableView显示属性设置 ## self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) ## self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) ## self.ui.tableView.resizeColumnsToContents() ## ==============自定义功能函数============ def __getFieldNames(self): ##获取所有字段名称 emptyRec = self.qryModel.record() #获取空记录,只有字段名 self.fldNum = {} #字段名与序号的字典 for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) def __openTable(self): #查询数据 self.qryModel = QSqlQueryModel(self) self.qryModel.setQuery( '''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo''' ) if self.qryModel.lastError().isValid(): QMessageBox.critical( self, "错误", "数据表查询错误,错误信息\n" + self.qryModel.lastError().text()) return self.__getFieldNames() #获取字段名和序号 ##字段显示名 self.qryModel.setHeaderData(0, Qt.Horizontal, "工号") self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.qryModel.setHeaderData(2, Qt.Horizontal, "性别") self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期") self.qryModel.setHeaderData(4, Qt.Horizontal, "省份") self.qryModel.setHeaderData(5, Qt.Horizontal, "部门") self.qryModel.setHeaderData(6, Qt.Horizontal, "工资") ## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal,"工号") ## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal,"姓名") ## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal,"性别") ## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal,"出生日期") ## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal,"省份") ## self.qryModel.setHeaderData(self.fldNum["Department"],Qt.Horizontal,"部门") ## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal,"工资") self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型 ##选择行变化时 self.selModel.currentRowChanged.connect(self.do_currentRowChanged) self.ui.tableView.setModel(self.qryModel) #设置数据模型 self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型 self.ui.actOpenDB.setEnabled(False) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actRecEdit.setEnabled(True) self.ui.actScan.setEnabled(True) self.ui.actTestSQL.setEnabled(True) def __updateRecord(self, recNo): ##更新一条记录 curRec = self.qryModel.record(recNo) #获取当前记录 empNo = curRec.value("EmpNo") #获取EmpNo query = QSqlQuery(self.DB) #查询出当前记录的所有字段 query.prepare("SELECT * FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) query.exec() # query.first() if (not query.isValid()): #是否为有效记录 return curRec = query.record() #获取当前记录的数据,QSqlRecord类型 dlgData = QmyDialogData(self) #创建对话框 dlgData.setUpdateRecord(curRec) #调用对话框函数更新数据和界面 ret = dlgData.exec() # 以模态方式显示对话框 if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() #获得对话框返回的记录 query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender, Birthday=:Birthday, Province=:Province, Department=:Department, Salary=:Salary, Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''') query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) query.bindValue(":ID", empNo) ## if (not query.exec_()): if (not query.exec()): #PyQt 5.11.2以前应该使用exec_()函数 QMessageBox.critical(self, "错误", "记录更新错误\n" + query.lastError().text()) else: self.qryModel.query().exec() #数据模型重新查询数据,更新tableView显示 ## ==========由connectSlotsByName() 自动连接的槽函数================== @pyqtSlot() ##打开数据库 def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return #打开数据库 self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动 self.DB.setDatabaseName(dbFilename) #设置数据库名称 ## DB.setHostName() ## DB.setUserName() ## DB.setPassword() if self.DB.open(): #打开数据库 self.__openTable() #查询数据 else: QMessageBox.warning(self, "错误", "打开数据库失败") @pyqtSlot() ##插入记录 def on_actRecInsert_triggered(self): query = QSqlQuery(self.DB) query.exec("select * from employee where EmpNo =-1") #实际不查询出记录,只查询字段信息 curRec = query.record() #获取当前记录,实际为空记录,但有字段信息 curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000) dlgData = QmyDialogData(self) dlgData.setInsertRecord(curRec) #插入记录 ret = dlgData.exec() #以模态方式显示对话框 if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday, Province,Department,Salary,Memo,Photo) VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province, :Department,:Salary,:Memo,:Photo)''') query.bindValue(":EmpNo", recData.value("EmpNo")) query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) res = query.exec() #执行SQL语句 if (res == False): QMessageBox.critical(self, "错误", "插入记录错误\n" + query.lastError().text()) else: #插入,删除记录后需要重新设置SQL语句查询 sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句 self.qryModel.setQuery(sqlStr) #reset 重新查询数据 @pyqtSlot() ##删除记录 def on_actRecDelete_triggered(self): curRecNo = self.selModel.currentIndex().row() curRec = self.qryModel.record(curRecNo) #获取当前记录 if (curRec.isEmpty()): #当前为空记录 return empNo = curRec.value("EmpNo") #获取员工编号 query = QSqlQuery(self.DB) query.prepare("DELETE FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) if (query.exec() == False): QMessageBox.critical(self, "错误", "删除记录出现错误\n" + query.lastError().text()) else: #插入,删除记录后需要重新设置SQL语句查询 sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句 self.qryModel.setQuery(sqlStr) #reset 重新查询数据 @pyqtSlot() ##编辑记录 def on_actRecEdit_triggered(self): curRecNo = self.selModel.currentIndex().row() self.__updateRecord(curRecNo) ## @pyqtSlot() ##双击编辑记录 def on_tableView_doubleClicked(self, index): curRecNo = index.row() self.__updateRecord(curRecNo) @pyqtSlot() ##遍历记录,涨工资 def on_actScan_triggered(self): qryEmpList = QSqlQuery(self.DB) #员工工资信息列表 qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo") qryUpdate = QSqlQuery(self.DB) #临时 QSqlQuery qryUpdate.prepare( '''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''') qryEmpList.first() while (qryEmpList.isValid()): #当前记录有效 empID = qryEmpList.value("empNo") #获取empNo salary = qryEmpList.value("Salary") #获取Salary salary = salary + 500 #涨工资 qryUpdate.bindValue(":ID", empID) qryUpdate.bindValue(":Salary", salary) #设置SQL语句参数 qryUpdate.exec() #执行update语句 if not qryEmpList.next(): #移动到下一条记录,并判断是否到末尾了 break self.qryModel.query().exec() #数据模型重新查询数据,更新tableView的显示 QMessageBox.information(self, "提示", "涨工资计算完毕") @pyqtSlot() ##SQL语句测试 def on_actTestSQL_triggered(self): query = QSqlQuery(self.DB) ## # SQL语句测试1, exec_() 和exec()都可以直接执行不带参数的SQL语句 ## query.exec('''UPDATE employee SET Salary=3000 where Gender="女" ''') ## query.exec_('''UPDATE employee SET Salary=4500 where Gender="女" ''') # SQL语句测试2,执行带参数的SQL语句,只能用 exec_(),不能用exec() ## query.prepare('''UPDATE employee SET Salary=9000 where Gender=:Gender ''') ## query.bindValue(":Gender","男") ## query.exec() query.exec('''UPDATE employee SET Salary=500+Salary ''') ## query.bindValue(":Gender","男") ## query.exec() ## query.prepare("UPDATE employee SET Department=?, Salary=? where Name=?") ## query.bindValue(0, "技术部") ## query.bindValue(1, 5500) ## query.bindValue(2, "张三") ## query.exec_() #只能用exec_(),而不能用exec()函数 ## self.qryModel.query().exec() #不增减记录时更新显示 ## 增减记录后的更新显示 sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句 self.qryModel.setQuery(sqlStr) #reset 重新查询数据 print("SQL OK") ## =============自定义槽函数=============================== def do_currentRowChanged(self, current, previous): ##行切换时触发 if (current.isValid() == False): return curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型 empNo = curRec.value("EmpNo") #不需要加 toInt()函数 self.ui.statusBar.showMessage("当前记录:工号=%d" % empNo)
class QmyMainWindow(QtWidgets.QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.setCentralWidget(self.ui.splitter) self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) @pyqtSlot() def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return self.DB = QSqlDatabase.addDatabase("QSQLITE") self.DB.setDatabaseName(dbFilename) if self.DB.open(): self.__openTable() else: QMessageBox.warning(self, "错误", "打开数据库失败") def __openTable(self): self.qryModel = QSqlQueryModel(self) self.qryModel.setQuery( '''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo''' ) if self.qryModel.lastError().isValid(): QMessageBox.critical( self, "错误", "数据表查询错误,错误信息\n" + self.qryModel.lastError().text()) return self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount()) self.__getFieldNames() self.qryModel.setHeaderData(0, Qt.Horizontal, "工号") self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.qryModel.setHeaderData(2, Qt.Horizontal, "性别") self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期") self.qryModel.setHeaderData(4, Qt.Horizontal, "省份") self.qryModel.setHeaderData(5, Qt.Horizontal, "部门") self.qryModel.setHeaderData(6, Qt.Horizontal, "工资") self.mapper = QDataWidgetMapper() self.mapper.setModel(self.qryModel) self.mapper.addMapping(self.ui.dbSpinEmpNo, 0) self.mapper.addMapping(self.ui.dbEditName, 1) self.mapper.addMapping(self.ui.dbComboSex, 2) self.mapper.addMapping(self.ui.dbEditBirth, 3) self.mapper.addMapping(self.ui.dbComboProvince, 4) self.mapper.addMapping(self.ui.dbComboDep, 5) self.mapper.addMapping(self.ui.dbSpinSalary, 6) self.mapper.toFirst() self.selModel = QItemSelectionModel(self.qryModel) self.selModel.currentRowChanged.connect(self.do_currentRowChanged) self.ui.tableView.setModel(self.qryModel) self.ui.tableView.setSelectionModel(self.selModel) self.ui.actOpenDB.setEnabled(False) def __refreshTableView(self): index = self.mapper.currentIndex() curIndex = self.qryModel.index(index, 1) self.selModel.clearSelection() self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) def __getFieldNames(self): emptyRec = self.qryModel.record() self.fldNum = {} for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) @pyqtSlot() def on_actRecFirst_triggered(self): self.mapper.toFirst() self.__refreshTableView() @pyqtSlot() def on_actRecPrevious_triggered(self): self.mapper.toPrevious() self.__refreshTableView() @pyqtSlot() def on_actRecNext_triggered(self): self.mapper.toNext() self.__refreshTableView() @pyqtSlot() def on_actRecLast_triggered(self): self.mapper.toLast() self.__refreshTableView() def do_currentRowChanged(self, current, previous): if (current.isValid() == False): self.ui.dbLabPhoto.clear() return self.mapper.setCurrentIndex(current.row()) first = (current.row() == 0) last = (current.row() == self.qryModel.rowCount() - 1) self.ui.actRecFirst.setEnabled(not first) self.ui.actRecPrevious.setEnabled(not first) self.ui.actRecNext.setEnabled(not last) self.ui.actRecLast.setEnabled(not last) curRec = self.qryModel.record(current.row()) empNo = curRec.value("EmpNo") query = QSqlQuery(self.DB) query.prepare( '''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''') query.bindValue(":ID", empNo) if not query.exec(): QMessageBox.critical(self, "错误", "执行SQL语句错误\n" + query.lastError().text()) return else: query.first() picData = query.value("Photo") if (picData == None): self.ui.dbLabPhoto.clear() else: pic = QPixmap() pic.loadFromData(picData) W = self.ui.dbLabPhoto.size().width() self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W)) memoData = query.value("Memo") self.ui.dbEditMemo.setPlainText(memoData)
class QmyMainWindow(QtWidgets.QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.setCentralWidget(self.ui.tableView) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) def __getFieldNames(self): emptyRec = self.qryModel.record() self.fldNum = {} for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) def __openTable(self): self.qryModel = QSqlQueryModel(self) self.qryModel.setQuery( '''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo''' ) if self.qryModel.lastError().isValid(): QMessageBox.critical( self, "错误", "数据表查询错误,错误信息\n" + self.qryModel.lastError().text()) return self.__getFieldNames() self.qryModel.setHeaderData(0, Qt.Horizontal, "工号") self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.qryModel.setHeaderData(2, Qt.Horizontal, "性别") self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期") self.qryModel.setHeaderData(4, Qt.Horizontal, "省份") self.qryModel.setHeaderData(5, Qt.Horizontal, "部门") self.qryModel.setHeaderData(6, Qt.Horizontal, "工资") self.selModel = QItemSelectionModel(self.qryModel) self.selModel.currentRowChanged.connect(self.do_currentRowChanged) self.ui.tableView.setModel(self.qryModel) self.ui.tableView.setSelectionModel(self.selModel) self.ui.actOpenDB.setEnabled(False) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actRecEdit.setEnabled(True) self.ui.actScan.setEnabled(True) self.ui.actTestSQL.setEnabled(True) def __updateRecord(self, recNo): curRec = self.qryModel.record(recNo) empNo = curRec.value("EmpNo") query = QSqlQuery(self.DB) query.prepare("SELECT * FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) query.exec() query.first() if (not query.isValid()): return curRec = query.record() dlgData = QmyDialogData(self) dlgData.setUpdateRecord(curRec) ret = dlgData.exec() if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender, Birthday=:Birthday, Province=:Province, Department=:Department, Salary=:Salary, Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''') query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) query.bindValue(":ID", empNo) if (not query.exec()): QMessageBox.critical(self, "错误", "记录更新错误\n" + query.lastError().text()) else: self.qryModel.query().exec() @pyqtSlot() def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return self.DB = QSqlDatabase.addDatabase("QSQLITE") self.DB.setDatabaseName(dbFilename) if self.DB.open(): self.__openTable() else: QMessageBox.warning(self, "错误", "打开数据库失败") @pyqtSlot() def on_actRecInsert_triggered(self): query = QSqlQuery(self.DB) query.exec("select * from employee where EmpNo = -1") curRec = query.record() curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000) dlgData = QmyDialogData(self) dlgData.setInsertRecord(curRec) ret = dlgData.exec() if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday, Province,Department,Salary,Memo,Photo) VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province, :Department,:Salary,:Memo,:Photo)''') query.bindValue(":EmpNo", recData.value("EmpNo")) query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) res = query.exec() if (res == False): QMessageBox.critical(self, "错误", "插入记录错误\n" + query.lastError().text()) else: sqlStr = self.qryModel.query().executedQuery() self.qryModel.setQuery(sqlStr) @pyqtSlot() def on_actRecDelete_triggered(self): curRecNo = self.selModel.currentIndex().row() curRec = self.qryModel.record(curRecNo) if (curRec.isEmpty()): return empNo = curRec.value("EmpNo") query = QSqlQuery(self.DB) query.prepare("DELETE FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) if (query.exec() == False): QMessageBox.critical(self, "错误", "删除记录出现错误\n" + query.lastError().text()) else: sqlStr = self.qryModel.query().executedQuery() self.qryModel.setQuery(sqlStr) @pyqtSlot() def on_actRecEdit_triggered(self): curRecNo = self.selModel.currentIndex().row() self.__updateRecord(curRecNo) def on_tableView_doubleClicked(self, index): curRecNo = index.row() self.__updateRecord(curRecNo) @pyqtSlot() def on_actScan_triggered(self): qryEmpList = QSqlQuery(self.DB) qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo") qryUpdate = QSqlQuery(self.DB) qryUpdate.prepare( '''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''') qryEmpList.first() while (qryEmpList.isValid()): empID = qryEmpList.value("empNo") salary = qryEmpList.value("Salary") salary = salary + 500 qryUpdate.bindValue(":ID", empID) qryUpdate.bindValue(":Salary", salary) qryUpdate.exec() if not qryEmpList.next(): break self.qryModel.query().exec() QMessageBox.information(self, "提示", "涨工资计算完毕") @pyqtSlot() def on_actTestSQL_triggered(self): query = QSqlQuery(self.DB) query.exec('''UPDATE employee SET Salary=500+Salary''') sqlStr = self.qryModel.query().executedQuery() self.qryModel.setQuery(sqlStr) print("SQL OK") def do_currentRowChanged(self, current, previous): if (current.isValid() == False): return curRec = self.qryModel.record(current.row()) empNo = curRec.value("EmpNo") self.ui.statusBar.showMessage("当前记录:工号 = %d" % empNo)
# Fill for i in range(1000 - 1): value = ''.join(string.ascii_lowercase[j] for j in map(int, str(i).zfill(3))) db.exec(f"INSERT OR IGNORE INTO Dict (key, value) VALUES ({i}, {value!r})") WINDOW_WIDTH = 400 WINDOW_HEIGHT = 300 app = QApplication([]) model = QSqlQueryModel() model.setQuery("SELECT id, key, value FROM Dict") model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "KEY") model.setHeaderData(2, Qt.Horizontal, "VALUE") list_view = QListView() list_view.setWindowTitle('QListView') list_view.setModel(model) list_view.move(100, 50) list_view.resize(WINDOW_WIDTH, WINDOW_HEIGHT) list_view.show() list_view_2 = QListView() list_view_2.setWindowTitle('QListView + ItemDelegate') list_view_2.setItemDelegate(ListViewDelegate()) list_view_2.setModel(model) list_view_2.move(list_view.geometry().right(), 50)
class DataGrid(QWidget): def __init__(self, input_num): super().__init__() self.setWindowTitle("订单信息查询,按号查找") self.setWindowFlags(Qt.WindowStaysOnTopHint) self.input_num = input_num self.resize(750, 300) # 查询模型 self.queryModel = None # 数据表 self.tableView = None self.labelMMM = None # 总数页文本 self.totalPageLabel = None # 当前页文本 self.currentPageLabel = None # 转到页输入框 self.switchPageLineEdit = None # 前一页按钮 self.prevButton = None # 后一页按钮 self.nextButton = None # 转到页按钮 self.switchPageButton = None # 当前页 self.currentPage = 0 # 总页数 self.totalPage = 0 # 总记录数 self.totalRecrodCount = 0 # 每页显示记录数 self.PageRecordCount = 5 self.db = None self.initUI() def initUI(self): # 创建窗口 self.createWindow() # 设置表格 self.setTableView() # 信号槽连接 self.prevButton.clicked.connect(self.onPrevButtonClick) self.nextButton.clicked.connect(self.onNextButtonClick) self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick) self.changeButton.clicked.connect(self.change) self.deleteButton.clicked.connect(self.delete) def closeEvent(self, event): # 关闭数据库 self.db.close() # 创建窗口 def createWindow(self): # 操作布局 operatorLayout = QHBoxLayout() self.prevButton = QPushButton("前一页") self.nextButton = QPushButton("后一页") self.changeButton = QPushButton("修改") self.deleteButton = QPushButton("删除") self.labelMMM = QLabel(" " * 20) self.switchPageButton = QPushButton("Go") self.switchPageLineEdit = QLineEdit() self.switchPageLineEdit.setFixedWidth(40) switchPage = QLabel("转到第") page = QLabel("页") operatorLayout.addWidget(self.prevButton) operatorLayout.addWidget(self.nextButton) operatorLayout.addWidget(switchPage) operatorLayout.addWidget(self.switchPageLineEdit) operatorLayout.addWidget(page) operatorLayout.addWidget(self.switchPageButton) operatorLayout.addWidget(self.labelMMM) operatorLayout.addWidget(self.changeButton) operatorLayout.addWidget(self.deleteButton) operatorLayout.addWidget(QSplitter()) # 状态布局 statusLayout = QHBoxLayout() self.totalPageLabel = QLabel() self.totalPageLabel.setFixedWidth(70) self.currentPageLabel = QLabel() self.currentPageLabel.setFixedWidth(70) self.totalRecordLabel = QLabel() self.totalRecordLabel.setFixedWidth(70) statusLayout.addWidget(self.totalPageLabel) statusLayout.addWidget(self.currentPageLabel) statusLayout.addWidget(QSplitter()) statusLayout.addWidget(self.totalRecordLabel) # 设置表格属性 self.tableView = QTableView() # 表格宽度的自适应调整 self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) # 创建界面 mainLayout = QVBoxLayout(self) mainLayout.addLayout(operatorLayout) mainLayout.addWidget(self.tableView) mainLayout.addLayout(statusLayout) self.setLayout(mainLayout) # 设置表格 def setTableView(self): print('*** step2 SetTableView') self.db = QSqlDatabase.addDatabase('QMYSQL') self.db.setHostName("67.209.xxx.xxx") self.db.setDatabaseName("db_sharedbike") self.db.setUserName("root") self.db.setPassword("xxxxxxxxx") # 打开数据库 self.db.open() # 声明查询模型 self.queryModel = QSqlQueryModel(self) # 设置当前页 self.currentPage = 1 # 得到总记录数 self.totalRecrodCount = self.getTotalRecordCount() # 得到总页数 self.totalPage = self.getPageCount() # 刷新状态 self.updateStatus() # 设置总页数文本 self.setTotalPageLabel() # 设置总记录数 self.setTotalRecordLabel() # 记录查询 self.recordQuery(0) # 设置模型 self.tableView.setModel(self.queryModel) print('totalRecrodCount=' + str(self.totalRecrodCount)) print('totalPage=' + str(self.totalPage)) # 设置表格表头 self.queryModel.setHeaderData(0, Qt.Horizontal, "订单编号") self.queryModel.setHeaderData(1, Qt.Horizontal, "用户编号") self.queryModel.setHeaderData(2, Qt.Horizontal, "自行车编号") self.queryModel.setHeaderData(3, Qt.Horizontal, "下单时间") self.queryModel.setHeaderData(4, Qt.Horizontal, "结账时间") # 得到记录数 def getTotalRecordCount(self): self.queryModel.setQuery("select * from order_info where id = %s" % (self.input_num)) rowCount = self.queryModel.rowCount() print('rowCount=' + str(rowCount)) return rowCount # 得到页数 def getPageCount(self): if self.totalRecrodCount == 0: self.currentPage = 0 if self.totalRecrodCount % self.PageRecordCount == 0: return (self.totalRecrodCount / self.PageRecordCount) else: return (int(self.totalRecrodCount / self.PageRecordCount) + 1) # 记录查询 def recordQuery(self, limitIndex): szQuery = ("select * from order_info where id=%s limit %d,%d" % (self.input_num, limitIndex, self.PageRecordCount)) print('query sql=' + szQuery) self.queryModel.setQuery(szQuery) # 刷新状态 def updateStatus(self): szCurrentText = ("当前第%d页" % self.currentPage) self.currentPageLabel.setText(szCurrentText) #设置按钮是否可用 if self.currentPage == 0: self.prevButton.setEnabled(False) self.nextButton.setEnabled(False) return if self.currentPage == 1: self.prevButton.setEnabled(False) else: self.prevButton.setEnabled(True) if self.currentPage == self.totalPage: self.nextButton.setEnabled(False) else: self.nextButton.setEnabled(True) # 设置总数页文本 def setTotalPageLabel(self): szPageCountText = ("总共%d页" % self.totalPage) self.totalPageLabel.setText(szPageCountText) # 设置总记录数 def setTotalRecordLabel(self): szTotalRecordText = ("共%d条" % self.totalRecrodCount) print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText) self.totalRecordLabel.setText(szTotalRecordText) def update(self): print("it is me") # 设置表格 self.setTableView() def change(self): from .chaneAdminByNum import chaneAdmin self.chanadminbynum = chaneAdmin(self.update) self.chanadminbynum.show() def delete(self): from .DeleteAdminByNum import DeleteAdminByNum self.deleteadminbynum = DeleteAdminByNum(self.update) self.deleteadminbynum.show() # 前一页按钮按下 def onPrevButtonClick(self): print('*** onPrevButtonClick ') limitIndex = (self.currentPage - 2) * self.PageRecordCount self.recordQuery(limitIndex) self.currentPage -= 1 self.updateStatus() # 后一页按钮按下 def onNextButtonClick(self): print('*** onNextButtonClick ') limitIndex = self.currentPage * self.PageRecordCount self.recordQuery(limitIndex) self.currentPage += 1 self.updateStatus() # 转到页按钮按下 def onSwitchPageButtonClick(self): # 得到输入字符串 szText = self.switchPageLineEdit.text() #数字正则表达式 #pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$') #match = pattern.match(szText) try: match = None match = int(szText) except Exception as e: print(e) # 判断是否为数字 if not match: QMessageBox.information(self, "提示", "请输入数字") return # 是否为空 if szText == '': QMessageBox.information(self, "提示", "请输入跳转页面") return #得到页数 pageIndex = int(szText) #判断是否有指定页 if pageIndex > self.totalPage or pageIndex < 1: QMessageBox.information(self, "提示", "没有指定的页面,请重新输入") return #得到查询起始行号 limitIndex = (pageIndex - 1) * self.PageRecordCount #记录查询 self.recordQuery(limitIndex) #设置当前页 self.currentPage = pageIndex #刷新状态 self.updateStatus()
class StudentMainUi(QMainWindow): def __init__(self): super().__init__() #self.search_layout = QtWidgets.QHBoxLayout() self.OpenRuslt = 0 # 查询模型 self.queryModel = None # 数据表 self.tableView = None # 当前页 self.currentPage = 0 # 总页数 self.totalPage = 0 # 总记录数 self.totalRecord = 0 # 每页数据数 self.pageRecord = 10 self.initUi() # 初始化UI界面 def initUi(self): # 设置窗口标题 self.setWindowTitle("欢迎使用图书查询") # 设置窗口大小 self.resize(700, 500) self.widget2 = QtWidgets.QWidget() self.layout = QGridLayout() self.Hlayout1 = QHBoxLayout() self.Hlayout2 = QHBoxLayout() self.Hlayout3 = QHBoxLayout() self.Hlayout = QHBoxLayout() # Hlayout1 # 创建一个文本框部件 self.line = QtWidgets.QLineEdit() self.ButtonSerch = QtWidgets.QPushButton('查询') self.combox = QtWidgets.QComboBox() self.item = ['ISBN', 'BookName'] self.combox.addItems(self.item) self.Hlayout1.addWidget(self.line) self.Hlayout1.addWidget(self.ButtonSerch) self.Hlayout1.addWidget(self.combox) # Hlayout2初始化 self.jumpToLabel = QLabel("跳转到第") self.pageEdit = QLineEdit() self.pageEdit.setFixedWidth(30) s = "/" + str(self.totalPage) + "页" self.pageLabel = QLabel(s) self.jumpToButton = QtWidgets.QPushButton("跳转") self.prevButton = QtWidgets.QPushButton("前一页") self.prevButton.setFixedWidth(60) self.backButton = QtWidgets.QPushButton("后一页") self.backButton.setFixedWidth(60) Hlayout = QHBoxLayout() Hlayout.addWidget(self.jumpToLabel) Hlayout.addWidget(self.pageEdit) Hlayout.addWidget(self.pageLabel) Hlayout.addWidget(self.jumpToButton) Hlayout.addWidget(self.prevButton) Hlayout.addWidget(self.backButton) widget = QtWidgets.QWidget() widget.setLayout(Hlayout) self.Hlayout3.addWidget(widget) # 创建一个按钮组 self.group_box = QtWidgets.QGroupBox() self.group_box_layout = QtWidgets.QVBoxLayout() self.group_box.setLayout(self.group_box_layout) # Table_View self.tableView = QtWidgets.QTableView() self.tableView.setFixedWidth(500) # self.tableView.horizontalHeader().setStretchLastSection(True) # self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers) self.queryModel = QSqlQueryModel() self.ButtonSerch_clicked() self.tableView.setModel(self.queryModel) self.queryModel.setHeaderData(0, Qt.Horizontal, "ISBN") self.queryModel.setHeaderData(1, Qt.Horizontal, "BookName") self.queryModel.setHeaderData(2, Qt.Horizontal, "Author") self.queryModel.setHeaderData(3, Qt.Horizontal, "Publisher") self.queryModel.setHeaderData(4, Qt.Horizontal, "Date") self.queryModel.setHeaderData(5, Qt.Horizontal, "Score") self.queryModel.setHeaderData(6, Qt.Horizontal, "Photo") # 创建按钮组的按钮 self.ButtonCreatDb = QtWidgets.QPushButton("打开数据库") self.ButtonCreatDb.setFixedHeight(40) self.ButtonCreatDb.setFixedWidth(90) self.ButtonClose = QtWidgets.QPushButton("退出") self.ButtonClose.setFixedHeight(40) self.ButtonClose.setFixedWidth(90) self.StudentInfo = QtWidgets.QTextEdit(" ") self.StudentInfo.setFixedHeight(100) self.StudentInfo.setFixedWidth(200) # 添加按钮到按钮组中 self.group_box_layout.addWidget(self.StudentInfo) self.group_box_layout.addWidget(self.ButtonCreatDb) self.group_box_layout.addWidget(self.ButtonClose) self.Hlayout2.addWidget(self.group_box) self.Hlayout2.addWidget(self.tableView) self.layout.addLayout(self.Hlayout2, 1, 0) self.layout.addLayout(self.Hlayout1, 0, 0) self.layout.addLayout(self.Hlayout3, 2, 0) # self.layout.addWidget(self.StudentInfo,0,0) self.widget2.setLayout(self.layout) # 发射信号 clicked Or Triggered self.ButtonCreatDb.clicked.connect(self.openDb) # 打开数据库信号 self.ButtonClose.clicked.connect(self.close) # 关闭信号 self.ButtonSerch.clicked.connect(self.ButtonSerch_clicked) # 查询 self.backButton.clicked.connect(self.backButtonClicked) self.prevButton.clicked.connect(self.prevButtonClicked) self.jumpToButton.clicked.connect(self.jumpToButtonClicked) self.setCentralWidget(self.widget2) self.show() # -----------------------------------------------槽函数---------------------------------------- """打开数据库的槽函数""" def openDb(self): db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName('F:/pycharm项目/图书管理系统NEW/AllDataBase/book.db') db.open() # 显示数据库 self.model = QtSql.QSqlTableModel() # 将数据库显示在表中 self.tableView.setModel(self.model) self.model.setTable('BookData') # 设置数据模型的数据表 self.model.setEditStrategy( QtSql.QSqlTableModel.OnManualSubmit) # 不允许字段更改 self.totalRecord = self.model.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) self.model.select() # 查询所有数据 # 设置表格头 self.model.setHeaderData(0, QtCore.Qt.Horizontal, 'ISBN') self.model.setHeaderData(1, QtCore.Qt.Horizontal, 'BookName') self.model.setHeaderData(2, QtCore.Qt.Horizontal, 'Author') self.model.setHeaderData(3, QtCore.Qt.Horizontal, 'Publisher') self.model.setHeaderData(4, QtCore.Qt.Horizontal, 'Date') self.model.setHeaderData(5, QtCore.Qt.Horizontal, 'Score') self.model.setHeaderData(6, QtCore.Qt.Horizontal, 'Photo') self.OpenRuslt += 1 # 显示有多少数据 self.queryModel = QSqlQueryModel() queryCondition = "select * from BookData" self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) def setButtonStatus(self): if(self.currentPage == self.totalPage): self.prevButton.setEnabled(True) self.backButton.setEnabled(False) if(self.currentPage == 1): self.backButton.setEnabled(True) self.prevButton.setEnabled(False) if(self.currentPage < self.totalPage and self.currentPage > 1): self.prevButton.setEnabled(True) self.backButton.setEnabled(True) # 得到记录数 def getTotalRecordCount(self): self.queryModel.setQuery("SELECT * FROM BookData") self.totalRecord = self.queryModel.rowCount() return # 得到总页数 def getPageCount(self): self.getTotalRecordCount() # 上取整 self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) print(self.totalPage) return def on_tableWidget_currentCellChanged( self, currentRow, currentColumn, previousRow, previousColumn): """ 当前单元格改变 """ pass def recordQuery(self, index): """ 查找 """ self.queryModel = QSqlQueryModel() self.tableView.setModel(self.queryModel) conditionChoice = self.combox.currentText() if (conditionChoice == "ISBN"): conditionChoice = 'ISBN' elif (conditionChoice == "BookName"): conditionChoice = 'BookName' if (self.line.text() == ""): queryCondition = "select * from BookData" self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select * from BookData ORDER BY %s limit %d,%d " % (conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return # 得到模糊查询条件 temp = self.line.text() s = '%' for i in range(0, len(temp)): s = s + temp[i] + "%" queryCondition = ( "SELECT * FROM BookData WHERE %s LIKE '%s' ORDER BY %s " % (conditionChoice, s, conditionChoice)) self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "SELECT * FROM BookData WHERE %s LIKE '%s' ORDER BY %s LIMIT %d,%d " % (conditionChoice, s, conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return def ButtonSerch_clicked(self): self.currentPage = 1 self.pageEdit.setText(str(self.currentPage)) self.getPageCount() s = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(s) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return # 向前翻页 def prevButtonClicked(self): self.currentPage -= 1 if (self.currentPage <= 1): self.currentPage = 1 self.pageEdit.setText(str(self.currentPage)) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return # 向后翻页 def backButtonClicked(self): self.currentPage += 1 if (self.currentPage >= int(self.totalPage)): self.currentPage = int(self.totalPage) self.pageEdit.setText(str(self.currentPage)) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return # 点击跳转 def jumpToButtonClicked(self): if (self.pageEdit.text().isdigit()): self.currentPage = int(self.pageEdit.text()) if (self.currentPage > self.totalPage): self.currentPage = self.totalPage if (self.currentPage <= 1): self.currentPage = 1 else: self.currentPage = 1 index = (self.currentPage - 1) * self.pageRecord self.pageEdit.setText(str(self.currentPage)) self.recordQuery(index) return
class DataGrid(QWidget): def __init__(self): super().__init__() self.setWindowTitle("分页查询例子") self.resize(750,300) # 查询模型 self.queryModel = None # 数据表 self.tableView = None # 总数页文本 self.totalPageLabel = None # 当前页文本 self.currentPageLabel = None # 转到页输入框 self.switchPageLineEdit = None # 前一页按钮 self.prevButton = None # 后一页按钮 self.nextButton = None # 转到页按钮 self.switchPageButton = None # 当前页 self.currentPage = 0 # 总页数 self.totalPage = 0 # 总记录数 self.totalRecrodCount = 0 # 每页显示记录数 self.PageRecordCount = 5 self.initUI() def initUI(self): # 创建窗口 self.createWindow() # 设置表格 self.setTableView() # 信号槽连接 self.prevButton.clicked.connect(self.onPrevButtonClick ) self.nextButton.clicked.connect(self.onNextButtonClick ) self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick ) # 创建数据库 # 创建窗口 def createWindow(self): # 操作布局 operatorLayout = QHBoxLayout() self.prevButton = QPushButton("前一页") self.nextButton = QPushButton("后一页") self.switchPageButton = QPushButton("Go") self.switchPageLineEdit = QLineEdit() self.switchPageLineEdit.setFixedWidth(40) switchPage = QLabel("转到第") page = QLabel("页") operatorLayout.addWidget(self.prevButton) operatorLayout.addWidget(self.nextButton) operatorLayout.addWidget(switchPage) operatorLayout.addWidget(self.switchPageLineEdit) operatorLayout.addWidget(page) operatorLayout.addWidget(self.switchPageButton) operatorLayout.addWidget( QSplitter()) # 状态布局 statusLayout = QHBoxLayout() self.totalPageLabel = QLabel() self.totalPageLabel.setFixedWidth(70) self.currentPageLabel = QLabel() self.currentPageLabel.setFixedWidth(70) self.totalRecordLabel = QLabel() self.totalRecordLabel.setFixedWidth(70) statusLayout.addWidget(self.totalPageLabel) statusLayout.addWidget(self.currentPageLabel) statusLayout.addWidget( QSplitter() ) statusLayout.addWidget(self.totalRecordLabel) # 设置表格属性 self.tableView = QTableView() # 表格宽度的自适应调整 self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) # 创建界面 mainLayout = QVBoxLayout(self); mainLayout.addLayout(operatorLayout); mainLayout.addWidget(self.tableView); mainLayout.addLayout(statusLayout); self.setLayout(mainLayout) # 设置表格 def setTableView(self): print('*** step2 SetTableView' ) # 声明查询模型 self.queryModel = QSqlQueryModel(self) # 设置当前页 self.currentPage = 1; # 得到总记录数 self.totalRecrodCount = self.getTotalRecordCount() # 得到总页数 self.totalPage = self.getPageCount() # 刷新状态 self.updateStatus() # 设置总页数文本 self.setTotalPageLabel() # 设置总记录数 self.setTotalRecordLabel() # 记录查询 self.recordQuery(0) # 设置模型 self.tableView.setModel(self.queryModel) print('totalRecrodCount=' + str(self.totalRecrodCount) ) print('totalPage=' + str(self.totalPage) ) # 设置表格表头 self.queryModel.setHeaderData(0,Qt.Horizontal,"编号") self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名") self.queryModel.setHeaderData(2,Qt.Horizontal,"性别") self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄") self.queryModel.setHeaderData(4,Qt.Horizontal,"院系") # 得到记录数 def getTotalRecordCount(self): self.queryModel.setQuery("select * from student") rowCount = self.queryModel.rowCount() print('rowCount=' + str(rowCount) ) return rowCount # 得到页数 def getPageCount(self): if self.totalRecrodCount % self.PageRecordCount == 0 : return (self.totalRecrodCount / self.PageRecordCount ) else : return (self.totalRecrodCount / self.PageRecordCount + 1) # 记录查询 def recordQuery(self, limitIndex ): szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) ) print('query sql=' + szQuery ) self.queryModel.setQuery(szQuery) # 刷新状态 def updateStatus(self): szCurrentText = ("当前第%d页" % self.currentPage ) self.currentPageLabel.setText( szCurrentText ) #设置按钮是否可用 if self.currentPage == 1 : self.prevButton.setEnabled( False ) self.nextButton.setEnabled( True ) elif self.currentPage == self.totalPage : self.prevButton.setEnabled( True ) self.nextButton.setEnabled( False ) else : self.prevButton.setEnabled( True ) self.nextButton.setEnabled( True ) # 设置总数页文本 def setTotalPageLabel(self): szPageCountText = ("总共%d页" % self.totalPage ) self.totalPageLabel.setText(szPageCountText) # 设置总总记录数 def setTotalRecordLabel(self): szTotalRecordText = ("共%d条" % self.totalRecrodCount ) print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText ) self.totalRecordLabel.setText(szTotalRecordText) # 前一页按钮按下 def onPrevButtonClick(self): print('*** onPrevButtonClick '); limitIndex = (self.currentPage - 2) * self.PageRecordCount self.recordQuery( limitIndex) self.currentPage -= 1 self.updateStatus() # 后一页按钮按下 def onNextButtonClick(self): print('*** onNextButtonClick '); limitIndex = self.currentPage * self.PageRecordCount self.recordQuery( limitIndex) self.currentPage += 1 self.updateStatus() # 转到页按钮按下 def onSwitchPageButtonClick(self): # 得到输入字符串 szText = self.switchPageLineEdit.text() #数字正则表达式 pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$') match = pattern.match(szText) # 判断是否为数字 if not match : QMessageBox.information(self, "提示", "请输入数字" ) return # 是否为空 if szText == '' : QMessageBox.information(self, "提示" , "请输入跳转页面" ) return #得到页数 pageIndex = int(szText) #判断是否有指定页 if pageIndex > self.totalPage or pageIndex < 1 : QMessageBox.information(self, "提示", "没有指定的页面,请重新输入" ) return #得到查询起始行号 limitIndex = (pageIndex-1) * self.PageRecordCount #记录查询 self.recordQuery(limitIndex); #设置当前页 self.currentPage = pageIndex #刷新状态 self.updateStatus();
class Window(QMainWindow, Ui_MainWindow): def __init__(self): super().__init__() self.setupUi(self) self.model = QSqlQueryModel() self.connectToSql() # Обработка действий по меню # нажата кнопка меню "Справочники -> Подразделения" self.unit_action.triggered.connect(self.unit_dialog) # нажата кнопка меню "Справочники -> Сотрудники" self.user_action.triggered.connect(self.user_dialog) # нажата кнопка меню "Справочники -> Сервисные службы" self.services_action.triggered.connect(self.services_dialog) # Работа с основной формой # Нажата кнопка "Добавить строку" на форме self.btProblemAdd.clicked.connect(self.bt_add) # Нажата кнопка "Обновить строку" на форме self.btProblemUpdate.clicked.connect(self.bt_upd) # Нажата кнопка "Удалить строку" на форме self.btProblemDel.clicked.connect(self.bt_del) # Найти заявку self.btProblemFind.clicked.connect(self.findText) # Сбросить фильтр self.btProblemReset.clicked.connect(self.reset) def connectToSql(self): """ Открываем базу и создаем запрос и соединяем модель с таблицей """ self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) self.tableView.horizontalHeader().setSectionResizeMode( QHeaderView.ResizeToContents) self.model.setHeaderData(0, Qt.Horizontal, '№ заявки') self.model.setHeaderData(1, Qt.Horizontal, 'Дата') self.model.setHeaderData(2, Qt.Horizontal, 'Служба') self.model.setHeaderData(3, Qt.Horizontal, 'Текст заявки') self.model.setHeaderData(4, Qt.Horizontal, 'ФИО заявителя') else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def bt_add(self): """ Вызывается по нажатию кнопки "Добавить строку" """ dialog_add = Ed_win(self) # Устанавливаем пустые значения в поля на форме dialog_add.edit_text.setText = '' dialog_add.cb_ser_name.setCurrentIndex(-1) dialog_add.cb_fio.setCurrentIndex(-1) # открываем окно добавления dialog_add.show() # Если нажата кнопка ОК на форме добавления if dialog_add.exec() == QDialog.Accepted: ser = dialog_add.cb_ser_name.currentText() user = dialog_add.cb_fio.currentText() self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""SELECT s.id FROM services s where s.name =:ser""") qry1.bindValue(":ser", ser) qry1.exec() qry1.next() servId = qry1.value(0) qry1.prepare("""SELECT u.id FROM User u where u.FIO =:user""") qry1.bindValue(":user", user) qry1.exec() qry1.next() userId = qry1.value(0) qry1.prepare( """INSERT INTO def_message(idServices, text,idUser) VALUES(:idService, :text,:idUser)""") qry1.bindValue(":idService", servId) qry1.bindValue(":text", dialog_add.edit_text.text()) qry1.bindValue(":idUser", userId) qry1.exec() self.qry = QSqlQuery(self.db) self.qry.prepare("""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def bt_upd(self): """ Вызывается по нажатию кнопки "Изменить строку" """ dialog_add = Ed_win(self) # определяем индекс строки в таблице index = self.tableView.currentIndex().row() print(index) if index == -1: QMessageBox.information(self, 'Ошибка', 'Строка не выбрана') else: # определяем id строки в таблице в базе данных nom = str( self.model.data(self.model.index(index, 0), Qt.DisplayRole)) # устанавливаем текст заявки в поле edit_text dialog_add.edit_text.setText( str(self.model.data(self.model.index(index, 3), Qt.DisplayRole))) # Данные по сервисной службе index_serv = dialog_add.cb_ser_name.findText( str(self.model.data(self.model.index(index, 2), Qt.DisplayRole))) if index_serv > -1: dialog_add.cb_ser_name.setCurrentIndex(index_serv) else: dialog_add.cb_ser_name.setCurrentIndex(-1) # Данные по сотруднику, который подал заявку index_fio = dialog_add.cb_fio.findText( str(self.model.data(self.model.index(index, 4), Qt.DisplayRole))) if index_fio > -1: dialog_add.cb_fio.setCurrentIndex(index_fio) else: dialog_add.cb_fio.setCurrentIndex(-1) # ОТкрываем окно с данными для редактирования dialog_add.show() # если нажата кнопка ОК в окне для редактирования строки if dialog_add.exec() == QDialog.Accepted: ser = dialog_add.cb_ser_name.currentText() user = dialog_add.cb_fio.currentText() self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""SELECT s.id FROM services s where s.name =:ser""") qry1.bindValue(":ser", ser) qry1.exec() qry1.next() servId = qry1.value(0) qry1.prepare("""SELECT u.id FROM User u where u.FIO =:user""") qry1.bindValue(":user", user) qry1.exec() qry1.next() userId = qry1.value(0) qry1.prepare( """UPDATE def_message SET idServices=:servId, text=:txt,idUser=:userId WHERE id=:id""" ) qry1.bindValue(":servId", servId) qry1.bindValue(":txt", dialog_add.edit_text.text()) qry1.bindValue(":userId", userId) qry1.bindValue(":id", nom) qry1.exec() # снова делаем выборку данных после проведения обновления данных self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def bt_del(self): """ Вызывается по нажатию кнопки "Удалить строку" """ quit_msg = "Подтвердите удаление записи" reply = QMessageBox.question(self, 'Confirm', quit_msg, QMessageBox.Yes, QMessageBox.No) if reply == QMessageBox.Yes: index = self.tableView.currentIndex().row() nom = str( self.model.data(self.model.index(index, 0), Qt.DisplayRole)) self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""DELETE FROM def_message where id =:id""") qry1.bindValue(":id", nom) qry1.exec() self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""" ) self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def findText(self): """ Вызывается по кнопке "Найти заявку" """ self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id where d.text Like :txt """) self.qry.bindValue(":txt", '%' + self.EditFind.text() + '%') self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def reset(self): """ Вызывается по кнопке "Сбросить фильтр" """ self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id """) self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def unit_dialog(self): """ Функция выполняется при нажатии кнопки меню "Справочники -> Подразделения" :return: Открывает окно с таблицей информации по подразделениям (Unit_win) """ dialog_unit = Unit_win(self) dialog_unit.show() # Скрываем основное окно программы wnd.hide() def user_dialog(self): """ Функция выполняется при нажатии кнопки меню "Справочники -> Сотрудники" :return: Открывает окно с таблицей информации по отрудникам (User_win) """ dialog_user = User_win(self) dialog_user.show() # Скрываем основное окно программы wnd.hide() def services_dialog(self): """ Функция выполняется при нажатии кнопки меню "Справочники -> Сервисные службы" :return: Открывает окно с таблицей информации по сервисным службам (Services_win) """ dialog = Services_win(self) dialog.show() # Скрываем основное окно программы wnd.hide()
class DataGrid(QWidget): def __init__(self): super().__init__() self.setWindowTitle("分頁查詢範例") self.resize(750, 300) # 查詢模型 self.queryModel = None # 資料表 self.tableView = None # 總頁數 self.totalPageLabel = None # 目前頁 self.currentPageLabel = None # 轉到頁輸入框 self.switchPageLineEdit = None # 前一頁按鈕 self.prevButton = None # 後一頁按鈕 self.nextButton = None # 轉到頁按鈕 self.switchPageButton = None # 目前頁 self.currentPage = 0 # 總頁數 self.totalPage = 0 # 總記錄數 self.totalRecrodCount = 0 # 每頁顯示記錄數 self.PageRecordCount = 5 self.initUI() def initUI(self): # 建立視窗 self.createWindow() # 設定表格 self.setTableView() # 訊號/槽連接 self.prevButton.clicked.connect(self.onPrevButtonClick) self.nextButton.clicked.connect(self.onNextButtonClick) self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick) # 建立視窗 def createWindow(self): # 操作佈局 operatorLayout = QHBoxLayout() self.prevButton = QPushButton("前一頁") self.nextButton = QPushButton("後一頁") self.switchPageButton = QPushButton("Go") self.switchPageLineEdit = QLineEdit() self.switchPageLineEdit.setFixedWidth(40) switchPage = QLabel("轉到第") page = QLabel("頁") operatorLayout.addWidget(self.prevButton) operatorLayout.addWidget(self.nextButton) operatorLayout.addWidget(switchPage) operatorLayout.addWidget(self.switchPageLineEdit) operatorLayout.addWidget(page) operatorLayout.addWidget(self.switchPageButton) operatorLayout.addWidget(QSplitter()) # 狀態佈局 statusLayout = QHBoxLayout() self.totalPageLabel = QLabel() self.totalPageLabel.setFixedWidth(70) self.currentPageLabel = QLabel() self.currentPageLabel.setFixedWidth(70) self.totalRecordLabel = QLabel() self.totalRecordLabel.setFixedWidth(70) statusLayout.addWidget(self.totalPageLabel) statusLayout.addWidget(self.currentPageLabel) statusLayout.addWidget(QSplitter()) statusLayout.addWidget(self.totalRecordLabel) # 設定表格屬性 self.tableView = QTableView() # 表格寬度的自我調整 self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) # 建立介面 mainLayout = QVBoxLayout(self) mainLayout.addLayout(operatorLayout) mainLayout.addWidget(self.tableView) mainLayout.addLayout(statusLayout) self.setLayout(mainLayout) # 設定表格 def setTableView(self): print('*** step2 SetTableView') # 宣告查詢模型 self.queryModel = QSqlQueryModel(self) # 設定目前頁 self.currentPage = 1 # 取得總記錄數 self.totalRecrodCount = self.getTotalRecordCount() # 取得總頁數 self.totalPage = self.getPageCount() # 刷新狀態 self.updateStatus() # 設定總頁數標籤 self.setTotalPageLabel() # 設定總記錄數標籤 self.setTotalRecordLabel() # 記錄查詢 self.recordQuery(0) # 設定模型 self.tableView.setModel(self.queryModel) print('totalRecrodCount=' + str(self.totalRecrodCount)) print('totalPage=' + str(self.totalPage)) # 設定表格表頭 self.queryModel.setHeaderData(0, Qt.Horizontal, "編號") self.queryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.queryModel.setHeaderData(2, Qt.Horizontal, "性別") self.queryModel.setHeaderData(3, Qt.Horizontal, "年齡") self.queryModel.setHeaderData(4, Qt.Horizontal, "院系") # 取得記錄數 def getTotalRecordCount(self): self.queryModel.setQuery("select * from student") rowCount = self.queryModel.rowCount() print('rowCount=' + str(rowCount)) return rowCount # 取得頁數 def getPageCount(self): if self.totalRecrodCount % self.PageRecordCount == 0: return (self.totalRecrodCount / self.PageRecordCount) else: return (self.totalRecrodCount / self.PageRecordCount + 1) # 記錄查詢 def recordQuery(self, limitIndex): szQuery = ("select * from student limit %d,%d" % (limitIndex, self.PageRecordCount)) print('query sql=' + szQuery) self.queryModel.setQuery(szQuery) # 刷新狀態 def updateStatus(self): szCurrentText = ("目前第%d頁" % self.currentPage) self.currentPageLabel.setText(szCurrentText) #設定按鈕是否可用 if self.currentPage == 1: self.prevButton.setEnabled(False) self.nextButton.setEnabled(True) elif self.currentPage == self.totalPage: self.prevButton.setEnabled(True) self.nextButton.setEnabled(False) else: self.prevButton.setEnabled(True) self.nextButton.setEnabled(True) # 設取總數頁標籤 def setTotalPageLabel(self): szPageCountText = ("總共%d頁" % self.totalPage) self.totalPageLabel.setText(szPageCountText) # 設定總記錄數標籤 def setTotalRecordLabel(self): szTotalRecordText = ("共%d筆" % self.totalRecrodCount) print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText) self.totalRecordLabel.setText(szTotalRecordText) # 按下前一頁按鈕 def onPrevButtonClick(self): print('*** onPrevButtonClick ') limitIndex = (self.currentPage - 2) * self.PageRecordCount self.recordQuery(limitIndex) self.currentPage -= 1 self.updateStatus() # 按下後一頁按鈕 def onNextButtonClick(self): print('*** onNextButtonClick ') limitIndex = self.currentPage * self.PageRecordCount self.recordQuery(limitIndex) self.currentPage += 1 self.updateStatus() # 按下轉到頁按鈕 def onSwitchPageButtonClick(self): # 取得輸入字串 szText = self.switchPageLineEdit.text() # 數字規則運算式 pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$') match = pattern.match(szText) # 判斷是否為數字 if not match: QMessageBox.information(self, "提示", "請輸入數字") return # 是否為空 if szText == '': QMessageBox.information(self, "提示", "請輸入跳越頁面") return # 取得頁數 pageIndex = int(szText) # 判斷是否有指定頁 if pageIndex > self.totalPage or pageIndex < 1: QMessageBox.information(self, "提示", "沒有指定的頁面,請重新輸入") return # 取得查詢起始列號 limitIndex = (pageIndex - 1) * self.PageRecordCount # 記錄查詢 self.recordQuery(limitIndex) # 設定目前頁 self.currentPage = pageIndex # 刷新狀態 self.updateStatus()
class RecordsViewer(QDialog): def __init__(self): super(RecordsViewer, self).__init__() self.resize(800, 500) self.setWindowTitle('myOCR——我的识别历史') self.setWindowIcon(QIcon('./images/icon.png')) self.queryModel = None self.tableView = None self.currentPage = 0 self.totalPage = 0 self.totalRecord = 0 self.pageRecord = 10 self.initUI() def initUI(self): self.vbox = QVBoxLayout() self.h1box = QHBoxLayout() self.h2box = QHBoxLayout() self.searchEdit = QLineEdit() self.searchEdit.setFixedHeight(32) self.searchEdit.setFont(QFont("苏新诗柳楷繁", 15)) self.searchButton = QPushButton("查询") self.searchButton.setFixedHeight(32) self.searchButton.setFont(QFont("苏新诗柳楷繁", 15)) self.condisionComboBox = QComboBox() searchCondision = ['按识别编号查询', '按识别时间查询', '按识别类型查询', '按识别文字查询'] self.condisionComboBox.setFixedHeight(32) self.condisionComboBox.setFont(QFont("苏新诗柳楷繁", 15)) self.condisionComboBox.addItems(searchCondision) self.h1box.addWidget(self.searchEdit) self.h1box.addWidget(self.condisionComboBox) self.h1box.addWidget(self.searchButton) self.jumpToLabel = QLabel(self) self.jumpToLabel.setText("跳转到第") self.jumpToLabel.setFont(QFont("苏新诗柳楷繁", 12)) self.jumpToLabel.setFixedWidth(90) self.pageEdit = QLineEdit() self.pageEdit.setFixedWidth(30) self.pageEdit.setFont(QFont("苏新诗柳楷繁", 12)) s = "/" + str(self.totalPage) + "页" self.pageLabel = QLabel(s) self.pageLabel.setFont(QFont("苏新诗柳楷繁", 12)) self.pageLabel.setFixedWidth(40) self.jumpToButton = QPushButton(self) self.jumpToButton.setText("跳转") self.jumpToButton.setFont(QFont("苏新诗柳楷繁", 12)) self.jumpToButton.setFixedHeight(30) self.jumpToButton.setFixedWidth(60) self.prevButton = QPushButton("前一页") self.prevButton.setFont(QFont("苏新诗柳楷繁", 12)) self.prevButton.setFixedHeight(30) self.prevButton.setFixedWidth(80) self.backButton = QPushButton("后一页") self.backButton.setFont(QFont("苏新诗柳楷繁", 12)) self.backButton.setFixedHeight(30) self.backButton.setFixedWidth(80) self.detailbutton = QPushButton(self) self.detailbutton.setText("详细信息") self.detailbutton.setFixedWidth(90) self.detailbutton.setFont(QFont("苏新诗柳楷繁", 12)) self.detailbutton.clicked.connect(self.detailInfo) self.hbox = QHBoxLayout() self.hbox.addWidget(self.jumpToLabel) self.hbox.addWidget(self.pageEdit) self.hbox.addWidget(self.pageLabel) self.hbox.addWidget(self.jumpToButton) self.hbox.addStretch(1) self.hbox.addWidget(self.detailbutton) self.hbox.addStretch(1) self.hbox.addWidget(self.prevButton) self.hbox.addWidget(self.backButton) widget = QWidget() widget.setLayout(self.hbox) widget.setFixedWidth(600) self.h2box.addWidget(widget) self.db = QSqlDatabase.addDatabase("QSQLITE") self.db.setDatabaseName('./db/myOCR.db') self.db.open() self.tableView = QTableView() self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers) self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.tableView.setFont(QFont("苏新诗柳楷繁", 12)) self.tableView.horizontalHeader().setFont(QFont("苏新诗柳楷繁", 12)) self.queryModel = QSqlQueryModel() self.searchButtonClicked() self.tableView.setModel(self.queryModel) self.queryModel.setHeaderData(0, Qt.Horizontal, "识别编号") self.queryModel.setHeaderData(1, Qt.Horizontal, "识别时间") self.queryModel.setHeaderData(2, Qt.Horizontal, "识别类型") self.queryModel.setHeaderData(3, Qt.Horizontal, "识别文字") self.vbox.addLayout(self.h1box) self.vbox.addWidget(self.tableView) self.vbox.addLayout(self.h2box) self.setLayout(self.vbox) self.searchButton.clicked.connect(self.searchButtonClicked) self.prevButton.clicked.connect(self.prevButtonClicked) self.backButton.clicked.connect(self.backButtonClicked) self.jumpToButton.clicked.connect(self.jumpToButtonClicked) self.searchEdit.returnPressed.connect(self.searchButtonClicked) def setButtonStatus(self): if (self.currentPage == self.totalPage): self.prevButton.setEnabled(True) self.backButton.setEnabled(False) if (self.currentPage == 1): self.backButton.setEnabled(True) self.prevButton.setEnabled(False) if (self.currentPage < self.totalPage and self.currentPage > 1): self.prevButton.setEnabled(True) self.backButton.setEnabled(True) def getTotalRecordCount(self): self.queryModel.setQuery( "select RecordId,RecordTime,RecordType,RecordText from records") self.totalRecord = self.queryModel.rowCount() return def getPageCount(self): self.getTotalRecordCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) return def recordQuery(self, index): conditionChoice = self.condisionComboBox.currentText() if (conditionChoice == "按识别编号查询"): conditionChoice = 'RecordId' elif (conditionChoice == "按识别时间查询"): conditionChoice = 'RecordTime' elif (conditionChoice == "按识别类型查询"): conditionChoice = 'RecordType' else: conditionChoice = 'RecordText' if (self.searchEdit.text() == ""): queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC" self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d " % (conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return temp = self.searchEdit.text() s = '%' for i in range(0, len(temp)): s = s + temp[i] + "%" queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc" % (conditionChoice, s, conditionChoice)) self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() if (self.totalRecord == 0): print( QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)) queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC" self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d " % (conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc limit %d,%d " % (conditionChoice, s, conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return def searchButtonClicked(self): self.currentPage = 1 self.pageEdit.setText(str(self.currentPage)) self.getPageCount() s = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(s) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return def prevButtonClicked(self): self.currentPage -= 1 if (self.currentPage <= 1): self.currentPage = 1 self.pageEdit.setText(str(self.currentPage)) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return def backButtonClicked(self): self.currentPage += 1 if (self.currentPage >= int(self.totalPage)): self.currentPage = int(self.totalPage) self.pageEdit.setText(str(self.currentPage)) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return def jumpToButtonClicked(self): if (self.pageEdit.text().isdigit()): self.currentPage = int(self.pageEdit.text()) if (self.currentPage > self.totalPage): self.currentPage = self.totalPage if (self.currentPage <= 1): self.currentPage = 1 else: self.currentPage = 1 index = (self.currentPage - 1) * self.pageRecord self.pageEdit.setText(str(self.currentPage)) self.recordQuery(index) return def detailInfo(self): index_ = self.tableView.currentIndex().row() if (index_ == -1): print( QMessageBox.warning(self, "警告", "您没有选中任何记录", QMessageBox.Yes, QMessageBox.Yes)) return else: str = self.queryModel.data(self.queryModel.index(index_, 0)) type = self.queryModel.data(self.queryModel.index(index_, 2)) if (type == '身份证识别'): self.recordDetail_id(str) else: self.recordDetail(str) def recordDetail(self, RecordId): recorddetaildialog = RecordDetailDialog(RecordId) recorddetaildialog.show() recorddetaildialog.exec_() def recordDetail_id(self, RecordId): recorddetail_id = RecordDetailDialog_id(RecordId) recorddetail_id.show() recorddetail_id.exec_()
def dbToTableView(self,commandSQL): try: QApplication.processEvents() if QSqlDatabase.contains("qt_sql_default_connection"): db = QSqlDatabase.database("qt_sql_default_connection") else: db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName(self.dbPath) db.open() projectModel = QSqlQueryModel() projectModel.setQuery(commandSQL, db) projectModel.setHeaderData(0, Qt.Horizontal, 'پلاک') projectModel.setHeaderData(1, Qt.Horizontal, 'متقاضی') projectModel.setHeaderData(2, Qt.Horizontal, 'نوع انجام کار') projectModel.setHeaderData(3, Qt.Horizontal, 'تاریخ بازدید') projectModel.setHeaderData(4, Qt.Horizontal, 'ساعت بازدید') projectModel.setHeaderData(5, Qt.Horizontal, 'نقشه بردار') projectModel.setHeaderData(6, Qt.Horizontal, 'نماینده') projectModel.setHeaderData(7, Qt.Horizontal, 'تاریخ ثبت') projectModel.setHeaderData(8, Qt.Horizontal, 'توضیحات') self.ui.tableView_result.setModel(projectModel) self.rowCount = projectModel.rowCount() self.tableResult = projectModel db.close() QApplication.processEvents() except: self.errorM('مشکل در ارتباط با دیتابیس\n {}')
def set_model(self): if user_id is None: return if not self.db.connect().open(): return print('Нет подключения к бд') sql = '{CALL dbo.operation_info_for_current_user(' + str(user_id) + ')}' query = QSqlQuery(sql, self.db.connect()) model = QSqlQueryModel() model.setQuery(query) model.setHeaderData(0, Qt.Horizontal, "Пользователь") model.setHeaderData(1, Qt.Horizontal, "IP") model.setHeaderData(2, Qt.Horizontal, "Дата операции") model.setHeaderData(3, Qt.Horizontal, "В ожидании") model.setHeaderData(4, Qt.Horizontal, "В работе") model.setHeaderData(5, Qt.Horizontal, "Выполнено") model.setHeaderData(6, Qt.Horizontal, "Выполнено с ошибкой") self.ui.tableView.setModel(model) self.ui.tableView.show()
class OrderController(QObject): def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow): super().__init__() self.form = form self.orderTable = orderTable self.addButton = addButton self.editButton = editButton self.deleteButton = deleteButton self.dbase = dbase self.mainwindow = mainwindow form.currentRecordChanged.connect(self.recordChanged) form.recordInserted.connect(lambda: self.recordChanged(None)) form.recordDeleted.connect(lambda: self.recordChanged(None)) if form.only_select: self.addButton.setEnabled(False) self.editButton.setEnabled(False) self.deleteButton.setEnabled(False) self.addButton.clicked.connect(self.addButtonClicked) self.editButton.clicked.connect(self.editButtonClicked) self.deleteButton.clicked.connect(self.deleteButtonClicked) def recordChanged(self, record): #print("record changed", record) if record is None: for button in (self.addButton, self.editButton, self.deleteButton): button.setEnabled(False) self.orderTable.setModel(None) self._hiddingHack(True) else: if not self.form.only_select: self.addButton.setEnabled(True) self.detailModel = QSqlQueryModel() query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \ detail.price as sole_price, detail.price*order_detail.quantity as total_price\ FROM order_detail INNER JOIN detail \ ON order_detail.detail_id = detail.id \ WHERE order_detail.order_id={} ORDER BY article".format(record.value("id")) self.detailModel.setQuery(query) self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул") self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование") self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество") self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку") self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена") self.orderTable.setModel(self.detailModel) self.orderTable.hideColumn(0) self.orderTable.resizeColumnsToContents() self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged) if not self.detailModel.query().isActive(): print(self.detailModel.lastError().text()) self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) self._hiddingHack(False) def _hiddingHack(self, val): ui = self.mainwindow.ui if val is True: ui.client_hack.setCurrentWidget(ui.client_hide_page) ui.shop_hack.setCurrentWidget(ui.shop_hide_page) ui.emp_hack.setCurrentWidget(ui.emp_hide_page) else: ui.client_hack.setCurrentWidget(ui.client_ok_page) ui.shop_hack.setCurrentWidget(ui.shop_ok_page) ui.emp_hack.setCurrentWidget(ui.emp_ok_page) def tableSelectionChanged(self, cur, prev): if self.form.only_select: return if cur.isValid(): self.deleteButton.setEnabled(True) self.editButton.setEnabled(True) else: self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) self.addButton.setEnabled(True) def addButtonClicked(self): order = self.form.currentRecord() query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \ FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \ WHERE order_id={}))".format(order.value("id"))) details = {} while query.next(): details[query.value("name")] = query.value("id") if not details: return QMessageBox.warning(None, "Ошибка добавления", "Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.") choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:", list(details.keys()), 0, False) if not ok: return qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1) if not ok: return detail_id = details[choice] order_id = order.value("id") query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \ VALUES ({}, {}, {})".format(order_id, detail_id, qnt)) if not query.isActive(): print(query.lastError().text()) self.form.update() def editButtonClicked(self): detail = self.detailModel.record(self.orderTable.currentIndex().row()) qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", detail.value("qnt"), 0) if not ok: return order = self.form.currentRecord() if qnt > 0: query = QSqlQuery("UPDATE order_detail SET quantity={} \ WHERE order_id={} AND detail_id={}".format(qnt, order.value("id"), detail.value("id"))) else: query = QSqlQuery("DELETE FROM order_detail WHERE \ order_id={} AND detail_id={} LIMIT 1".format( order.value("id"), detail.value("id"))) query.exec_() if not query.isActive(): print(query.lastError().text()) self.form.update() def deleteButtonClicked(self): if not self.orderTable.currentIndex().isValid(): return detail = self.detailModel.record(self.orderTable.currentIndex().row()) order = self.form.currentRecord() query = QSqlQuery("DELETE FROM order_detail WHERE \ order_id={} AND detail_id={} LIMIT 1".format( order.value("id"), detail.value("id"))) query.exec_() if not query.isActive(): print(query.lastError().text()) self.form.update() def selectRow(self, row): self.form.selectRow(row) def update(self): self.form.update()
def show_demo(self): def test(x): print(len(x)) # Создаем модель sqm = QSqlQueryModel(parent=self) sqm.setQuery( 'SELECT id, lastName, firstName, patrName, birthDate, sex, notes ' 'FROM Client WHERE deleted = 0 AND deathDate IS NULL ' 'LIMIT 20') # Задаем заголовки для столбцов модели sqm.setHeaderData(1, Qt.Horizontal, 'Фамилия') sqm.setHeaderData(2, Qt.Horizontal, 'Имя') sqm.setHeaderData(3, Qt.Horizontal, 'Отчество') sqm.setHeaderData(4, Qt.Horizontal, 'Дата рождения') sqm.setHeaderData(5, Qt.Horizontal, 'Пол') sqm.setHeaderData(6, Qt.Horizontal, 'Примечание') # Задаем для таблицы только что созданную модель self.clients_table.setModel(sqm) self.clients_table.hideColumn(0) self.clients_table.resizeColumnsToContents() self.clients_table.horizontalHeader().setStretchLastSection(True) self.clients_table.horizontalHeader().setHighlightSections(False) self.clients_table.verticalHeader().hide() self.clients_table.clearSelection() self.lbl_suitable_customers_count.setText(str(sqm.rowCount())) self.lbl_selected_customers_count.setText('0') self.clients_table.selectionModel().selectionChanged.connect( self.show_selected_customers_count)
class DatabaseSearchWidget(QWidget, Ui_DatabaseSearchWidget): # Signal to be emitted when user wants item to be added signal_add_item = QtCore.pyqtSignal(int) # Signal to be emitted when user selection changes signal_current_selection = QtCore.pyqtSignal(int) def __init__(self, queries, headers, parent=None): super(DatabaseSearchWidget, self).__init__(parent) self.setupUi(self) # Setup database connection self.database = pyqt_database_connection() self.database.open() self.databaseModel = QSqlQueryModel(self) self.dataView_search_results.setModel(self.databaseModel) # Store bound queries for usage self.queries = queries self.combo_search_options.addItems(sorted(queries.keys())) # Connect signals self.dataView_search_results.selectionModel().selectionChanged.connect( self.slot_emit_selection_changed) self.dataView_search_results.doubleClicked.connect( self.slot_emit_add_item) self.pushButton_search.clicked.connect(self.update_query) self.combo_search_options.currentIndexChanged.connect( self.update_query) # Setup header for element in headers: self.databaseModel.setHeaderData(0, QtCore.Qt.Horizontal, element) self.installEventFilter(self) @QtCore.pyqtSlot() def update_query(self): """ Update the query with the search term Returns ------- None """ LOGGER.debug("Updating search query..") query = self.queries[self.combo_search_options.currentText()] # Todo: Slow query setting freezes ui self.databaseModel.setQuery( query.format(input=self.lineEdit_search_input.text().strip())) LOGGER.debug(str(self.databaseModel.query().executedQuery())) LOGGER.debug("Search complete.") def selected_id(self): """ Return the id of the currently selected item Returns ------- int """ selection = self.dataView_search_results.get_selected_rows() if len(selection) != 1: return -1 else: return int( self.databaseModel.data( self.databaseModel.index(selection[0], 0))) def eventFilter(self, source, event): if event.type() == QtCore.QEvent.KeyPress and event.key() in ( QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter): self.update_query() return True return False @QtCore.pyqtSlot() def slot_emit_selection_changed(self): """ Emit database id of the currently selected item Returns ------- None """ self.signal_current_selection.emit(self.selected_id()) @QtCore.pyqtSlot() def slot_emit_add_item(self): """ Emit database id of the currently selected item Returns ------- None """ self.signal_add_item.emit(self.selected_id())
# Устанавливаем соединение с базой данных conn = QSqlDatabase.addDatabase('QSQLITE') # Определяем путь до базы данных conn.setDatabaseName('test.sqlite3') # Открываем базу данных conn.open() T_WIND = QTableView() T_WIND.setWindowTitle("Виджет-таблица на основе модели-запроса") # Создаем модель T_QUERY = QSqlQueryModel(parent=T_WIND) T_QUERY.setQuery("SELECT * FROM vendors ORDER BY name") # Задаем заголовки для столбцов модели T_QUERY.setHeaderData(1, QtCore.Qt.Horizontal, 'Название') T_QUERY.setHeaderData(2, QtCore.Qt.Horizontal, 'Телефон') T_QUERY.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес') # Задаем для таблицы только что созданную модель T_WIND.setModel(T_QUERY) # Скрываем первый столбец, в котором выводится идентификатор T_WIND.hideColumn(0) T_WIND.setColumnWidth(1, 150) T_WIND.setColumnWidth(2, 100) T_WIND.setColumnWidth(3, 200) T_WIND.resize(470, 130) T_WIND.show() sys.exit(APP.exec_())