This will be a brief overview of how I use QDataWidgetMappers to take the manual process out of CRUD'ing PyQt fields.

QDataWidgetMappers are great because you don't need to compile data structures (like dicts or lists) and then loop through them with .setData (which can get confusing with QModelIndex) to change your database data!

After setting up my python environment, the general project structure looks like this, generally following the MVC design pattern:

• App/

  • models.py (I create and handle QSqlTableModels here)

  • main.py

  • view_logic.py (which imports the generated *_view_ui.py files)

  • database.py (handles creating database connection)

  • db/

    • records.db (sqlite3)

  • ui_views/

    • ui_files/

      • *.ui files created using Qt Designer

    • *_view_ui.py, created using the VSCode extension "PYQT Integration" by FengZhou (found here)

I will not be covering how to set up your QSqlTableModels, however, this link is a great resource.

In my example, I have a database created with this Sqlite3 schema/code (I generally have a separate script, <script.py> in my App's root folder that handles creating/exec'ing my database, so I can easily delete my <.db> file then run <script.py> to have everything setup again) :

<script.py>

from PyQt5.QtSql import QSqlQuery
create_table_query = QSqlQuery()
# code here to connect to database
create_table_query.exec(
        """CREATE TABLE "patient_profile" (
            "patient_id"    INTEGER,
            "doc1"    BLOB,
            "doc2"    BLOB,
            "name_title" BLOB,
            "name_fname" BLOB,
            "name_mname" BLOB,
            "name_lname" BLOB,
            "name_suffix" BLOB,
            "name_pref_name" BLOB,
            "address_add1" BLOB,
            ...
            "date_patient_added"    BLOB,
            PRIMARY KEY("patient_id"),
            FOREIGN KEY("doc1") REFERENCES "doctors"("id"),
            FOREIGN KEY("doc2") REFERENCES "doctors"("id")
            )""")

Note: I used BLOB datatype to avoid any potential datatype conflicts, especially helpful when using Q-type objects (QDateTimeEdit, QRadioBox, etc...)

In the case of my example, I want to populate a modal QDialog that opens after a user has selected a patient from a QTableView. The QDialog is made in Qt Designer, the .ui file is saved in /ui_views/ui_files/, and the generated *_view_ui.py is saved in /ui_views/.

Getting started in <view_logic.py>

from PyQt5.QtWidgets import QDataWidgetMapper, QDialog
from ui_views.ui_files import Ui_patient_profile

class PatientProfile(QDialog):
    def __init__(self, parent, patient_profile_model, data_model):
        super().__init__(parent)
        self.patient_profile_ui = Ui_patient_profile()
        self.patient_profile_ui.setupUi(self)
        self.patient_profile_ui.save_changes_btn.clicked.connect(self.save_changes)

        self.patient_profile_model = patient_profile_model
        # data_model is a class created in main.py to hold global variables
        self.data_model = data_model
        # create mapper and set it's model
        self.patient_mapper = QDataWidgetMapper()
        self.patient_mapper.setModel(self.patient_profile_model)

    def save_changes(self):
        if self.patient_mapper.submit():
            print('saved all fields successfully')

For my use case, I don't want changes submitted until the user presses a button "Save", so I'll set the submit policy:

patient_mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)

Then start mapping widgets to their respective column in the QSqlTableModel:

self.patient_mapper.addMapping(self.patient_profile_ui.title_combo, 3)
self.patient_mapper.addMapping(self.patient_profile_ui.firstname_edit, 4)

For my use case, I wanted to update the index of the patient_mapper based on a selection from a QTableView in my <main.py> (please note that most <main.py> code is omitted for brevity):

<main.py>

class MainWindow(QMainWindow):
    def __init__(self):
        self.appt_ui = Ui_MainWindow()
        self.appt_ui.setupUi(self)
        self.appt_ui.patient_tableview.selectionModel().selectionChanged.connect(self.patient_selection_changed)
        self.patient_details = PatientProfile()
        # we use .exec_ here to ensure window is modal
        self.appt_ui.show_patient_btn.clicked.connect(lambda: self.patient_details.exec_())


    def patient_selection_changed(self, current: QModelIndex):
        # .selectionChanged.connect passes in "current", which is an index of where we are in the model
        self.patient_details.set_patient_index(current)

Back to <view_logic.py>

class PatientProfile(QDialog):
    def __init__(self, parent, patient_profile_model, data_model):
        super().__init__(parent)
        # omitted code
    def set_patient_index(self, current):
        self.patient_mapper.setCurrentIndex(current.row())

If you are a newer Python and/or PyQt coder, this example may seem a bit confusing however I would highly recommend breaking your code down into the general MVC design pattern (the generated *_view_ui.py files as the view, <view_logic.py> as the controller, and <models.py> as the model(s)) as it can save you a lot of confusion in the long run! Speaking from experience haha.