Inserting data from sqlite database to QTableWidget using PyQt in Python

Question

I want to insert my data which is stored in a sqlite table, to a QTableWidget. I use two for loop to find the data and index. after each iteration I print the data in console and it is OK but when it displays the table widget there is only the first row and the last row filled with the data. Any idea to solve this problem?

It's obvious that tblTable is a QTableWidget!

Here is this part of the code:

cursor.execute('''SELECT * FROM MyTable''')
for index , form in enumerate(cursor.fetchall()):
    i = 0
    for item in form:
        print(str(item))
        self.tblTable.setItem(index, i, QtGui.QTableWidgetItem(str(item)))       
        i = i + 1
    self.tblTable.insertRow(1)

Answer 1

You keep inserting your new row at position 1. What happens is that the previously entered data is then moved up one row, at which point you overwrite that data in the next loop.

So, first iteration everything is inserted in row 0, you add a row at index 1. Then you update row 1 with data, and insert another row at position 1, making the previously modified row move to row 2. Next loop, you overwrite the data on row 2, insert another empty row at position 1, moving the row with data to position 3 and you overwrite it again, etc., etc.

Set the row-count to 0 at the start, and insert rows as you need them before you insert your column data:

cursor.execute('''SELECT * FROM MyTable''')
self.tblTable.setRowCount(0)
for row, form in enumerate(cursor):
    self.tblTable.insertRow(row)
    for column, item in enumerate(form):
        print(str(item))
        self.tblTable.setItem(row, column, QtGui.QTableWidgetItem(str(item)))       

I am not that familiar with the QtTableWidget, it could be that continually adding rows in not going to perform as well as setting the number of rows up front.

If sqlite's cursor.rowcount attribute is properly updated on your query (it not always is), you'd be better off calling .setRowCount with that value:

cursor.execute('''SELECT * FROM MyTable''')
self.tblTable.setRowCount(cursor.rowcount)
for row, form in enumerate(cursor):
    for column, item in enumerate(form):
        self.tblTable.setItem(row, column, QtGui.QTableWidgetItem(str(item)))       

If the .rowcount value is not available (set to 1 or similar), perhaps first asking the database for the number of rows can help:

rowcount = cursor.execute('''SELECT COUNT(*) FROM MyTable''').fetchone()[0]
self.tblTable.setRowCount(rowcount)
cursor.execute('''SELECT * FROM MyTable''')
for row, form in enumerate(cursor):
    for column, item in enumerate(form):
        self.tblTable.setItem(row, column, QtGui.QTableWidgetItem(str(item)))       

In all examples above, I also renamed you variables to something a little closer to their use, and used enumerate on the item loop as well. Last, but not least, the cursor can act as an iterator, meaning you can loop over rows directly without calling .fetchall() and rows will be fetched as needed.