PyQt: Simplest way to make a CRUD UI for an existing database?

Question

In the least amount of code, how should I enumerate the (hundreds) of columns in an existing database table and create a table view that is coupled to the database with type-appropriate widgets in the table cells to modify the data?

I understand this used to be dead simple, thanks to Qt Designer's Database Connection Wizard, but it's been deprecated.

Answer 1

The simplest way as it was said is using QSqlTableModel. Let's assume that we want to:

• view data and switch between tables
• edit data using appropriate editors, e.g. QSpinBox for integers
• insert rows
• and delete rows

Ok, first we need to get the list of all the tables in the database. This can be achieved using QSqlDatabase::tables method. Also I will use QComboxBox to show tables:

 class MainWindow(QtWidgets.QFrame):
     def __init__(self, parent=None):
         QtWidgets.QFrame.__init__(self, parent)

         # Connect to database
         self.__database__ = QtSql.QSqlDatabase.addDatabase('QSQLITE')
         self.__database__.setDatabaseName('sqlite.db')
         self.__database__.open()

         # Create QComboBox to show tables
         self.__tableNames__ = QtWidgets.QComboBox(self)

         # Create QTableView to show table's data
         self.__tableGrid__ = QtWidgets.QTableView(self)

         # Create table model 
         self.__tableModel__ = QtSql.QSqlTableModel(self, self.__database__)
         self.__tableGrid__.setModel(self.__tableModel__)

         # Connect combobox signal to update model
         self.__tableNames__.currentIndexChanged[str].connect(self.__tableModel__.setTable)
         self.__tableNames__.currentIndexChanged[str].connect(self.__tableModel__.select)

         # Set the list of the tables to combobox
         self.__tableNames__.addItems(self.__database__.tables())

Now we already can switch between database's tables and edit the data. But the default editor is QLineEdit. We can provide others editors using QItemDelegate class. In createEditor function we can get the type of column from QSqlField. There is a shortcoming because at least for SQLite for the DATETIME it always returns string (but it is SQLite :)). Probably you should find other way to get the type.

 class SqlItemDelegate(QtWidgets.QStyledItemDelegate):
     def __init__(self, database, parent=None):
         QtWidgets.QStyledItemDelegate.__init__(self, parent)
         self.__table__ = ''
         self.__database__ = database

     def setTable(self, table):
         self.__table__ = table

     def createEditor(self, parent, option, index):
         record = self.__database__.record(self.__table__)
         column_type = record.field(record.fieldName(index.column())).type()

         print(record.fieldName(index.column()), column_type)
         if column_type == QtCore.QVariant.Double:
             return QtWidgets.QDoubleSpinBox(parent)
         if column_type == QtCore.QVariant.DateTime:
             return QtWidgets.QDateTimeEditor(parent)
         # etc.

         return QtWidgets.QStyledItemDelegate.createEditor(self, parent, option, index)

Also in the MainWindow we should create and connect the delegate with combobox to update table name:

class MainWindow(QtWidgets.QFrame): def init(self, parent=None): # .....

     self.__delegate__ = SqlItemDelegate(self.__database__, self)
     self.__tableGrid__.setItemDelegate(self.__delegate__)

     self.__tableNames__.currentIndexChanged[str].connect(self.__delegate__.setTable)

And the last step is implementation of insert and delete functions:

 class MainWindow(QtWidgets.QFrame):
     def __init__(self, parent=None):
         # .....

         self.__insertRow__ = QtWidgets.QPushButton('Insert', self)
         self.__insertRow__.clicked.connect(self.insertRow)
         self.__deleteRow__ = QtWidgets.QPushButton('Delete', self)
         self.__deleteRow__.clicked.connect(self.deleteRow)

     def deleteRow(self):
         index = self.__tableGrid__.currentIndex()
         self.__tableModel__.removeRows(index.row(), 1)

     def insertRow(self):
         self.__tableModel__.insertRows(0, 1)