PyQt: The easiest way to do CRUD UI for an existing database?

Question

In the smallest amount of code, how should I list (hundreds) of columns in an existing database table and create a table view that is connected to the database with the corresponding widgets in the table cells to modify the data?

I understand that this was simply not possible thanks to the Qt Designer Database Connection Wizard, but it is deprecated.

Answer 1

The easiest way, as was said, is to use QSqlTableModel. Suppose we want:

• view data and switch between tables
• , . QSpinBox

, . QSqlDatabase::tables. QComboxBox :

 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 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())

. - QLineEdit. , QItemDelegate. createEditor QSqlField. , SQLite DATETIME string ( SQLite:)). , .

 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)

MainWindow combobox :

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)

insert delete:

 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)