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())
