Creating a database data entry form with PyQt5
Using SQL table models to create automatic views

Heads up! You've already completed this tutorial.

Glenn Breeden wrote

I am learning Python and Qt and am interested in creating an app to take the place of a MS Word form that I use for my work service calls. Ideally, I’d like for it to be connected to a database or databases that would allow me to populate client name/address etc fields from the source via drop down box and also to be editable for a new client. I also need date pickers and text fields for notes about the service, which would be searchable at a later time. I haven’t seen this in a commercial product yet that wasn’t part if a super expensive package. My hope here is that someone can point me in the right direction as to how to do this or if it’s even possible.


Martin Fitzpatrick

Hi Glenn welcome to the forum!

What you're describing is definitely possible with Qt, and actually fairly straightforward if you use Qt's SQL models and a widget mapper. There are some examples in the latest update to the book, and I'll be adding more here -- but in the meantime this should give you an idea.

python
import sys

from PyQt5.QtCore import QSize, Qt
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel
from PyQt5.QtWidgets import (
    QApplication,
    QComboBox,
    QDataWidgetMapper,
    QDateTimeEdit,
    QFormLayout,
    QHBoxLayout,
    QLabel,
    QLineEdit,
    QMainWindow,
    QPushButton,
    QVBoxLayout,
    QWidget,
)

db = QSqlDatabase("QSQLITE")
db.setDatabaseName("test.sqlite")
# Create some dummy data.
db.open()
db.exec_(
    "create table if not exists mytable (title string, kind string, created datetime);"
)
db.exec_(
    "insert into mytable (title, kind, created) values ('first title', 'Two', datetime('2020-06-02 12:45:11') );"
)
db.exec_(
    "insert into mytable (title, kind, created) values ('2nd item', 'Three', datetime('2019-06-02 12:45:11') );"
)


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        form = QFormLayout()

        # Define fields.
        self.title = QLineEdit()

        self.kind = QComboBox()
        self.kind.addItems(["One", "Two", "Three"])

        self.date = QDateTimeEdit()

        form.addRow(QLabel("Title"), self.title)
        form.addRow(QLabel("Type of item"), self.kind)
        form.addRow(QLabel("Date"), self.date)

        self.model = QSqlTableModel(db=db)

        self.mapper = QDataWidgetMapper()  # Syncs widgets to the database.
        self.mapper.setModel(self.model)

        self.mapper.addMapping(self.title, 0)  # Map to column number
        self.mapper.addMapping(self.kind, 1)
        self.mapper.addMapping(self.date, 2)

        self.model.setTable("mytable")
        self.model.select()  # Query the database

        self.mapper.toFirst()  # Jump to first record

        self.setMinimumSize(QSize(400, 400))

        controls = QHBoxLayout()

        prev_rec = QPushButton("Previous")
        prev_rec.clicked.connect(self.mapper.toPrevious)

        next_rec = QPushButton("Next")
        next_rec.clicked.connect(self.mapper.toNext)

        save_rec = QPushButton("Save Changes")
        save_rec.clicked.connect(self.mapper.submit)

        controls.addWidget(prev_rec)
        controls.addWidget(next_rec)
        controls.addWidget(save_rec)

        layout = QVBoxLayout()

        layout.addLayout(form)
        layout.addLayout(controls)

        widget = QWidget()
        widget.setLayout(layout)
        self.setCentralWidget(widget)


app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec_()

This produces the following form which is linked to the database. The database is pre-populated with some dummy data. You can navigate between records and save changes.

screenshot|402x439

The last bit would be to add a form to enter a new record. How you do that depends a bit on the interface (popping up a dialog vs. changing the form state).

The complete guide to packaging Python GUI applications with PyInstaller.
[[ discount.discount_pc ]]% OFF for the next [[ discount.duration ]] [[discount.description ]] with the code [[ discount.coupon_code ]]

Purchasing Power Parity

Developers in [[ country ]] get [[ discount.discount_pc ]]% OFF on all books & courses with code [[ discount.coupon_code ]]
Well done, you've finished this tutorial! Mark As Complete
[[ user.completed.length ]] completed [[ user.streak+1 ]] day streak

Creating a database data entry form with PyQt5 was written by Martin Fitzpatrick .

Martin Fitzpatrick has been developing Python/Qt apps for 8 years. Building desktop applications to make data-analysis tools more user-friendly, Python was the obvious choice. Starting with Tk, later moving to wxWidgets and finally adopting PyQt.