· 6 years ago · Jul 08, 2019, 08:20 PM
1import sys
2import re
3from PyQt5 import QtWidgets, QtGui, QtCore, QtSql
4
5db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
6db.setDatabaseName("test_db15.db");
7MY_TABLE = "person"
8#db.setDatabaseName("promon.db");
9#MY_TABLE = "md_vendor"
10#db.setDatabaseName("psm.db");
11#MY_TABLE = "patient"
12modelQuery = QtSql.QSqlQueryModel()
13modelTable = QtSql.QSqlRelationalTableModel()
14
15_hitsCount = 0
16_totalCount = 0
17_selectedCount = 0
18
19def _human_key(key):
20 parts = re.split(r'(\d*\.\d+|\d+)', key)
21 return tuple((e.swapcase() if i % 2 == 0 else float(e))
22 for i, e in enumerate(parts))
23
24class dlgDetail(QtWidgets.QDialog):
25 def __init__(self, index, parent):
26 QtWidgets.QDialog.__init__(self, parent=None)
27 self.setupUi(self)
28
29 # connect signals
30 self.accepted.connect(self.dlgDetail_accepted)
31 self.rejected.connect(self.dlgDetail_rejected)
32
33 self.pbNew.clicked.connect(self.on_pbNew_clicked)
34 self.pbSave.clicked.connect(self.on_pbSave_clicked)
35 self.pbDel.clicked.connect(self.on_pbDel_clicked)
36 self.pbCancel.clicked.connect(self.on_pbCancel_clicked)
37
38 proxy = parent.treeView.model()
39 sourceIdx = proxy.mapToSource(index)
40 #ix = proxy.index(index.row(), 0) # column which contains the id
41
42 self.cur_row = sourceIdx.row()
43 self.parent = parent
44
45 # load selected dataset
46 record = modelTable.record(self.cur_row)
47 record.setGenerated(self.cur_row, False)
48
49 self.row_id = record.value("id")
50 persId = record.value("persId")
51 lastName = record.value("lastName")
52 firstName = record.value("firstName")
53
54 # display dataset
55 self.efId.setText(persId)
56 self.efLastName.setText(lastName)
57 self.efFirstName.setText(firstName)
58
59 def clear_inputs(self):
60 self.efId.setText("")
61 self.efLastName.setText("")
62 self.efFirstName.setText("")
63
64 def setupUi(self, parent):
65 self.setGeometry(300,200,350,200)
66 self.center()
67
68 lblId = QtWidgets.QLabel(self)
69 lblId.setText("ID:")
70
71 lblLastName = QtWidgets.QLabel(self)
72 lblLastName.setText("Last Name:")
73 lblLastName.move(0,30)
74
75 lblFirstName = QtWidgets.QLabel(self)
76 lblFirstName.setText("First Name:")
77 lblFirstName.move(0,60)
78
79 self.efId = QtWidgets.QLineEdit(self)
80 self.efId.move(100,0)
81
82 self.efLastName = QtWidgets.QLineEdit(self)
83 self.efLastName.move(100,30)
84
85 self.efFirstName = QtWidgets.QLineEdit(self)
86 self.efFirstName.move(100,60)
87
88 self.pbNew = QtWidgets.QPushButton("New", self)
89 self.pbNew.move(0,150)
90
91 self.pbSave = QtWidgets.QPushButton("Save", self)
92 self.pbSave.move(75,150)
93
94 self.pbDel = QtWidgets.QPushButton("Del", self)
95 self.pbDel.move(150,150)
96
97 self.pbCancel = QtWidgets.QPushButton("Cancel", self)
98 self.pbCancel.move(225,150)
99
100 def dlgDetail_accepted(self):
101 print("dlgDetail_rejected() called.")
102
103 def dlgDetail_rejected(self):
104 print("dlgDetail_rejected() called.")
105
106 @QtCore.pyqtSlot()
107 def on_pbCancel_clicked(self):
108 print("on_pbCancel_clicked() called.")
109 self.reject()
110
111 @QtCore.pyqtSlot()
112 def on_pbSave_clicked(self):
113 print("on_pbSave_clicked() called.")
114 self.parent.update_dataset(self.cur_row, self.row_id, self.efId.text(), self.efLastName.text(), self.efFirstName.text(), 1)
115 #self.accept()
116
117 @QtCore.pyqtSlot()
118 def on_pbNew_clicked(self):
119 self.clear_inputs()
120 self.parent.new_dataset()
121
122 @QtCore.pyqtSlot()
123 def on_pbDel_clicked(self):
124 self.parent.del_dataset(self.cur_row)
125 self.accept()
126
127 def center(self):
128 frameGm = self.frameGeometry()
129 screen = QtWidgets.QApplication.desktop().screenNumber(QtWidgets.QApplication.desktop().cursor().pos())
130 centerPoint = QtWidgets.QApplication.desktop().screenGeometry(screen).center()
131 frameGm.moveCenter(centerPoint)
132 self.move(frameGm.topLeft())
133
134
135class FilterHeader(QtWidgets.QHeaderView):
136 filterActivated = QtCore.pyqtSignal()
137
138 def __init__(self, parent):
139 super().__init__(QtCore.Qt.Horizontal, parent)
140 self._editors = []
141 self._padding = 4
142 self.setStretchLastSection(True)
143 self.setDefaultAlignment(QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter)
144 self.setSortIndicatorShown(False)
145 self.sectionResized.connect(self.adjustPositions)
146 parent.horizontalScrollBar().valueChanged.connect(self.adjustPositions)
147
148 def setFilterBoxes(self, count):
149 while self._editors:
150 editor = self._editors.pop()
151 editor.deleteLater()
152 for index in range(count):
153 editor = QtWidgets.QLineEdit(self.parent())
154 editor.setPlaceholderText('Filter')
155 editor.setClearButtonEnabled(True)
156 editor.textChanged.connect(self.textChanged)
157
158 self._editors.append(editor)
159 self.adjustPositions()
160
161 def textChanged(self):
162 self.filterActivated.emit()
163
164 def sizeHint(self):
165 size = super().sizeHint()
166 if self._editors:
167 height = self._editors[0].sizeHint().height()
168 size.setHeight(size.height() + height + self._padding)
169 return size
170
171 def updateGeometries(self):
172 if self._editors:
173 height = self._editors[0].sizeHint().height()
174 self.setViewportMargins(0, 0, 0, height + self._padding)
175 else:
176 self.setViewportMargins(0, 0, 0, 0)
177 super().updateGeometries()
178 self.adjustPositions()
179
180 def adjustPositions(self):
181 for index, editor in enumerate(self._editors):
182 height = editor.sizeHint().height()
183 editor.move(
184 self.sectionPosition(index) - self.offset() + 2,
185 height + (self._padding // 2))
186 editor.resize(self.sectionSize(index), height)
187
188 def filterText(self, index):
189 if 0 <= index < len(self._editors):
190 return self._editors[index].text()
191 return ''
192
193 def setFilterText(self, index, text):
194 if 0 <= index < len(self._editors):
195 self._editors[index].setText(text)
196
197 def clearFilters(self):
198 print("[FilterHeader] clearFilters called.")
199 for editor in self._editors:
200 editor.clear()
201
202
203class HumanProxyModel(QtCore.QSortFilterProxyModel):
204 def lessThan(self, source_left, source_right):
205 data_left = source_left.data()
206 data_right = source_right.data()
207 if type(data_left) == type(data_right) == str:
208 return _human_key(data_left) < _human_key(data_right)
209 return super(HumanProxyModel, self).lessThan(source_left, source_right)
210
211 @property
212 def filters(self):
213 if not hasattr(self, "_filters"):
214 self._filters = []
215 return self._filters
216
217 @filters.setter
218 def filters(self, filters):
219 self._filters = filters
220 self.invalidateFilter()
221
222 global _hitsCount
223 _hitsCount = self.rowCount()
224
225 def filterAcceptsRow(self, sourceRow, sourceParent):
226 print("<filterAcceptsRow()> sourceRow: ", str(sourceRow), "sourceParent: ", str(sourceParent.row()))
227 for i, text in self.filters:
228 if 0 <= i < self.columnCount():
229 ix = self.sourceModel().index(sourceRow, i, sourceParent)
230 data = ix.data()
231 if text not in data:
232 return False
233 return True
234
235class winMain(QtWidgets.QMainWindow):
236 def __init__(self, parent=None):
237 super().__init__(parent)
238 self.setupUi()
239 self.setGeometry(300,200,700,500)
240
241 self.treeView.selectionModel().selectionChanged.connect(self.item_selection_changed_slot)
242 self.treeView.doubleClicked.connect(self.OnEditData)
243 self.center()
244 self.show()
245
246 def closeEvent(self, event):
247 print("closing main window.")
248# self.treeView.model().invalidateFilter()
249# self.treeView.header().filterActivated.disconnect()
250# self.treeView.header().clearFilters()
251# # delete model
252# #self.treeView.header().filter.modelReset.emit()
253# self.treeView.model().clear()
254#
255# modelTable.submitAll()
256 self.deleteLater()
257
258 def new_dataset(self):
259 print("new_dataset() called.")
260
261 new_row = modelTable.rowCount()+1
262 self.row_id = new_row
263 self.cur_row = new_row
264
265 print("new row: ", new_row)
266 print("cur row: ", self.cur_row)
267
268 modelTable.insertRow(new_row)
269
270 def update_dataset(self, cur_row, id, persId, lastName, firstName, country_id):
271 print("update_dataset() called. cur_row: ", cur_row, ", id: ", id)
272 record = modelTable.record()
273
274 record.setValue("id", id)
275 record.setValue("persId", persId)
276 record.setValue("lastName", lastName)
277 record.setValue("firstName", firstName)
278 record.setValue("country_id", country_id)
279
280 # Update Model
281 modelTable.setRecord(cur_row, record)
282
283 # Update Database
284 proxy = self.treeView.model()
285 proxy.dataChanged.emit(QtCore.QModelIndex(), QtCore.QModelIndex())
286 #modelTable.submitAll() # -> On success the model will be repopulated. \
287 # Any views presenting it will lose their selections.
288
289 if modelTable.submit():
290 print("success on storing data.")
291 else:
292 print("error on storing data: ", modelTable.lastError().text())
293
294 def del_dataset(self, cur_row):
295 print("del_dataset() called.")
296 modelTable.deleteRowFromTable(cur_row)
297
298 proxy = self.treeView.model()
299 proxy.dataChanged.emit(QtCore.QModelIndex(), QtCore.QModelIndex())
300
301 if modelTable.submit():
302 print("success on delete data.")
303 proxy.dataChanged.emit(QtCore.QModelIndex(), QtCore.QModelIndex())
304 modelTable.select()
305 return True
306 else:
307 print("error on deleting data: ", modelTable.lastError().text())
308 return False
309
310 def OnEditData(self, index):
311 #print("OnEditData() -> index: ", index.row()) # <- the row number that was clicked.
312
313 dlg_detail = dlgDetail(index, self)
314 dlg_detail.exec_()
315
316 def handleFilterActivated(self):
317 header = self.treeView.header() # -> QTreeView
318 # header = self.treeView.horizontalHeader() # -> QTableView
319 filters = []
320 for i in range(header.count()):
321 text = header.filterText(i)
322 if text:
323 filters.append((i, text))
324 proxy = self.treeView.model()
325 proxy.filters = filters
326
327 self.updateStatus()
328
329 QtCore.pyqtSlot()
330 def item_selection_changed_slot(self):
331 selected = self.treeView.selectionModel()
332 indexes = selected.selectedIndexes()
333
334 global _selectedCount
335 _selectedCount = len(selected.selectedRows())
336
337 self.updateStatus()
338
339 def updateStatus(self):
340 global _hitsCount, _totalCount, _selectedCount
341
342 sStatusText = f"Selected: {str(_selectedCount)} / Hits: {str(_hitsCount)} / Total: {str(_totalCount)}"
343
344 self.statusBar.showMessage(sStatusText)
345
346 def keyReleaseEvent(self, eventQKeyEvent):
347 key = eventQKeyEvent.key()
348 modifiers = QtWidgets.QApplication.keyboardModifiers()
349 if modifiers == QtCore.Qt.ShiftModifier and key == QtCore.Qt.Key_Escape:
350 self.clear_all_filters()
351
352 def keyPressEvent(self, event):
353 key = event.key()
354
355 modifiers = QtWidgets.QApplication.keyboardModifiers()
356
357 if modifiers != QtCore.Qt.ShiftModifier:
358 focus_obj = self.focusWidget()
359
360 if key == QtCore.Qt.Key_Return:
361 if type(focus_obj) == QtWidgets.QTreeView:
362 self.OnEditData(self.treeView.currentIndex())
363
364 elif key == QtCore.Qt.Key_Delete:
365 if type(focus_obj) == QtWidgets.QTreeView:
366 self.delete_dataset(self.treeView.currentIndex())
367
368 elif key == QtCore.Qt.Key_Escape:
369 if type(focus_obj) == QtWidgets.QLineEdit:
370 focus_obj.clear()
371
372 def clear_all_filters(self):
373 # clear all inputs of type QLineEdit
374 lineEdits = self.findChildren(QtWidgets.QLineEdit)
375 for lineEdit in lineEdits:
376 lineEdit.clear()
377
378 def center(self):
379 frameGm = self.frameGeometry()
380 screen = QtWidgets.QApplication.desktop().screenNumber(QtWidgets.QApplication.desktop().cursor().pos())
381 centerPoint = QtWidgets.QApplication.desktop().screenGeometry(screen).center()
382 frameGm.moveCenter(centerPoint)
383 self.move(frameGm.topLeft())
384
385 def setupUi(self):
386 self.centralwidget = QtWidgets.QWidget(self)
387 self.horizontalLayout = QtWidgets.QHBoxLayout(self.centralwidget)
388
389 self.treeView = QtWidgets.QTreeView(self.centralwidget) # -> QTreeView
390 #self.treeView = QtWidgets.QTableView(self.centralwidget) # -> QTableView
391
392 self.treeView.setRootIsDecorated(False) # -> QTreeView
393 self.treeView.setSortingEnabled(True)
394 self.treeView.setAlternatingRowColors(True)
395 self.treeView.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)
396 self.treeView.setSelectionMode(QtWidgets.QAbstractItemView.ExtendedSelection)
397 self.treeView.header().setStretchLastSection(True) # -> QTreeView
398 #self.treeView.horizontalHeader().setStretchLastSection(True) # -> QTableView
399
400 self.horizontalLayout.addWidget(self.treeView)
401 self.setCentralWidget(self.centralwidget)
402
403 header = FilterHeader(self.treeView)
404 self.treeView.setHeader(header) # -> QTreeView
405 #self.treeView.setHorizontalHeader(header) # -> QTableView
406
407 # StatusBar
408 self.statusBar = QtWidgets.QStatusBar()
409 self.setStatusBar(self.statusBar)
410
411 modelTable.setTable(MY_TABLE)
412 modelTable.setRelation(4, QtSql.QSqlRelation("country", "id", "name"));
413 #modelTable.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
414 modelTable.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
415
416 modelTable.select()
417
418# modelTable.setHeaderData(0, QtCore.Qt.Horizontal, "ID");
419# modelTable.setHeaderData(1, QtCore.Qt.Horizontal, "Last Name");
420# modelTable.setHeaderData(2, QtCore.Qt.Horizontal, "First Name");
421# modelTable.setHeaderData(3, QtCore.Qt.Horizontal, "Country");
422
423 self.treeView.setModel(modelTable)
424
425 # enable human sorting
426 proxy = HumanProxyModel(self)
427 proxy.setSourceModel(modelTable)
428 self.treeView.setModel(proxy)
429
430 # enable filtering
431 header.setFilterBoxes(modelTable.columnCount())
432 header.filterActivated.connect(self.handleFilterActivated)
433
434 # update counters
435 global _totalCount, _hitsCount
436 _totalCount = modelTable.rowCount()
437 _hitsCount = _totalCount
438 self.updateStatus()
439
440def create_sample_data():
441 modelQuery.setQuery("""CREATE TABLE IF NOT EXISTS country (
442 id INTEGER PRIMARY KEY UNIQUE,
443 name TEXT
444 )""")
445
446 modelQuery.setQuery("""CREATE TABLE IF NOT EXISTS person (
447 id INTEGER PRIMARY KEY UNIQUE,
448 persId TEXT,
449 lastName TEXT,
450 firstName TEXT,
451 country_id INTEGER,
452 FOREIGN KEY (country_id) REFERENCES country(id)
453 )""")
454
455 # create some sample data for our model
456 modelQuery.setQuery("INSERT INTO country (id, name) VALUES (1, 'Angola')")
457 modelQuery.setQuery("INSERT INTO country (id, name) VALUES (2, 'Serbia')")
458 modelQuery.setQuery("INSERT INTO country (id, name) VALUES (3, 'Georgia')")
459
460 modelQuery.setQuery("INSERT INTO person (id, persId, lastName, firstName, country_id) VALUES (1, '1001', 'Martin', 'Robert', 1)")
461 modelQuery.setQuery("INSERT INTO person (id, persId, lastName, firstName, country_id) VALUES (2, '1002', 'Smith', 'Brad', 2)")
462 modelQuery.setQuery("INSERT INTO person (id, persId, lastName, firstName, country_id) VALUES (3, '1003', 'Smith', 'Angelina', 3)")
463
464if __name__ == '__main__':
465 app = QtWidgets.QApplication(sys.argv)
466
467 create_sample_data()
468
469 window = winMain()
470 sys.exit(app.exec_())