PyQt project practice work assistant (4 use QSqlTableModel and tableView to implement Todolist)
In this part, the functions of querying, adding, modifying, deleting and exporting todolist work to do are realized through QSqlTableModel and tableView control
Article directory
- PyQt project practice work assistant (4 use QSqlTableModel and tableView to implement Todolist)
- 1. Interface design
- 2. Implement todolist query
- 3. Using delegate to realize the personalized display of tableview data
- 3. Realize new functions
- 4. Implement deletion function
- 5. Export function
- 6. Click the link of event and slot function for each Button on the interface
1. Interface design
The interface supports the functions of filtering, querying, adding, deleting and exporting. First, I want to do this.
- The first line is to filter the query fields to realize the query function:
The date range is comboBox, default to all, this day, this week, last week, this month, last month, this year and before this year.
The degree is comboBox, which is divided into:
- Important and urgent, priority of this part
- It's not important. It's urgent
- Important not urgent, again
- It's not important. It's not urgent. It's final
The event is lineEdit, which supports fuzzy matching query by inputting text.
- The second line is the tableview control, which shows the query results and supports the direct modification of the database.
- The third line is toolButton, which implements the functions of adding records, deleting records and exporting tasks.
2. Implement todolist query
- Create a new QSqlTableModel instance todomodel. The QSqlTableModel class provides an editable data model that can read and write a single SQL table. Supports modification, insertion, deletion, query, and sorting. EditStrategy uses OnFiledChange policy to realize editing function.
self.todomodel = QSqlTableModel() self.todomodel.setTable("todolist") self.todomodel.setEditStrategy(QSqlTableModel.OnFieldChange)
The values of setEditStrategy are as follows:
QSqlTableModel.OnFieldChange, submit the database as soon as the change is made
QSqlTableModel.OnRowChange, submit the database after the current line number changes
QSqlTableModel.OnManualSubmit, the default value, will take effect only after submitting submit all () manually.
- By default, the QSqlTableModel query returns all records. You can use setFilter() to implement the function of where statement in sql, that is, to set query filter conditions. Query conditions should be transformed into filter statements recognized by Sqlite3 database, especially dealing with various date dictionaries.
Today: date(pdate) = date('now ')
This week: date (pdate) > = date ('now ',' weekday 1 ',' 7 day '))
Last week: (date (pdate) > = date ('now ',' weekday 1 ',' 14 day ') and date (pdate) < date ('now', 'weekday 1', '7 day'))
This month: (date (pdate) > = date ('now ',' start of month '))
Last month: (date (pdate) > = date ('now ',' start of month ',' 1 month ') and date (pdate) < date ('now', 'start of month'))
This year: (date (pdate) > = date ('now ',' start of year '))
Before this year: (date (pdate) < date ('now ',' start of year '))
# 2. Business conditions are converted to query filter statements. ##2.1 text fuzzy matching sql1 = "(content like '%{}%')".format(scontent) filterlist = sql1 ##2.2 to do list establishment date range. if sdaterange in [1, 2, 3, 4, 5, 6, 7]: if sdaterange == 1: # Today sql2 = "(date(pdate) = date('now'))" elif sdaterange == 2: # This week sql2 = "(date(pdate) >= date('now','weekday 1','-7 day'))" elif sdaterange == 3: # Last week sql2 = "(date(pdate) >= date('now','weekday 1','-14 day') and date(pdate) < date('now','weekday 1','-7 day'))" elif sdaterange == 4: # This month sql2 = "(date(pdate) >= date('now','start of month'))" elif sdaterange == 5: # Last month sql2 = "(date(pdate) >= date('now','start of month','-1 month') and date(pdate) < date('now','start of month'))" elif sdaterange == 6: # This year sql2 = "(date(pdate) >= date('now','start of year'))" elif sdaterange == 7: # Last year and before sql2 = "(date(pdate) < date('now','start of year'))" filterlist = filterlist + " and " + sql2 else: sql2 = '' ##2.3 to do status. if sfinstatus in [1, 2]: sql3 = "(finstatus={})".format(sfinstatus) filterlist = filterlist + " and " + sql3 else: sql3 = '' ##2.4 classification conditions of to-do items. if scatalog in [1, 2, 3, 4]: sql4 = "(catalog={})".format(scatalog) filterlist = filterlist + " and " + sql4 else: sql4 = ''
- Get database data and set the display content of the header
self.todomodel.setFilter(filterlist) self.todomodel.select() self.todomodel.setHeaderData(0, Qt.Horizontal, 'number') self.todomodel.setHeaderData(1, Qt.Horizontal, 'Important emergency') self.todomodel.setHeaderData(2, Qt.Horizontal, 'Matter') self.todomodel.setHeaderData(3, Qt.Horizontal, 'state') self.todomodel.setHeaderData(4, Qt.Horizontal, 'creation date') self.todomodel.setHeaderData(5, Qt.Horizontal, 'Completion date') self.todomodel.setHeaderData(6, Qt.Horizontal, 'Completion status') self.todomodel.setHeaderData(7, Qt.Horizontal, 'Next step plan')
- Set tableview column properties, including columnwidth, autoscroll, row crossing color change, etc
self.todo_un.setModel(self.todomodel) # Format todo tableview self.todo_un.setColumnWidth(0, 50) self.todo_un.setColumnWidth(1, 100) self.todo_un.setColumnWidth(2, 300) self.todo_un.setColumnWidth(3, 80) self.todo_un.setColumnWidth(4, 100) self.todo_un.setColumnWidth(5, 100) self.todo_un.setColumnWidth(6, 300) self.todo_un.setColumnWidth(7, 300) self.todo_un.setAutoScroll(True) self.todo_un.setAlternatingRowColors(True) # Row crossing change color self.todo_un.resizeRowsToContents() self.todo_un.horizontalHeader().setStyleSheet("QHeaderView::section{background:grey;}") self.todo_un.setSelectionBehavior(QAbstractItemView.SelectRows) self.todo_un.setColumnHidden(0, True) self.todo_un.setItemDelegate(listDelegate(self))
3. Using delegate to realize the personalized display of tableview data
tableview uses QItemDelegate by default. Because comboBox is involved in this project, the delegate agent listDelegate needs to be rewritten to display the dictionary item index in the database as the corresponding dictionary item name.
- Override the paint function to display the dictionary item name using drawDisplay().
- Rewrite the function of createEditor, and realize the use of comboBox to edit numbers in tableview
- Rewrite the setModelData function to select a dictionary, and then the data value dictionary index
- Rewrite setEditorData function to select dictionary data.
class listDelegate(QItemDelegate): # Set up todo proxy def __init__(self, parent): super(listDelegate, self).__init__(parent) def paint(self, painter, option, index): '''Lack of color marking function for important urgent tasks''' myoption = option myoption.displayAlignment = Qt.AlignCenter if index.column() == 1:#comboBox is displayed as dictionary text value = index.model().data(index, role=Qt.DisplayRole) text = todocatalog[value] self.drawDisplay(painter, myoption, myoption.rect, text) elif index.column() == 3:#comboBox is displayed as dictionary text value = index.model().data(index, role=Qt.DisplayRole) text = finstatus[value] self.drawDisplay(painter, myoption, myoption.rect, text) else: QItemDelegate.paint(self, painter, myoption, index) def createEditor(self, parent, option, index): if index.column() == 0: # Editing not allowed return None elif index.column() == 1: combox = QComboBox(parent) combox.addItems(todocatalog) combox.setEditable(True) return combox elif index.column() == 3: combox = QComboBox(parent) combox.addItems(finstatus) # combox.setCurrentText(QModelIndex.value) combox.setEditable(True) return combox elif index.column() in [4, 5]: dateedit = QDateEdit(parent) dateedit.setDate(QDate.currentDate()) dateedit.setCalendarPopup(True) return dateedit elif index.column() in [2, 6, 7]: plaintextedit = QPlainTextEdit(parent) return plaintextedit else: return QItemDelegate.createEditor(self, parent, option, index) def setModelData(self, editor, model, index): if index.column() == 1: model.setData(index, QVariant(editor.currentIndex())) elif index.column() == 3: model.setData(index, QVariant(editor.currentIndex())) else: QItemDelegate.setModelData(self, editor, model, index) def setEditorData(self, editor, index): text = index.model().data(index, Qt.DisplayRole) # print(text) if index.column() == 1: editor.setCurrentIndex(text) elif index.column() == 3: editor.setCurrentIndex(text) else: QItemDelegate.setEditorData(self, editor, index)
3. Realize new functions
- model.insertRow() new row
- Set the default value of some fields after clicking Add, setData()
- submitAll() submit
# 1.2 plan addition def todo_add(self): self.todomodel.insertRow(0) # Add in the first line, avoid turning pages self.todomodel.setData(self.todomodel.index(0, 1), 1) # Default initial classification as critical emergency self.todomodel.setData(self.todomodel.index(0, 3), 1) # Default initial state incomplete self.todomodel.setData(self.todomodel.index(0, 4), QDate.currentDate()) # The default creation date is the same day self.todomodel.submitAll()
4. Implement deletion function
- Gets the current row. index=tableview.currentIndex(), the result is QItemIndex, row=index.row()
- Use SqlTableModel.removeRow(row) to delete a row.
- Re query to render the results immediately.
# 1.3 plan deletion def todo_del(self): row = self.todo_un.currentIndex().row() self.todomodel.removeRow(row) self.todo_query()
5. Export function
Using xlwt library to export modeldata as xls.
- Get the export folder and filename QFileDialog().getSaveFileName()
- Judge whether filename is empty
- Get the number of rows and columns of data,
- Using xlwt.XFStyle to set the export file format
- Using cycle to write header
- Using row and column nesting loop, write all data, pay special attention to turning dictionary item number into dictionary item name output.
- After the writing is successful, the result window will pop up using QMessageBox.
# 1.4 plan export def todo_export(self): filename, filetype = QFileDialog().getSaveFileName(self, 'Please select Save File', './Data/', 'Text Files (*.xls)') if filename != '': row = self.todomodel.rowCount() col = self.todomodel.columnCount() f = xlwt.Workbook(encoding='utf-8') sheet1 = f.add_sheet('sheet1', cell_overwrite_ok=True) style = xlwt.XFStyle() al = xlwt.Alignment() al.horz = 0x02 # Set horizontal center al.vert = 0x01 # Set vertical center style.alignment = al for h in range(col): sheet1.write(0, h, self.todomodel.headerData(h, Qt.Horizontal, Qt.DisplayRole), style) for i in range(row): for j in range(col): index = self.todomodel.index(i, j) data = self.todomodel.data(index) if j == 1: data = todocatalog[data] if j == 3: data = finstatus[data] sheet1.write(i + 1, j, data, style) f.save(filename) QMessageBox.information(self, "Save results", "Data export succeeded, total{}Data export!".format(row), QMessageBox.Yes) else: QMessageBox.warning(self, "warning", "File open error, data export failed", QMessageBox.Ok)
6. Click the link of event and slot function for each Button in the interface
self.pushButton_todo_query.clicked.connect(self.todo_query) # To do query self.toolButton__task_add.clicked.connect(self.todo_add) # x new to do self.toolButton__task_export.clicked.connect(self.todo_export) # Export to do self.toolButton__task_del.clicked.connect(self.todo_del) # Delete to do
Published 4 original articles, won praise 0, visited 25