def dumpRows(self): rows = [] q = QSqlQuery(self.db) q.exec(self.origQueryStr) q.seek(QSql.BeforeFirstRow) while True: q.next() if q.at() == QSql.AfterLastRow: break row = [] for col in range(0, len(self.headerLabels)): row.append(q.value(col)) rows.append(row) return rows
class GenericTableModel(QStandardItemModel): rowCountChanged = pyqtSignal() db = None tableName = "" # total row count which must de displayed in the view totalRowCount = 0 # lastColumnCount = 0 # original query string before we modify it origQueryStr = QSqlQuery() # previous original query string; used to check if the query has changed prevQueryStr = '' # modified query object realQuery = QSqlQuery() items = [] lastItems = [] def __init__(self, tableName, headerLabels): self.tableName = tableName self.headerLabels = headerLabels self.lastColumnCount = len(self.headerLabels) QStandardItemModel.__init__(self, 0, self.lastColumnCount) self.setHorizontalHeaderLabels(self.headerLabels) #Some QSqlQueryModel methods must be mimiced so that this class can serve as a drop-in replacement #mimic QSqlQueryModel.query() def query(self): return self #mimic QSqlQueryModel.query().lastQuery() def lastQuery(self): return self.origQueryStr #mimic QSqlQueryModel.query().lastError() def lastError(self): return self.realQuery.lastError() #mimic QSqlQueryModel.clear() def clear(self): pass def data(self, index, role=QtCore.Qt.DisplayRole): if role == QtCore.Qt.DisplayRole: items_count = len(self.items) if index.isValid( ) and items_count > 0 and index.row() < items_count: return self.items[index.row()][index.column()] return QStandardItemModel.data(self, index, role) # set columns based on query's fields def setModelColumns(self, newColumns): self.headerLabels = [] self.removeColumns(0, self.lastColumnCount) self.setHorizontalHeaderLabels(self.headerLabels) for col in range(0, newColumns): self.headerLabels.append(self.realQuery.record().fieldName(col)) self.lastColumnCount = newColumns self.setHorizontalHeaderLabels(self.headerLabels) self.setColumnCount(len(self.headerLabels)) def setQuery(self, q, db): self.origQueryStr = q self.db = db #print("q:", q) if self.prevQueryStr != self.origQueryStr: self.realQuery = QSqlQuery(q, db) self.realQuery.exec_() self.realQuery.last() queryRows = max(0, self.realQuery.at() + 1) self.totalRowCount = queryRows self.setRowCount(self.totalRowCount) # update view's columns queryColumns = self.realQuery.record().count() if queryColumns != self.lastColumnCount: self.setModelColumns(queryColumns) self.prevQueryStr = self.origQueryStr self.rowCountChanged.emit() def nextRecord(self, offset): cur_pos = self.realQuery.at() q.seek(max(cur_pos, cur_pos + offset)) def prevRecord(self, offset): cur_pos = self.realQuery.at() q.seek(min(cur_pos, cur_pos - offset)) # refresh the viewport with data from the db. def refreshViewport(self, scrollValue, maxRowsInViewport, force=False): # set records position to last, in order to get correctly the number of # rows. self.realQuery.last() rowsFound = max(0, self.realQuery.at() + 1) if scrollValue == 0 or self.realQuery.at() == QSql.BeforeFirstRow: self.realQuery.seek(QSql.BeforeFirstRow) elif self.realQuery.at() == QSql.AfterLastRow: self.realQuery.seek(rowsFound - maxRowsInViewport) else: self.realQuery.seek(min(scrollValue - 1, self.realQuery.at())) upperBound = min(maxRowsInViewport, rowsFound) self.setRowCount(self.totalRowCount) # only visible rows will be filled with data, and only if we're not # updating the viewport already. if upperBound > 0 or self.realQuery.at() < 0: self.fillRows(self.realQuery, upperBound, force) def fillRows(self, q, upperBound, force=False): rowsLabels = [] self.setVerticalHeaderLabels(rowsLabels) self.items = [] cols = [] self.blockSignals(True) #don't trigger setItem's signals for each cell, instead emit dataChanged for all cells for x in range(0, upperBound): q.next() if q.at() < 0: # if we don't set query to a valid record here, it gets stucked # forever at -2/-1. q.seek(upperBound) break rowsLabels.append(str(q.at() + 1)) cols = [] for col in range(0, len(self.headerLabels)): cols.append(str(q.value(col))) self.items.append(cols) self.blockSignals(False) self.setVerticalHeaderLabels(rowsLabels) if self.lastItems != self.items or force == True: self.dataChanged.emit( self.createIndex(0, 0), self.createIndex(upperBound, len(self.headerLabels))) self.lastItems = self.items del cols def dumpRows(self): rows = [] q = QSqlQuery(self.db) q.exec(self.origQueryStr) q.seek(QSql.BeforeFirstRow) while True: q.next() if q.at() == QSql.AfterLastRow: break row = [] for col in range(0, len(self.headerLabels)): row.append(q.value(col)) rows.append(row) return rows
def refreshViewport(self, value, maxRowsInViewport): q = QSqlQuery(self.db) #sequential number of topmost/bottommost rows in viewport (numbering starts from the bottom with 1 not with 0) botRowNo = max(1, self.totalRowCount - (value + maxRowsInViewport - 1)) topRowNo = min(botRowNo + maxRowsInViewport - 1, self.totalRowCount) if not self.isQueryFilter: part1, part2 = self.origQueryStr.split('ORDER') qStr = part1 + 'WHERE rowid>=' + str( botRowNo) + ' AND rowid<=' + str(topRowNo) + ' ORDER' + part2 else: self.updateDistinctIfNeeded(True) #replace query part between WHERE and ORDER qStr = self.origQueryStr.split('WHERE')[0] + ' WHERE ' actionStr = self.getActionStr() if actionStr: qStr += actionStr + " AND " #find inside the map the range(s) in which top and bottom rows are located total, offsetInRange, botRowFound, topRowFound = 0, None, False, False ranges = [{'from': 0, 'to': 0, 'hits': 0}] for i in reversed(self.map): if total + i['hits'] >= botRowNo: botRowFound = True if total + i['hits'] >= topRowNo: topRowFound = True if botRowFound and i['hits'] > 0: if i['to'] == ranges[-1]['from']: #merge two adjacent ranges ranges[-1]['from'] = i['from'] ranges[-1]['hits'] += i['hits'] else: ranges.append(i.copy()) if topRowFound: offsetInRange = i['hits'] - (topRowNo - total) break total += i['hits'] rangeStr = '' if len(ranges) > 0: rangeStr = '(' for r in ranges: rangeStr += '(rowid>' + str( r['to']) + ' AND rowid<=' + str(r['from']) + ') OR ' rangeStr = rangeStr[:-3] #remove trailing 'OR ' rangeStr += ') AND ' qStr += rangeStr filterStr = self.getFilterStr() matchStr = self.getMatch(filterStr) if filterStr else None if matchStr: qStr += matchStr + " AND " qStr = qStr[:-4] #remove trailing ' AND' qStr += ' ORDER ' + self.origQueryStr.split('ORDER')[1] q.exec(qStr) q.last() rowsFound = max(0, q.at() + 1) if not self.isQueryFilter: q.seek(QSql.BeforeFirstRow) else: #position the db cursor on topRowNo q.seek(QSql.BeforeFirstRow if offsetInRange == 0 else offsetInRange - 1) upperBound = min(maxRowsInViewport, rowsFound) self.setRowCount(upperBound) #only visible rows will be filled with data if upperBound > 0: #don't trigger setItem's signals for each cell, instead emit dataChanged for all cells self.blockSignals(True) for x in range(0, upperBound): q.next() for col in range(0, len(self.headerLabels)): self.setItem(x, col, QStandardItem(q.value(col))) self.blockSignals(False) self.dataChanged.emit( self.createIndex(0, 0), self.createIndex(upperBound, len(self.headerLabels)))
class ConnectionsTableModel(QStandardItemModel): headerLabels = [ 'Time', 'Node', 'Action', 'Destination', 'Protocol', 'Process', 'Rule' ] rowCountChanged = pyqtSignal() #max rowid in the db; starts with 1, not with 0 maxRowId = 0 #previous total number of rows in the db when the filter was applied prevFiltRowCount = 0 #total number of rows in the db when the filter was not applied prevNormRowCount = 0 #total row count which must de displayed in the view totalRowCount = 0 #new rows which must be added to the top of the rows displayed in the view prependedRowCount = 0 db = None #original query string before we modify it origQueryStr = QSqlQuery() #modified query object realQuery = QSqlQuery() #previous original query string; used to check if the query has changed prevQueryStr = '' #whether or not the original query has a filter (a WHERE condition) isQueryFilter = False limit = None #a map for fast lookup or rows when filter is enabled #contains ranges of rowids and count of filter hits #range format {'from': <rowid>, 'to': <rowid>, 'hits':<int>} #including the 'from' rowid up to but NOT including the 'to' rowid map = [] rangeSize = 1000 #all unique/distinct values for each column will be stored here distinct = { 'process': [], 'dst_host': [], 'dst_ip': [], 'dst_port': [], 'rule': [], 'node': [], 'protocol': [] } #what was the last rowid\time when the distinct value were updates distinctLastRowId = 0 distinctLastUpdateTime = time.time() def __init__(self): QStandardItemModel.__init__(self, 0, len(self.headerLabels)) self.setHorizontalHeaderLabels(self.headerLabels) #Some QSqlQueryModel methods must be mimiced so that this class can serve as a drop-in replacement #mimic QSqlQueryModel.query() def query(self): return self #mimic QSqlQueryModel.query().lastQuery() def lastQuery(self): return self.origQueryStr #mimic QSqlQueryModel.query().lastError() def lastError(self): return self.realQuery.lastError() #mimic QSqlQueryModel.clear() def clear(self): pass def setQuery(self, q, db): self.origQueryStr = q self.db = db maxRowIdQuery = QSqlQuery(db) maxRowIdQuery.setForwardOnly(True) maxRowIdQuery.exec("SELECT MAX(rowid) FROM connections") maxRowIdQuery.first() value = maxRowIdQuery.value(0) self.maxRowId = 0 if value == '' else int(value) self.updateDistinctIfNeeded() self.limit = int( q.split(' ')[-1]) if q.split(' ')[-2] == 'LIMIT' else None self.isQueryFilter = True if ("LIKE '%" in q and "LIKE '% %'" not in q) or 'Action = "' in q else False self.realQuery = QSqlQuery(db) isTotalRowCountChanged = False isQueryChanged = False if self.prevQueryStr != q: isQueryChanged = True if self.isQueryFilter: if isQueryChanged: self.buildMap() largestRowIdInMap = self.map[0]['from'] newRowsCount = self.maxRowId - largestRowIdInMap self.prependedRowCount = 0 if newRowsCount > 0: starttime = time.time() self.realQuery.setForwardOnly(True) for offset in range(0, newRowsCount, self.rangeSize): lowerBound = largestRowIdInMap + offset upperBound = min(lowerBound + self.rangeSize, self.maxRowId) part1, part2 = q.split('ORDER') qStr = part1 + 'AND rowid>' + str( lowerBound) + ' AND rowid<=' + str( upperBound) + ' ORDER' + part2 self.realQuery.exec(qStr) self.realQuery.last() rowsInRange = max(0, self.realQuery.at() + 1) if self.map[0]['from'] - self.map[0]['to'] < self.rangeSize: #consolidate with the previous range; we don't want many small ranges self.map[0]['from'] = upperBound self.map[0]['hits'] += rowsInRange else: self.map.insert( 0, { 'from': upperBound, 'to': lowerBound, 'hits': rowsInRange }) self.prependedRowCount += rowsInRange if time.time() - starttime > 0.5: #don't freeze the UI when fetching too many recent rows break self.totalRowCount = 0 for i in self.map: self.totalRowCount += i['hits'] if self.totalRowCount != self.prevFiltRowCount: isTotalRowCountChanged = True self.prevFiltRowCount = self.totalRowCount else: #self.isQueryFilter == False self.prependedRowCount = self.maxRowId - self.prevNormRowCount self.totalRowCount = self.maxRowId if self.totalRowCount != self.prevNormRowCount: isTotalRowCountChanged = True self.prevNormRowCount = self.totalRowCount self.prevQueryStr = self.origQueryStr if isTotalRowCountChanged or self.prependedRowCount > 0 or isQueryChanged: self.rowCountChanged.emit() #fill self.map with data def buildMap(self): self.map = [] q = QSqlQuery(self.db) q.setForwardOnly(True) self.updateDistinctIfNeeded(True) filterStr = self.getFilterStr() actionStr = self.getActionStr() #we only want to know the count of matching rows qStr = "SELECT COUNT(*) from connections WHERE (rowid> :lowerBound AND rowid<= :upperBound)" if actionStr: qStr += ' AND ' + actionStr matchStr = self.getMatch(filterStr) if filterStr else None if matchStr: qStr += ' AND ' + matchStr qStr += ' LIMIT ' + str(self.limit) if self.limit else '' q.prepare(qStr) totalRows = 0 for offset in range(self.maxRowId, -1, -self.rangeSize): upperBound = offset lowerBound = max(0, upperBound - self.rangeSize) if (not filterStr and actionStr) or (filterStr and matchStr): #either 1) only action was present or 2) filter which has a match (with or without action) q.bindValue(":lowerBound", str(lowerBound)) q.bindValue(":upperBound", str(upperBound)) q.exec_() q.first() rowsInRange = int(q.value(0)) else: rowsInRange = 0 totalRows += rowsInRange self.map.append({ 'from': upperBound, 'to': lowerBound, 'hits': rowsInRange }) if self.limit and totalRows >= self.limit: break #periodically keep track of all distinct values for each column #this is needed in order to build efficient queries when the filter is applied def updateDistinctIfNeeded(self, force=False): if (not force and (time.time() - self.distinctLastUpdateTime) < 10 ) or self.maxRowId == self.distinctLastRowId: return if (self.maxRowId < self.distinctLastRowId): #the db has been cleared, re-init the values self.distinctLastRowId = 0 self.distinct = { 'process': [], 'dst_host': [], 'dst_ip': [], 'dst_port': [], 'rule': [], 'node': [], 'protocol': [] } q = QSqlQuery(self.db) q.setForwardOnly(True) for column in self.distinct.keys(): q.exec('SELECT DISTINCT ' + column + ' FROM connections WHERE rowid>' + str(self.distinctLastRowId) + ' AND rowid<=' + str(self.maxRowId)) while q.next(): if q.value(0) not in self.distinct[column]: self.distinct[column].append(q.value(0)) self.distinctLastRowId = self.maxRowId self.distinctLastUpdateTime = time.time() #refresh the viewport with data from the db #"value" is vertical scrollbar's value def refreshViewport(self, value, maxRowsInViewport): q = QSqlQuery(self.db) #sequential number of topmost/bottommost rows in viewport (numbering starts from the bottom with 1 not with 0) botRowNo = max(1, self.totalRowCount - (value + maxRowsInViewport - 1)) topRowNo = min(botRowNo + maxRowsInViewport - 1, self.totalRowCount) if not self.isQueryFilter: part1, part2 = self.origQueryStr.split('ORDER') qStr = part1 + 'WHERE rowid>=' + str( botRowNo) + ' AND rowid<=' + str(topRowNo) + ' ORDER' + part2 else: self.updateDistinctIfNeeded(True) #replace query part between WHERE and ORDER qStr = self.origQueryStr.split('WHERE')[0] + ' WHERE ' actionStr = self.getActionStr() if actionStr: qStr += actionStr + " AND " #find inside the map the range(s) in which top and bottom rows are located total, offsetInRange, botRowFound, topRowFound = 0, None, False, False ranges = [{'from': 0, 'to': 0, 'hits': 0}] for i in reversed(self.map): if total + i['hits'] >= botRowNo: botRowFound = True if total + i['hits'] >= topRowNo: topRowFound = True if botRowFound and i['hits'] > 0: if i['to'] == ranges[-1]['from']: #merge two adjacent ranges ranges[-1]['from'] = i['from'] ranges[-1]['hits'] += i['hits'] else: ranges.append(i.copy()) if topRowFound: offsetInRange = i['hits'] - (topRowNo - total) break total += i['hits'] rangeStr = '' if len(ranges) > 0: rangeStr = '(' for r in ranges: rangeStr += '(rowid>' + str( r['to']) + ' AND rowid<=' + str(r['from']) + ') OR ' rangeStr = rangeStr[:-3] #remove trailing 'OR ' rangeStr += ') AND ' qStr += rangeStr filterStr = self.getFilterStr() matchStr = self.getMatch(filterStr) if filterStr else None if matchStr: qStr += matchStr + " AND " qStr = qStr[:-4] #remove trailing ' AND' qStr += ' ORDER ' + self.origQueryStr.split('ORDER')[1] q.exec(qStr) q.last() rowsFound = max(0, q.at() + 1) if not self.isQueryFilter: q.seek(QSql.BeforeFirstRow) else: #position the db cursor on topRowNo q.seek(QSql.BeforeFirstRow if offsetInRange == 0 else offsetInRange - 1) upperBound = min(maxRowsInViewport, rowsFound) self.setRowCount(upperBound) #only visible rows will be filled with data if upperBound > 0: #don't trigger setItem's signals for each cell, instead emit dataChanged for all cells self.blockSignals(True) for x in range(0, upperBound): q.next() for col in range(0, len(self.headerLabels)): self.setItem(x, col, QStandardItem(q.value(col))) self.blockSignals(False) self.dataChanged.emit( self.createIndex(0, 0), self.createIndex(upperBound, len(self.headerLabels))) #form a condition string for the query: if filterStr is (partially) present in any of the columns def getMatch(self, filterStr): match = {} for column in self.distinct.keys(): match[column] = [] for value in self.distinct[column]: if filterStr in value: match[column].append(value) matchStr = None if any([match[col] for col in match]): matchStr = '( ' if match['process']: matchStr += "process IN ('" + "','".join( match['process']) + "') OR" if match['dst_host']: matchStr += " (dst_host != '' AND dst_host IN ('" + "','".join( match['dst_host']) + "') ) OR" if match['dst_ip']: matchStr += " (dst_host = '' AND dst_ip IN ('" + "','".join( match['dst_ip']) + "') ) OR" if match['dst_port']: matchStr += " dst_port IN ('" + "','".join( match['dst_port']) + "') OR" if match['rule']: matchStr += " rule IN ('" + "','".join(match['rule']) + "') OR" if match['node']: matchStr += " node IN ('" + "','".join(match['node']) + "') OR" if match['protocol']: matchStr += " protocol IN ('" + "','".join( match['protocol']) + "') OR" matchStr = matchStr[:-2] #remove trailing 'OR' matchStr += ' )' return matchStr #extract the filter string if any def getFilterStr(self): filterStr = None if "LIKE '%" in self.origQueryStr: filterStr = self.origQueryStr.split("LIKE '%")[1].split("%")[0] return filterStr #extract the action string if any def getActionStr(self): actionStr = None if 'WHERE Action = "' in self.origQueryStr: actionCond = self.origQueryStr.split('WHERE Action = "')[1].split( '"')[0] actionStr = "action = '" + actionCond + "'" return actionStr def dumpRows(self): rows = [] q = QSqlQuery(self.db) q.exec(self.origQueryStr) q.seek(QSql.BeforeFirstRow) while True: q.next() if q.at() == QSql.AfterLastRow: break row = [] for col in range(0, len(self.headerLabels)): row.append(q.value(col)) rows.append(row) return rows
def db_select(self): # 数据库设置 self.db = QSqlDatabase.addDatabase('QMYSQL') # 数据库设置 self.db.setHostName('localhost') self.db.setDatabaseName('bigpeng') self.db.setUserName('root') self.db.setPassword('') if not self.db.open(): QMessageBox.critical(None, ('无法打开数据库'), ('无法建立连接,需要配置'), QMessageBox.Cancel) return False query = QSqlQuery() text = self.comboBox_2.currentText() # 获取选择的哪块土地 print(text) query.exec("select * from df_status WHERE place='%s'" % text) i = 0 self.tableWidget.setColumnWidth(3, 130) # 设置table表格最后一个列的宽度 # self.tableWidget.setRowHeight(3,130)# 设置table表格最后一个列的宽度 #设置表格标题的方法 # self.tableWidget.setHorizontalHeaderLabels(('第一列','第二列'))# 括号中为Iterable对象,即可迭代,其中可以为列表[],元组(),集合{} # self.tableWidget.setVerticalHeaderLabels(('第一行', '第二行'))# 这里使用元组,有几行或者几列,元组内有几个字符串 # self.tableWidget.insertColumn(0) #插入到第一列 # self.tableWidget.insertRow(0) while (query.next()): # 判断查询语句集合是否有下一条 self.tableWidget.setItem(i, 0, QTableWidgetItem(str(query.value(8)))) self.tableWidget.setItem(i, 1, QTableWidgetItem('板载温度')) self.tableWidget.setItem(i, 2, QTableWidgetItem(str(query.value(4)))) self.tableWidget.setItem( i, 3, QTableWidgetItem( str(query.value(2).toString("yyyy/MM/dd HH:mm:ss")))) self.tableWidget.setItem(i + 1, 0, QTableWidgetItem(str(query.value(8)))) self.tableWidget.setItem(i + 1, 1, QTableWidgetItem('电压')) self.tableWidget.setItem(i + 1, 2, QTableWidgetItem(str(query.value(5)))) self.tableWidget.setItem( i + 1, 3, QTableWidgetItem( str(query.value(2).toString("yyyy/MM/dd HH:mm:ss")))) self.tableWidget.setItem(i + 2, 0, QTableWidgetItem(str(query.value(8)))) self.tableWidget.setItem(i + 2, 1, QTableWidgetItem('电量')) self.tableWidget.setItem(i + 2, 2, QTableWidgetItem(str(query.value(6)))) self.tableWidget.setItem( i + 2, 3, QTableWidgetItem( str(query.value(2).toString("yyyy/MM/dd HH:mm:ss")))) self.tableWidget.setItem(i + 3, 0, QTableWidgetItem(str(query.value(8)))) self.tableWidget.setItem(i + 3, 1, QTableWidgetItem('放置位置')) self.tableWidget.setItem(i + 3, 2, QTableWidgetItem(str(query.value(7)))) self.tableWidget.setItem( i + 3, 3, QTableWidgetItem( str(query.value(2).toString("yyyy/MM/dd HH:mm:ss")))) i = i + 4 print(str(query.value(8)), query.at())