wxGrid problem

[Haloway13]

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:

https://plus.google.com/photos/106297050976164109460/albums/5732871841178413873?authkey=COWd2rq92ZvXbw

Here is the actual data in CSV format

[Tim Roberts]

Haloway13 wrote:
> 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.

> ...

>
> I expect the first pictured table, get the second pictured table:

I don't know what the PIVOT clause does, but I'm guessing it's related
here. It's not standard SQL. It looks like you're getting each record
duplicated a number of time, with some kind of ordinal attached to the
field data.

As an experiment, you might try the query without the PIVOT. If that
works, then you may need to do some extra processing on the data to undo
whatever change the PIVOT clause did.
--
Tim Roberts, ti...@probo.com
Providenza & Boekelheide, Inc.