Results 1 to 12 of 12

Thread: PyQT: The QSqlTableModel and how to wrap my head around it

  1. #1
    Join Date
    Mar 2020
    Location
    Graz, Austria
    Posts
    6
    Thanks
    1
    Qt products
    Platforms
    Windows

    Unhappy PyQT: The QSqlTableModel and how to wrap my head around it

    Dear fellow Qtarians!

    I am fairly new to the world of Qt/PyQt after, as a reasonable tkinter programmer, try to reach the next level of beautiful gui design. So far it is fantastic.
    But since a few days I can't seem to wrap my head around qt's own model structure. The documentation for pyQt gets very fast very bad if you want to do more advanced stuff.

    I am writing a program which uses sqlite3. So I read about these fantastic Sql Models. I tried it with QSqlTableModel (the others too, but for trying this will do),
    but it won't work and the examples I found just confused me more than anything.

    I want to display the vocabulary of a language in a treeview.

    So here I have a little example for you, what I have so far.

    Qt Code:
    1. from PyQt5.QtCore import *
    2. from PyQt5.QtGui import *
    3. from PyQt5.QtSql import *
    4.  
    5. class model(QSqlTableModel):
    6.  
    7. def __init__(self, db_file, mode):
    8. super().__init__()
    9. self.db = QSqlDatabase.addDatabase('QSQLITE')
    10. self.db.setDatabaseName(db_file)
    11. self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
    12. self.setTable("VOCABULARY")
    13. self.select()
    14.  
    15. if mode == "create":
    16. self.create_db()
    17. elif mode == "load":
    18. self.load_db()
    19.  
    20.  
    21. def create_db(self):
    22.  
    23. sql_create_vocab = '''CREATE TABLE VOCABULARY
    24. ([generated_id] INTEGER PRIMARY KEY,
    25. [word] TEXT NOT NULL,
    26. [translation] TEXT NOT NULL,
    27. [pos] TEXT NOT NULL,
    28. [example_sentence] TEXT NOT NULL,
    29. [example_translation] TEXT NOT NULL,
    30. [description] TEXT NOT NULL,
    31. [related_words] TEXT NOT NULL,
    32. [related_image] BLOB NOT NULL)'''
    33.  
    34.  
    35.  
    36. query = QSqlQuery(self.db)
    37. query.prepare(sql_create_vocab)
    38. query.exec()
    39. self.setQuery(query)
    40.  
    41. return True
    42.  
    43.  
    44. def load_db(self):
    45.  
    46. if self.db.open():
    47. log.debug('MODEL: connect to SQL Server successfully')
    48. # return True
    49. else:
    50. log.error('MODEL: connection failed')
    51. # return False
    52.  
    53. qry = QSqlQuery(self.db)
    54. log.info('MODEL: Processing Query')
    55. qry.prepare('SELECT * FROM VOCABULARY')
    56. qry.exec()
    57. self.setQuery(qry)
    58.  
    59.  
    60. def save_data(self):
    61. print("BEFORE " + self.record(0).value("related_words"))
    62. try:
    63. self.setData(self.index(0, 7), "XXXXX")
    64. self.submitAll()
    65. print("SUCCESS")
    66. except:
    67. print("FAIL")
    68.  
    69. print("AFTER " + self.record(0).value("related_words"))
    To copy to clipboard, switch view to plain text mode 

    Right here the last method is to check if it writes "XXXXX" to the db, but it won't.


    And then I set the model for the treeview, but the treeview also refuses to display something, unless I query with "SELECT * FROM VOCABULARY". I know the code here won't work, but what I don't understand is,
    if I have to query the database myself, or If I can use the high level model funtions - and if so, how does this work!

    Any help would be greatly appreciated.

    Cheers

    Fabian

  2. #2
    Join Date
    Jan 2006
    Location
    Bremen, Germany
    Posts
    554
    Thanked 86 Times in 81 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    First I don't think you need to set an executed query to the model - since you're working on a complete table setTable() should be enough.
    Then you have added a lot of constraints (NOT NULL) so only setting related_words will not work.

  3. #3
    Join Date
    Mar 2020
    Location
    Graz, Austria
    Posts
    6
    Thanks
    1
    Qt products
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    Alright, for understanding the principle I stripped my model class to this:

    Qt Code:
    1. class model(QSqlTableModel):
    2.  
    3. def __init__(self, db_file, mode):
    4. super().__init__()
    5. self.db = QSqlDatabase.addDatabase('QSQLITE')
    6. print(db_file)
    7. self.db.setDatabaseName("start.db")
    8. self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
    9. self.setTable("VOCABULARY")
    10. self.select()
    11.  
    12. print(self.rowCount())
    To copy to clipboard, switch view to plain text mode 

    I also removed all the constraints from the database.
    But The Row Count is showing 0, and the treeview is still empty. There should be exactly 1 row in the table.

  4. #4
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    The problem might be that you are trying to use a hierarchical view (a tree) to display a non-hierarchical table. In principle, a tree view should display one top-level entry for each row in the table, but that's it. There's no drill-down, because a table model's rows by definition have no children.

    Also, did you set the model on your view? (QAbstractItemView::setModel() in C++)

    You might also want to add some code to check to see if your database is actually open. Your code assumes the DB file is in your current working directory, but what you think and what python thinks that location is might be two different places.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  5. #5
    Join Date
    Mar 2020
    Location
    Graz, Austria
    Posts
    6
    Thanks
    1
    Qt products
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    Ah, alright, thanks for the reply!
    So firstly: at the beginning it actually worked to insert something in the treeview with model.setquery("SELECT * FROM VOCABULARY"). It inserted exactly what i wanted. But then it wasn't possible to do anything else. But through that (and from tkinter treeview) I know, that it only turns hirarchical if you want it to e.g. set parent items and give them child items.

    Secondly, my controller does this to start the model:

    Qt Code:
    1. def start_mode(self):
    2. self.start_vocab = model("data/start.db", "load")
    3. self.main_win.vocab_tv.setModel(self.start_vocab)
    To copy to clipboard, switch view to plain text mode 

    (vocab_tv is the treeview)

    And thirdly, I also checked the location of the file - tested it with the sqlite3 module simultaneously, if it works. Sqlite3 returns everything as plannend!

    This is the craziest thing!

    So here the logic broken down:

    1.) Create and instantiate a model, inherited from QSqlTableModel.
    2.) Add the Database QSQLITE (In this case)
    3.) Set the DatabaseName: the database File
    4.) Optional: Set EditStrategy
    5.) Set the Table - which is the one it reads from the file
    6.) Make it select()
    7.) Create a Treeview or Listview
    8.) Set the model for the Treeview or Listview

    That should be it, right!?

  6. #6
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    I would move step 6 to the end. select() results in signals being issued by the model that the view uses to update itself. If you haven't connected the model to the view yet, no one is listening to the model's signals at the time you do the select().
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  7. #7
    Join Date
    Mar 2020
    Location
    Graz, Austria
    Posts
    6
    Thanks
    1
    Qt products
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    So I called the select() method at the very last... moved it on different positions, but I think the very problem seems to lay with loading the data in the model, since the row count keeps being 0!

  8. #8
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    Don't know how else to help. Maybe use "DB Browser for SQLite" to make sure that your empty select actually does something there and then try to map that to python. I thought you would at least have to issue some kind of real SELECT statement - "SELECT * from MyTable" to get a populated result.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  9. #9
    Join Date
    Mar 2020
    Location
    Graz, Austria
    Posts
    6
    Thanks
    1
    Qt products
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    Yeah... thanks anyway for the troubleshooting with me! As newbie in Qt I felt really lost...

    The thing is, I use the DB Browser for SQlite on a regular basis, and it checked out fine... when I select and print out the rows, everything checks out as planned.

    I would hate to give up on PyQt... I will try it as isolated example again and post here what I have learned!


    Added after 22 minutes:


    Alright, I found it!! And this is why pyQt is so confusing:

    It ways never clear to me how exactly the QSqlTableModel knows, where to connect to the database. You just set db = "Database.db" and that's it? Until know I have just read here that the databse is set to the variable db. So the logical exaplanation why it won't work is, that the QSqlTableMOdel simply gets initialized before the database gets added,
    which is why it can't find data.

    So I did this:
    Qt Code:
    1. def __init__(self, db_file, mode):
    2. self.db = QSqlDatabase.addDatabase('QSQLITE')
    3. self.db.setDatabaseName(db_file)
    4. super().__init__()
    5.  
    6. print(self.database())
    7. self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
    8. self.setTable("VOCABULARY")
    9. self.select()
    To copy to clipboard, switch view to plain text mode 

    and it worked! It showed me the whole table as is in the tree_view. Next thing will be inserting and deleting records.

    Thank you so much for the help!
    Last edited by fschober; 21st March 2020 at 19:12.

  10. #10
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    Hmm. I am wondering if you need to derive your own model from QSqlTableModel at all. In my (limited) use of databases, I have used the QSqlTableModel as-is. If you look at the example code in the QSqlTableModel documentation Detailed Description section, it basically does everything you are doing without deriving a new class. Your problem stems entirely from the fact that you derived from the base class, and had to defer the base class initialization as a result.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  11. #11
    Join Date
    Mar 2020
    Location
    Graz, Austria
    Posts
    6
    Thanks
    1
    Qt products
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    Hmmm good point actually, after sleeping over it had the same realization. I just made my own class from it because I read somewhere that this QSqlTableModel should be used only with derivation, and not on its own.
    But what do you know, you can't always believe everything on the internet...

  12. #12
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: PyQT: The QSqlTableModel and how to wrap my head around it

    There are some Qt models that generally must be used with derivation (eg. QSortFilterProxyModel if you need filtering not implemented in the class) and of course any of the QAbstract* classes, but QSqlTableModel is more or less complete.

    But what do you know, you can't always believe everything on the internet...
    What? Say it isn't so!
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

Similar Threads

  1. Build Qt-based app on Mac OS X, head file not found
    By leonardyu in forum Qt Programming
    Replies: 3
    Last Post: 25th February 2015, 15:01
  2. Replies: 2
    Last Post: 9th April 2013, 07:26
  3. How to display picture from raw data without head?
    By luochen601 in forum Qt Programming
    Replies: 1
    Last Post: 23rd July 2010, 11:30
  4. QNetworkAccessManager::head not working
    By jonks in forum Qt Programming
    Replies: 4
    Last Post: 21st October 2009, 23:23
  5. Replies: 1
    Last Post: 15th March 2009, 21:01

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.