Linking wxGrid to SQLite database in Python - an example

Released: Sat, 08 Nov 2014 22:15:00 +0000

Here's how I linked a wx.Grid widget to an underlying SQLite grid. Note that this didn't need me to override the GridTableBase which might be needed for more advanced purposes.

What this demonstration will do is produce a small window and open (if it doesn't already exist) a file called Grid_SQLite_demo_db. You can put information into the grid and close it. When you re-open it, the information is still there with no explicit save from the user needed.

We're hoping to make Salstat work like this to reduce the reliance on saving files. Data, meta-data and output (possibly history and sources of data) can be recorded into a single file as well so that an entire analysis session can be audited.

# a demonstration of wxPython's grid with various functions, and linked to SQLite
# (c) 2014 Alan James Salmoni. Released under the Lesser GPL license.

import wx
import wx.grid as gridlib
import sqlite3 as sqlite

import string

class Grid(gridlib.Grid):
def __init__(self, parent, db):
gridlib.Grid.__init__(self, parent)
self.CreateGrid(50, 20)

self.db = db
self.cur = self.db.con.cursor()
if self.db.exists:
# read labels in from DATA table
meta = self.cur.execute("SELECT * from DATATABLE")
labels = []
for i in meta.description:
labels.append(i[0])
labels = labels[1:]
for i in range(len(labels)):
self.SetColLabelValue(i, labels[i])
# then populate grid with data from DATA
all = self.cur.execute("SELECT * from DATATABLE ORDER by DTindex")
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.SetCellValue(row_num, i, str(cells[i]))
else:
labels = """CREATE TABLE DATATABLE\n(DTindex INTEGER PRIMARY KEY,\n"""
for i in range(self.GetNumberCols()):
labels=labels+(self.GetColLabelValue(i)+" string,\n")
labels = labels[0:-1]
labels = labels[0:-1] # very bad string code here!
labels = labels + """);"""
self.cur.execute(labels)
for i in range(self.GetNumberRows()):
RowLable = self.GetRowLabelValue(i)
self.cur.execute("INSERT into DATATABLE (DTindex) values (%d)"%i)
self.db.con.commit()

self.Bind(gridlib.EVT_GRID_CELL_CHANGE, self.CellContentsChanged)
#self.Bind(gridlib.EVT_

def CellContentsChanged(self, event):
x = event.GetCol()
y = event.GetRow()
val = self.GetCellValue(y, x)
if val == "":
val = "null"
ColLabel = self.GetColLabelValue(x)
InsertCell = "UPDATE DATATABLE SET %s = ? WHERE DTindex = %d"%(ColLabel, y)
self.cur.execute(InsertCell, [(val),]) # protects against injection of dangerous 'val'
# needs to be done for 'ColLabel' too. 'y' is fine as it's an integer
self.db.con.commit() # do commit here to ensure data persistence
# also, retrieve formatting for variable and format the output
self.SetCellValue(y, x, val)

class TestFrame(wx.Frame):
def __init__(self, parent, db):
wx.Frame.__init__(self, parent, -1, "TSP: Grid linked to SQLite Demo", size=(640,480))
grid = Grid(self, db)

class GetDatabase():
def __init__(self, f):
# check db file exists
try:
file = open(f)
file.close()
except IOError:
# database doesn't exist - create file & populate it
self.exists = 0
else:
# database already exists - need integrity check here
self.exists = 1
self.con = sqlite.connect(f)

if __name__ == '__main__':
import sys
# first open an existing file or create one
db = GetDatabase("Grid_SQLite_demo_db")
app = wx.PySimpleApp()
frame = TestFrame(None, db)
frame.Show(True)
app.MainLoop()




Contact us:

We're happy to hear from you

Email us
Contact form

Salstat's social world:





Salstat is an open source project fostered by Thought Into Design Ltd
Thought Into Design Ltd is registered in England and Wales (Companies House number 7367421)