PyQt project practice work assistant (4 use QSqlTableModel and tableView to implement Todolist)

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

1. Interface design

The interface supports the functions of filtering, querying, adding, deleting and exporting. First, I want to do this.

  1. 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.

  1. The second line is the tableview control, which shows the query results and supports the direct modification of the database.
  2. The third line is toolButton, which implements the functions of adding records, deleting records and exporting tasks.

2. Implement todolist query

  1. 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.

  1. 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
            sql2 = ''
        ##2.3 to do status.
        if sfinstatus in [1, 2]:
            sql3 = "(finstatus={})".format(sfinstatus)
            filterlist = filterlist + " and " + sql3
            sql3 = ''
        ##2.4 classification conditions of to-do items.
        if scatalog in [1, 2, 3, 4]:
            sql4 = "(catalog={})".format(scatalog)
            filterlist = filterlist + " and " + sql4
            sql4 = ''
  1. Get database data and set the display content of the header
        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')
  1. Set tableview column properties, including columnwidth, autoscroll, row crossing color change, etc
        # 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.setAlternatingRowColors(True)  # Row crossing change color
        self.todo_un.setColumnHidden(0, True)

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.

  1. Override the paint function to display the dictionary item name using drawDisplay().
  2. Rewrite the function of createEditor, and realize the use of comboBox to edit numbers in tableview
  3. Rewrite the setModelData function to select a dictionary, and then the data value dictionary index
  4. 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)
            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)
            return combox
        elif index.column() == 3:
            combox = QComboBox(parent)
            # combox.setCurrentText(QModelIndex.value)
            return combox
        elif index.column() in [4, 5]:
            dateedit = QDateEdit(parent)
            return dateedit
        elif index.column() in [2, 6, 7]:
            plaintextedit = QPlainTextEdit(parent)
            return plaintextedit
            return QItemDelegate.createEditor(self, parent, option,

    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()))
            QItemDelegate.setModelData(self, editor, model, index)

    def setEditorData(self, editor, index):
        text = index.model().data(index, Qt.DisplayRole)
        # print(text)
        if index.column() == 1:
        elif index.column() == 3:
            QItemDelegate.setEditorData(self, editor, index)

3. Realize new functions

  1. model.insertRow() new row
  2. Set the default value of some fields after clicking Add, setData()
  3. 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

4. Implement deletion function

  1. Gets the current row. index=tableview.currentIndex(), the result is QItemIndex, row=index.row()
  2. Use SqlTableModel.removeRow(row) to delete a row.
  3. Re query to render the results immediately.
 # 1.3 plan deletion
    def todo_del(self):
        row = self.todo_un.currentIndex().row()

5. Export function

Using xlwt library to export modeldata as xls.

  1. Get the export folder and filename QFileDialog().getSaveFileName()
  2. Judge whether filename is empty
  3. Get the number of rows and columns of data,
  4. Using xlwt.XFStyle to set the export file format
  5. Using cycle to write header
  6. Using row and column nesting loop, write all data, pay special attention to turning dictionary item number into dictionary item name output.
  7. 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 =
                    if j == 1:
                        data = todocatalog[data]
                    if j == 3:
                        data = finstatus[data]
                    sheet1.write(i + 1, j, data, style)
            QMessageBox.information(self, "Save results", "Data export succeeded, total{}Data export!".format(row), QMessageBox.Yes)
            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
Private letter follow

Keywords: Qt Database SQL encoding

Added by jtbaker on Sun, 23 Feb 2020 12:34:10 +0200