How to update the grid with the database?

wxPython Users
1

Hi, all

I am using wxGrid to display the content of a database in my app. Because we have a lot of data in the database, it's a large table. And I have two issues:

1) I find that It is quite slow to just override GetValue() method. Because every call to GetValue() invokes a database query. And when I scroll the grid, tens of cells need to be displayed, and GetValue() need to be called many times, and the screen flickers. I think the performance can be improved by getting values of all visible cells once. But I do not know how to do it. I guess that maybe there is a "standard" way to do this.

2) How to make the ww.Grid automatically update when the content of database changes? For example, when new data are add, then how to make the grid automatically add new rows to display them? I tried AppendRows(), but it doesn't work!

This is my code, any help would be appreciated!

···

#=============================================================================
class HugeTable(PyGridTableBase):
    def __init__(self):
        PyGridTableBase.__init__(self)
        self.__database_ = wx.GetApp().database_
    def GetNumberRows(self):
        count = self.__database_.getRecordCount()
        return count
    def GetNumberCols(self):
        return 4
    def IsEmptyCell(self, row, col):
        return False
    def GetValue(self, row, col):
        # 1)It's quite slow to get cell values and display them one by one, if it is possible to
        # get values of all current visible cells once from database and then display,
        # it may be much fater, but I do not know how to.
        value = self.__database_.getValue(row, col)
        return value
    def SetValue(self, row, col, value):
        # do nothing
        pass
    def GetNumberRows(self, numRow):
        # I do not know what should be filled here, is "pass" OK?
        pass

class DataViewerGrid(Grid):
    def __init__(self, parent):
        Grid.__init__(self, parent, -1)
        self.SetRowLabelSize(0)

        table = HugeTable()
        self.SetTable(table, True)

        self.EnableEditing(False)
    def onDataChange(self):
        # 2)When database changes, how to update the grid? When the database changes,
        # the grid don't.
        # I have tried AppendRows():
        # self.AppendRows(self.__database_.getRecordCount() - self.GetNumberRows())
        # but the two methods always return the same value.
        pass

--------------
bruce.who.hk
2006-08-23

2

Bruce Who wrote:

Hi, all

I am using wxGrid to display the content of a database in my
app. Because we have a lot of data in the database, it's a
large table. And I have two issues:

1) I find that It is quite slow to just override GetValue()
method. Because every call to GetValue() invokes a database
query. And when I scroll the grid, tens of cells need to be
displayed, and GetValue() need to be called many times, and
the screen flickers. I think the performance can be improved
by getting values of all visible cells once. But I do not
know how to do it. I guess that maybe there is a "standard"
way to do this.

Here is a quick suggestion.

Can you replace
        value = self.__database_.getValue(row, col)
with
        rowData = self.__database_.getRow(row)

If so, you can replace

    def GetValue(self, row, col):
        value = self.__database_.getValue(row, col)
        return value
with

    def __init__(self):
        [...]
        self.currentRow = -1

    def GetValue(self, row, col):
        if row != self.currentRow:
            self.rowData = self.__database_.getRow(row)
            self.currentRow = row
        return self.rowData[col]

Now you only have one database lookup per row instead of one per cell.

It is possible that self.__database_.getValue(row,col) is doing something
like this anyway, in which case it will not make much difference.

You can get a lot cleverer, but of course it gets more complicated.

I use a database cursor, from which I retrieve 100 rows at a time, and store
them in a 2-dimensional list, so I can get those values quickly. You need to
work out a system to trigger retrieving the next 100 rows, etc. I can give
you some ideas from what I have done, but our requirements may not be the
same.

2) How to make the ww.Grid automatically update when the
content of database changes? For example, when new data are
add, then how to make the grid automatically add new rows to
display them? I tried AppendRows(), but it doesn't work!

This is explained in the demo program, but here is the relevant extract -

            # tell the grid we've added a row
            msg = gridlib.GridTableMessage(self, # The table
                    gridlib.GRIDTABLE_NOTIFY_ROWS_APPENDED, # what we did to
it
                    1 # how many
                    )

            self.GetView().ProcessTableMessage(msg)

HTH

Frank Millman