How to refresh QTableView after an SQL Commit?

[liquidhamilton]

I've been scouring the forums, and various websites for the past 12 hours trying to solve this simple issue and I can't seem to get it! I have a QTableView widget which is populated from an SQL Database. When I add a record and commit, it updates the database just fine, but the table doesn't refresh to reflect it. If I close and reopen the app, it does in fact show the newly added record. How would I get it to refresh/update on the Commit?

In case it matters, in the Main Window class, it creates the table widget by calling a separate class self.databaseWidget = databaseSetup(self.centralwidget)with the database widget I created (and handles all of the database management), and thus I can't call tableView.update() it would seem, since it needs to be called from the Main Window class and not the databaseSetup class?

I think that .update() should be what I need correct? If so, how would I call it so that it would work correctly, since it doesn't do anything when called from my database class?

Edit: Additionally, I don't need it to be constantly searching for updates, as new records won't be added on a regular basis (it's for an home inventory app), which is why I was trying to have it update only on the commit.

[JonB]

@liquidhamilton
I didn't have time to type more last night. I'm afraid I'm still not offering to type in a whole example, but...

...There is another wrong in your approach. Although to make the way you have written it could be made to work if you obtain a reference to the main window instance so you can go

self.mysignal.connect(mainUiWindowInstance.signalTest)

you are actually connecting the signal to the slot from the wrong place.

First, just to be clear, when I talk about "the main window" I mean the one of type Ui_MainWindow, the one you create as the first thing in your start up code. You have made your class bookSearch [do yourself a favour, make your class names start with an upper-case, make your variables/parameters/methods start with a lower-case, it's much clearer, so this should be class BookSearch)] also derive from QMainWindow. Although this is allowed because QMainWindow is simply a particular window style suitable for a main window, not a "special" "this is the main window of the app and there can only be one", you almost certainly don't want to do this. Think about organizing so you have just the one QMainWindow, if you want to "swap" what is inside it like "pages" put a QStackedWidget inside it. That allows you to define and swap between one given "page" at a time. Or, you might want BookSearch to be a modeless QDialog so it sits there all the time and notifies the main window when the user adds a book.

That aside, returning to the signal/slot. You have made it so the class which is emitting the signal is responsible for attaching the slot (via connect()) to be called. This is not at all how signals/slots are intended to work! The emitter of a signal does so when it has something to say to the outside world, but it has no knowledge of who is listening to (slotted onto) the signal. It is the places which wish to be notified of the signal to act on it which should connect their slot. That's very important for the logic of signals/slots, it makes the signaller independent of the listeners.

Further, you wouldn't connect() a signal to a slot when an event happens/just before the emit(). (Plus, you must not re-connect() the same signal->slot more than once, i.e. not each time a button is clicked.) You would do it once before any signals get raised, and it remains there for then on.

So... in outline you should have:

mainwindow.py:

from booksearch import BookSearch

class Ui_MainWindow(QMainWindow):

    # somewhere when you want to create the BookSearch window
    self.bookSearch = BookSearch(self)
    # do the connect() once now
    # this *instance* of a BookSearch has its signal connected to this *instance* of a Ui_MainWindow's slot
    self.bookSearch.mySignal.connect(self.signalTest)

    # somewhere when you want to show it
    self.bookSearch.show()

   def signalTest(self):
        print("signal acquired")

booksearch.py:

class BookSearch(QWidget):

    mySignal = QtCore.pyqtSignal()

    def addClicked(self):
        self.mySignal.emit()

Note that BookSearch, the signaller, does not import UI_MainWindow and does not need to get the main window instance this way round. It does not know or care about the main window at all, and indeed could be used from elsewhere, and wouldn't care if nobody has a slot on its signal. OTOH, UI_MainWindow knows that BookSearch emits mySignal when something happens there, and it connects its slot to that signal from the created self.bookSearch instance.

[SGaist]

Hi and welcome to devnet,

What are you using as model ? If QSqlTable, you can call select on it.

[liquidhamilton]

I am indeed using QSqlTable as my model. In fact, in my createModel method, I do call self.model.select() on its creation. However, when I call this method:

def addEntry(self):
        self.q = QtSql.QSqlQuery()
        self.q.exec_(
            "INSERT INTO Books VALUES ('Test', 'Test', 'Test', '1234');")
        self.db.commit()
        self.model.select()

it does not refresh or do anything other than correctly add to the database.

Here is the method from the Main Class:

def addClicked(self):
        t = databaseSetup(None)
        t.addEntry()

and a snippet from the class that it is calling:

class databaseSetup(QWidget):
    def __init__(self, parent):
        super(databaseSetup, self).__init__(parent)
        self.createConnection()

    def createConnection(self):
        if not os.path.exists('./test.db'):
            self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
            self.db.setDatabaseName("test.db")
            if not self.db.open():
                print("Cannot establish a database connection")
                return False
            self.fillTable()
            self.createModel()
            self.initUI()
        else:
            self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
            self.db.setDatabaseName("test.db")
            if not self.db.open():
                print("Cannot establish a database connection")
                return False
            self.createModel()
            self.initUI()

    def createModel(self):
        self.model = QtSql.QSqlTableModel()
        self.model.setTable("Books")
        self.model.setHeaderData(0, QtCore.Qt.Horizontal, "Title")
        self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Author")
        self.model.setHeaderData(2, QtCore.Qt.Horizontal, "Series")
        self.model.setHeaderData(3, QtCore.Qt.Horizontal, "Year")
        # self.model.setEditStrategy(0)
        self.model.select()

    def initUI(self):
        self.tableView = QtWidgets.QTableView(self)
        self.tableView.setModel(self.model)
        self.tableView.setGeometry(QtCore.QRect(240, 40, 831, 481))
        self.tableView.setObjectName("tableView")
        self.tableView.setSortingEnabled(True)
        self.tableView.setDropIndicatorShown(True)
        self.tableView.setSelectionBehavior(self.tableView.SelectRows)
        self.tableView.setSelectionMode(self.tableView.SingleSelection)
        self.tableView.horizontalHeader().setSectionsMovable(True)

# some other methods

    def addEntry(self):
        self.q = QtSql.QSqlQuery()
        self.q.exec_(
            "INSERT INTO Books VALUES ('Test', 'Test', 'Test', '1234');")
        self.db.commit()
        self.model.select()

Edit: Some additional info. I do have a bug somewhere causing these errors when I add an entry, it could be contributing to this particular issue. The more I look into it, it seems that when I call a method from the databaseSetup class, it's reinitializing the entire class and therefore trying to re-open the database. I tested this with an empty method and then calling it, and still get the duplicate connection error. Am I calling the methods incorrectly?

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

[SGaist]

Well... As I already wrote many times and also written in the QSqlDatabase documentation: do not keep a copy QSqlDatabase. The class is a singleton that manageable all the connections.

One more thing, why are you re-creating the connection each time you click ?

[liquidhamilton]

I’m sorry, I’m pretty new to SQL in general so database management especially in PyQt is a learning experience. What do you mean by keeping a copy of the database? How would I prevent that?

Also, am I recreating it on each call? I thought I was just setting the q variable Via the addEntry method. Should I do that on the initialization of the database setup instead? Is it re-running the entire class when I call it, meaning I called the method incorrectly?

[jsulm]

@liquidhamilton

self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")

This self.db is not needed. You can always get your db calling QSqlDatabase::database() (you can pass database name).
You should really pay more attention to the documentation. It is shown there: https://doc.qt.io/qt-5/qsqldatabase.html

"Also, am I recreating it on each call?" - yes, you do. In addClicked() you call databaseSetup() which calls createConnection(). You only need to call databaseSetup() once.

[liquidhamilton]

@jsulm

Thank you so much for the info. I’ll give it a shot in a little bit but one clarification: When I’m calling databaseSetup, it’s so that I can call the method within it. Don’t I need to call databaseSetup(None).addEntry() In order to access it from another class?

[jsulm]

@liquidhamilton The thing is: each time you're creating an instance of databaseSetup you're setting up your database connection! Don't do this. You should only do it once. Create the connection once and then get it using QSqlDatabase::database() when needed.
"duplicate connection name 'qt_sql_default_connection'" - this clearly states the problem.

[liquidhamilton]

I went ahead and fixed that duplicate connection issue, although it seems unrelated to my original issue of getting select() to work. I took everything from the databaseSetup class and put it into the Main class so it all happens during the initial setup and I can work with the database from there.

Regarding select(), I think I've narrowed it down to the fact that I seem to be calling the function incorrectly from within a separate class.

Class 1 is the main class where the model lives.

Class 2 is a separate widget class that is part of a second main window (opened via a button). It correctly is able to add to the database, however I can't call model.select() on it since that function lives in Class 1.

So I guess what I'm asking is, what is the proper way to call that function from within Class 2? I've tried things like Class1().model.select() as well as writing a separate method within Class 1 that only does self.model.select() and calling it from Class 2 via Class1().newmethod() and still no luck. I'm sure it's just me not understanding how to call functions from between classes.

If I connect a button within Class 1 to the "new method" for this test case, I should note that it does indeed update as expected.

[SGaist]

Class1().newmethod()

This created a new object of Class1 and calls the method on it. It does nothing with the actual object you are using.

[liquidhamilton]

Understood, but what would be the CORRECT way to call that method then? Because when I run it like that, I get 'Class1' object has no attribute 'model' but if I call it from within Class1 itself, it works, which makes sense if I'm creating a new instance of Class 1 instead of accessing the existing instance.

Or if there is a better way of doing what I'm trying to accomplish, what would that be? Could I affect the model directly from the second class somehow?

[SGaist]

@liquidhamilton said in How to refresh QTableView after an SQL Commit?:

createModel

Until that method is called, your Class1 object has no attribute model since it's created in that method.

You need to give more details. What is Class2, what does it do with respect to Class1 ?

[liquidhamilton]

Class 1 is the UI_MainWindow class.
Class 2 opens a second window and is essentially an app in its own (scrapes the internet for metadata and then displays it). This class also has a button which creates a new SQL entry with the scraped metadata. All works as expected. However, with respect to Class 1, I need it to trigger self.model.select() that is created inside Class 1 in order to refresh the TableView on the Class 1 Main Window after the new entry is created.

For reference, here is the complete code: https://github.com/LiquidHamilton/Testing/blob/master/libraryApp.py

Line 232 triggers "class 2" to open.
Line 485 is in Class 2 and is where "select()" would need to be called in class 1.

[SGaist]

In that case you should rather use signals and slots. Once your Class2 has finished it job, emit a signal that you will connected to Class1's slot.

[liquidhamilton]

I've tried that but I get the same errors. signalTest() missing 1 required positional argument: 'self' This is really frustrating. All I need to do is call a method, why can't I get this working?

class bookSearch(QMainWindow):

    mysignal = QtCore.pyqtSignal()

# methods and whatnot

    def addClicked(self):
        self.mysignal.connect(Ui_MainWindow.signalTest())
        self.mysignal.emit()

class Ui_MainWindow(QMainWindow):

# other methods

    def signalTest(self):
        print("signal acquired")

I'm all for learning and figuring things out on my own with minimal assistance, but I've been staring at this same problem for almost 24 hours straight. Could you perhaps tell me exactly how to fix it, so I can work backwards from there and deconstruct it to learn?

[JonB]

@liquidhamilton said in How to refresh QTableView after an SQL Commit?:

self.mysignal.connect(Ui_MainWindow.signalTest())

self.mysignal.connect(Ui_MainWindow.signalTest), note the omitted ().

And I'm afraid your Ui_MainWindow is a class, you would need the instance. And you wouldn't do it this way.

I hope someone else can write you a full example of how to connect signals & slots.

[liquidhamilton]

@JonB

That just made it click in my head. I was reinitializing the entire class instead of affecting the existing instance of it! I just have no idea how to actually do that haha. I've read the documentation on signals & slots but can't seem to get it to work in this particular instance. Once I understand it, my original question will be solved (because model.select() will be called), and I can apply that knowledge to the additional classes that I'm constructing for this app that need to interact with the first Class, so I appreciate all the help I'm getting.

[JonB]

@liquidhamilton
I didn't have time to type more last night. I'm afraid I'm still not offering to type in a whole example, but...

...There is another wrong in your approach. Although to make the way you have written it could be made to work if you obtain a reference to the main window instance so you can go

self.mysignal.connect(mainUiWindowInstance.signalTest)

you are actually connecting the signal to the slot from the wrong place.

First, just to be clear, when I talk about "the main window" I mean the one of type Ui_MainWindow, the one you create as the first thing in your start up code. You have made your class bookSearch [do yourself a favour, make your class names start with an upper-case, make your variables/parameters/methods start with a lower-case, it's much clearer, so this should be class BookSearch)] also derive from QMainWindow. Although this is allowed because QMainWindow is simply a particular window style suitable for a main window, not a "special" "this is the main window of the app and there can only be one", you almost certainly don't want to do this. Think about organizing so you have just the one QMainWindow, if you want to "swap" what is inside it like "pages" put a QStackedWidget inside it. That allows you to define and swap between one given "page" at a time. Or, you might want BookSearch to be a modeless QDialog so it sits there all the time and notifies the main window when the user adds a book.

That aside, returning to the signal/slot. You have made it so the class which is emitting the signal is responsible for attaching the slot (via connect()) to be called. This is not at all how signals/slots are intended to work! The emitter of a signal does so when it has something to say to the outside world, but it has no knowledge of who is listening to (slotted onto) the signal. It is the places which wish to be notified of the signal to act on it which should connect their slot. That's very important for the logic of signals/slots, it makes the signaller independent of the listeners.

Further, you wouldn't connect() a signal to a slot when an event happens/just before the emit(). (Plus, you must not re-connect() the same signal->slot more than once, i.e. not each time a button is clicked.) You would do it once before any signals get raised, and it remains there for then on.

So... in outline you should have:

mainwindow.py:

from booksearch import BookSearch

class Ui_MainWindow(QMainWindow):

    # somewhere when you want to create the BookSearch window
    self.bookSearch = BookSearch(self)
    # do the connect() once now
    # this *instance* of a BookSearch has its signal connected to this *instance* of a Ui_MainWindow's slot
    self.bookSearch.mySignal.connect(self.signalTest)

    # somewhere when you want to show it
    self.bookSearch.show()

   def signalTest(self):
        print("signal acquired")

booksearch.py:

class BookSearch(QWidget):

    mySignal = QtCore.pyqtSignal()

    def addClicked(self):
        self.mySignal.emit()

Note that BookSearch, the signaller, does not import UI_MainWindow and does not need to get the main window instance this way round. It does not know or care about the main window at all, and indeed could be used from elsewhere, and wouldn't care if nobody has a slot on its signal. OTOH, UI_MainWindow knows that BookSearch emits mySignal when something happens there, and it connects its slot to that signal from the created self.bookSearch instance.

[liquidhamilton]

@JonB

Wow! I can’t thank you enough for that thorough explanation. That’s exactly what I needed to learn. I’ll be sure to take all of that information and practice on a few smaller examples to help solidify it, and then integrate it into my project.

Edit: I got it to work!! Thank you to everyone who contributed. My understanding of these particular topics has grown substantially!