Results 1 to 12 of 12

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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
    502
    Thanked 75 Times in 71 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
    4,714
    Thanks
    259
    Thanked 759 Times in 749 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

    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
    4,714
    Thanks
    259
    Thanked 759 Times in 749 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

    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
    4,714
    Thanks
    259
    Thanked 759 Times in 749 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

    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.

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.