CODEWITHSUNDEEP
pythonsqldatabasepyqttableview
Shayan
Shayan

Reputation: 21

How to update TableView in PyQt when a new entry has been added in the database?

I am trying to update my TableView in PyQt Gui so that anytime a new entry is added to the QPSQL database, it will update it in the TableView within the Gui as well. Currently, the connection works great and properly connects to the database and shows what is in the tables which is just a phonebook for now with a name, phone number, and address column. The problem is that if I insert a new row into the Postgrese Sql database, it wont update in the TableView unless I close the GUI and re run the code. I am just looking for a way to update my TableView so that if something is added to the Postrgese SQL database, that it will automatically do it without a push button or anything. Any help is appreciated, thanks in advance.

I will show a section of my code as well.

class Ui_Dialog(object):
def setupUi(self, Dialog):
    Dialog.setObjectName(_fromUtf8("Dialog"))
    Dialog.resize(400, 300)
    self.buttonBox = QtGui.QDialogButtonBox(Dialog)
    self.buttonBox.setGeometry(QtCore.QRect(30, 240, 341, 32))
    self.buttonBox.setOrientation(QtCore.Qt.Horizontal)
    self.buttonBox.setStandardButtons(QtGui.QDialogButtonBox.Cancel|QtGui.QDialogButtonBox.Ok)
    self.buttonBox.setObjectName(_fromUtf8("buttonBox"))
    self.tableView = QtGui.QTableView(Dialog)
    self.tableView.setGeometry(QtCore.QRect(20, 50, 256, 192))
    self.tableView.setObjectName(_fromUtf8("tableView"))
###############################Connection for database 
    db = QSqlDatabase.addDatabase("QPSQL")  # Database typed set to QPSQL for postgres use 
    db.setDatabaseName("postgres")          # This field should stay as postgres
    db.setUserName("postgres")              # This field should stay as postgres
    db.setPassword("pass")              # This field is for your personal password you used for the postgres install
    db.setHostName("localhost")             # This field should stay as localhost
    if not db.open():
      QtGui.QMessageBox.critical(None, QtGui.qApp.tr("Cannot open database"),
         QtGui.qApp.tr("Unable to establish a database connection.\n"
            "This example needs SQLite support. Please read "
            "the Qt SQL driver documentation for information "
            "how to build it.\n\n" "Click Cancel to exit."),
         QtGui.QMessageBox.Cancel)

      return False
    ok = db.open()
############################### Add database table to the Table view in the main GUI # 
    model = QSqlTableModel()
    query = QSqlQuery("SELECT * FROM PHONEBOOK", db)
    model.setEditStrategy(QSqlTableModel.OnFieldChange)
    model.setQuery(query)
    model.submitAll()

    self.tableView.setModel(model)
    self.tableView.show()
    model.select()
#

Upvotes: 0

Views: 2871

Answers (1)

Oliver
Oliver

Reputation: 29571

If you have ability to enable notification in your database then you can use QSqlDriver.subscribeToNotification to receive notification events as soon as you add something to your table. How to enable notification depends on how you admin your DB, but start by looking at https://www.postgresql.org/docs/9.0/static/sql-notify.html.

Note that the notification system is just to let our app know that something has changed. Once you have a slot that connects to the notification, you still have to decide how to handle it. Your app won't necessarily update every time there is a notification, depends how expensive it is to get updates. It might update at most once per 5 seconds, say.

It would look something like this:

db.exec("NOTIFY yourEventId")  # only this once for lifetime of database
db.driver().subscribeToNotification("yourEventId")
db.driver().notification.connect(self.yourSlot)

One thing though, if your database is going to update every second forever (say, because it is reading hardware constantly), you should just poll the DB (query the DB for added records, at regular time interval). The notification mechanism is better than poling when time between db updates can be large. But if updates are more frequent (say 10 times per sec) than desired refresh rate of your GUI table (say once per second) then polling may be better.

Either way there is no automatic way for your table to update when DB changes, you at least have to add code that updates the table view's model, and the update happens only when your app determines that DB has changed, which happens by polling it or listening for modification events from it (only available in postgresql AFAICT).

Upvotes: 1

Related Questions