sqlitewxpythonwxgrid

wxGrid row numbers don't sync with SQLite3 database


I have a wx.Grid that is populated from a SQLite3 database. The user enters the data in text controls and then clicks the "Commit Values" button. The values are written to the database and then the function loads the database into the grid. The database has an index and it starts at '1'. The table looks like this:

CREATE TABLE Cycles ( index INTEGER, First INTEGER, Second TEXT, Third INTEGER, Fourth INTEGER, Fifth INTEGER, Sixth INTEGER, Seventh INTEGER, Eighth INTEGER, PRIMARY KEY(index)

When the database loads, the column numbers jump and begin at '0' instead of '1' and the first row of values populate row '1'leaving row '0' blank. If I change the row number definition in onLoadTable() to 'rowNumb = row +1', then, when the "Commit Values" button is clicked, the rows begin at '1' but the values populate the second row leaving row '1' blank. Here is fully functional, reproducible code:

import wx
import wx.lib.scrolledpanel as scrolled
import sqlite3 as sqlite
import wx.grid as gridlib


class MasterFrame(wx.Frame):
    def __init__(self):
        wx.Frame.__init__(self, None, wx.ID_ANY,"",size=(900,400))

        panel = wx.Panel(self, -1)

        self.db = db
        self.c = self.db.conn.cursor()

        vbox = wx.BoxSizer(wx.VERTICAL)

        self.grid = wx.grid.Grid(panel)
        rowN = self.c.execute('SELECT Count(*) FROM Cycles')
        val = rowN.fetchone()
        rowNum = val[0] + 3
        self.grid.CreateGrid(rowNum,8)
        self.grid.Scroll(0,0)

        self.enterBtn = wx.Button(panel, -1, "Commit Values")
        self.Bind(wx.EVT_BUTTON, self.onEnter, self.enterBtn)

        vbox.Add(self.enterBtn)
        vbox.Add(self.grid)
        panel.SetSizer(vbox)
        panel.Fit()

    def onEnter(self, event):
        First = 1
        Second = 'mine'
        Third = 1
        Fourth = .5
        Fifth = 1
        Sixth = 20
        Seventh = 4
        Eighth = 10

        self.c.execute("INSERT INTO datatable VALUES (NULL,?,?,?,?,?,?,?,?);",(First,Second,Third,Fourth,Fifth,Sixth,Seventh,Eighth))
        self.db.conn.commit()
        self.onLoadTable()

    def onLoadTable(self):       
        self.grid.ClearGrid()      
        for row in range(12):
            rowNumb = row #+ 1
            self.grid.SetRowLabelValue(row, "%s" %rowNumb)

        meta = self.c.execute('SELECT * from datatable') 
        labels = []
        for i in meta.description:
            labels.append(i[0])
        labels = labels[1:]
        for i in range(len(labels)):
            self.grid.SetColLabelValue(i, labels[i])

        all = self.c.execute('SELECT * from datatable')
        for row in all:
            row_num = row[0]
            cells = row[1:]
            for i in range(len(cells)):
                if cells[i] != None and cells[i] != "null":
                    self.grid.SetCellValue(row_num, i, str(cells[i]))
        self.db.conn.commit()

class GetDatabase():
    def __init__(self):
        self.conn = sqlite.connect("data4.db") 

if __name__ == "__main__":
    db=GetDatabase()
    app = wx.App(False)
    frame = MasterFrame()
    frame.Show()
    app.MainLoop()

If I add ...WHERE index=%d" %rowNumb to the SELECT statements I get sqlite3.OperationalError: near "index": syntax error.


Solution

  • Grid starts at (0,0) so try subtracting 1 from row_num i.e.

    self.grid.SetCellValue(row_num-1, i, str(cells[i]))