I have a recordset from a DB that I am trying to get to display in a
wxgrid. I think I am missing something simple.
I would greatly appreciate it if someone can help me out.
USE [ToolData]
GO
/****** Object: Table [dbo].[ToolData] Script Date: 04/18/2012
16:34:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ToolData](
[TimeStamp] [datetime] NOT NULL,
[LotID] [nvarchar](18) NOT NULL,
[VariableName] [nvarchar](12) NOT NULL,
[VariableValue] [nvarchar](25) NULL,
[EyelitTimeStamp] [datetime] NULL,
CONSTRAINT [PK_ToolData] PRIMARY KEY CLUSTERED
(
[TimeStamp] ASC,
[LotID] ASC,
[VariableName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
import pyodbc as p
import wx
import wx.grid
LOTID = '987-ASTI'
class TestTable(wx.grid.PyGridTableBase):
def __init__(self):
wx.grid.PyGridTableBase.__init__(self)
self.rowLabels = ["1", "2", "3", "4", "5", "6", "7", "8", "9",
"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20",
"21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31",
"32", "33", "34", "35"]
self.colLabels = ["TIMESTAMP", "LotID", "ToolID", "Z1A38",
"Z1A39", "Z1A44", "Z1A45", "Z2A38", "Z2A39"]
self._rawData = []
server = 'ipaddy\SQLEXPRESS'
database = 'ToolData'
table = 'ToolData'
field = 'VariableName'
UID = 'pwd'
PWD = 'pwd'
strHolder = ''
connStr = ( r'DRIVER={SQL Server};SERVER=' +
server + ';DATABASE=' + database + ';' +
'UID=' + UID + ';' + 'PWD=' + PWD)
db = p.connect(connStr)
c = db.cursor()
sql = ('SELECT TIMESTAMP, LotID, [ToolID], [Z1A38], [Z1A39],
[Z1A44], [Z1A45], [Z2A38], [Z2A39] ' +
'FROM (SELECT TIMESTAMP, LotID, ' +
'CASE ' +
'WHEN ISNUMERIC(VariableValue) = 1 THEN
CONVERT(nvarchar(25),isnull(VariableValue,0.0)) ' +
'ELSE variableValue END AS VariableValue, VariableName
' +
'FROM [ToolData].[dbo].[ToolData] where LotID = \'' +
LOTID + '\' ' +
') TBL ' +
'PIVOT ( MAX(VariableValue) FOR VariableName IN
( [ToolID], [Z1A38], [Z1A39], [Z1A44], [Z1A45], [Z2A38], [Z2A39]) )
AS PVT;')
c.execute(sql)
rs = c.fetchall()
for row in range(len(rs)):
for col in range(len(rs[row])):
self._rawData.append(str(rs[row][col]))
#print self._rawData
def GetNumberRows(self):
return 35
#return len(self._rawData)
def GetNumberCols(self):
return 9
#return len(self._rawData[0])
def IsEmptyCell(self, row, col):
return False
def GetValue(self, row, col):
return self._rawData[col],[row]
def SetValue(self, row, col, value):
pass
def GetColLabelValue(self, col):
return self.colLabels[col]
def GetRowLabelValue(self, row):
return self.rowLabels[row]
class TestFrame(wx.Frame):
def __init__(self):
wx.Frame.__init__(self, None, title="Grid
Table",size=(500,200))
grid = wx.grid.Grid(self)
table = TestTable()
grid.SetTable(table, True)
app = wx.PySimpleApp()
frame = TestFrame()
frame.Show()
app.MainLoop()
I expect the first pictured table, get the second pictured table:
Here is the actual data in CSV format