Received: by 10.224.97.134 with SMTP id l6mr14239qan.6.1334791067385;
        Wed, 18 Apr 2012 16:17:47 -0700 (PDT)
X-BeenThere: wxpython-users@googlegroups.com
Received: by 10.224.59.16 with SMTP id j16ls1827427qah.8.gmail; Wed, 18 Apr
 2012 16:17:43 -0700 (PDT)
Received: by 10.224.184.70 with SMTP id cj6mr17897qab.3.1334791063002;
        Wed, 18 Apr 2012 16:17:43 -0700 (PDT)
Received: by 10.224.8.211 with SMTP id i19msqai;
        Wed, 18 Apr 2012 15:39:25 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.236.180.36 with SMTP id i24mr684233yhm.3.1334788765430; Wed,
 18 Apr 2012 15:39:25 -0700 (PDT)
Authentication-Results: ls.google.com; spf=pass (google.com: domain of
 halowa...@gmail.com designates internal as permitted sender)
 smtp.mail=halowa...@gmail.com; dkim=pass
 header...@gmail.com
Received: by r9g2000yqd.googlegroups.com with HTTP; Wed, 18 Apr 2012 15:39:25
 -0700 (PDT)
Date: Wed, 18 Apr 2012 15:39:25 -0700 (PDT)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20100101 Firefox/11.0,gzip(gfe)
Message-ID: <f6cc4754-3047-4eba-b1ba-370098d132a6@r9g2000yqd.googlegroups.com>
Subject: wxGrid problem
From: Haloway13 <halowa...@gmail.com>
To: wxPython-users <wxpython-users@googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

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