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)

Martijn Pieters · Accepted Answer · 2012-08-01T09:26:05.977

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.

Thanks, it's OK now. Your first example is working well and as you said it's end up with one empty row. But your second example doesn't work well and it just make a single row and fill it. Sorry about my silly questions, I'm new in python. — Amir, Aug 01 '12 at 09:13
@Amir: right, that means that `rowcount` isn't reflecting the number results properly, which *can* happen in sqlite. Set the row count to *0* and insert rows before you insert data. Updated my answer. — Martijn Pieters, Aug 01 '12 at 09:15
@Amir: And further refined to a) advise you to get the row count one way or the other, and b) use the cursor as an iterator instead of calling `.fetchall()`. — Martijn Pieters, Aug 01 '12 at 09:27
Thanks, I'm learning python and you're helping me alot with great information. — Amir, Aug 01 '12 at 09:55

Inserting data from sqlite database to QTableWidget using PyQt in Python

1 Answers1