Populating wx.grid with MySQL table

[acrocephalus]

Hello!
I have designed a dialog with a grid widget. I would like to populate it with data from a MySQL table which has 10396 rows and 5 columns, as the grid widget. How can I do it? Then, how can I select a row from the grid widget and store it in a variable?
Cheers!

#! /usr/bin/env python
# TestDialogs.py

import wx, MySQLdb, wx.lib.intctrl, wx.grid

class Ornithobase(wx.Frame):
	def __init__(self, parent, id, title = 'Select species'):
		wx.Frame.__init__(self, parent, id, title, size=(800,650))
		db= MySQLdb.connect(host='localhost', user='acrocephalus' , passwd='acrsci00', db='Ornithobase')
		cursor = db.cursor()
#Define main panel
		panel = wx.Panel(self, -1)
		vbox = wx.BoxSizer(wx.VERTICAL)
	#Define sizers
		#Horizontal sizers
		SpeciesTableSizer = wx.BoxSizer(wx.HORIZONTAL)
		BtnSizer = wx.BoxSizer(wx.HORIZONTAL)

	#Add species table widget
		Species = wx.grid.Grid(panel, -1, size=(750,550))
		Species.CreateGrid(10396,5)
		Species.SetColLabelValue(0, 'Order')
		Species.SetColLabelValue(1, 'Family')
		Species.SetColLabelValue(2, 'Genus')
		Species.SetColLabelValue(3, 'Species')
		Species.SetColLabelValue(4, 'English name')
		Species.SetRowLabelSize(0)
		SpeciesTableSizer.Add(Species, wx.ALIGN_CENTER | wx.ALL,0 )
	
	#Add buttons
		useBtn = wx.Button(panel, -1, 'Use')
		rmvBtn = wx.Button(panel, -1, 'Remove')
		doneBtn = wx.Button(panel, -1, 'Done')
		BtnSizer.Add(useBtn, 0, wx.ALL, 5)
		BtnSizer.Add(rmvBtn, 0, wx.ALL, 5)
		BtnSizer.Add(doneBtn, 0, wx.ALL, 5)
		vbox.Add(SpeciesTableSizer, 0, wx.ALIGN_CENTER | wx.ALL, 5)
		vbox.Add(BtnSizer, 0, wx.ALIGN_CENTER | wx.ALL, 5)


		panel.SetSizer(vbox)
		self.Centre()
		self.Show(True)



app = wx.App()
Ornithobase(None, -1)
app.MainLoop()

[acrocephalus]

Can anyone help?
Cheers!

Dani

[Beat_Slayer]

You must use the 'SetCellValue(self, row, col, s)' attrivute of the grid.

Make a loop, read row, iterate trough items, and insert the data on the cells.

Cheers and Happy coding

[acrocephalus]

Thank you so much. Now, how can I select a row and store it in a variable?
Cheers!

Dani

[Beat_Slayer]

I believe with a GetCellValue(self, row, col) in a nice loop.

ncols = 7
row = 3
row = [self.GetCellValue(row, col) for col in range(ncols)]

Cheers and Happy coding

[acrocephalus]

Hello!
This isn't what I need, but I think now I am on the right way, using the

event.GetRow()

However, when I run the code (at the end of the post) I get this traceback

Traceback (most recent call last):
  File "Species.py", line 128, in <module>
    SpeciesGrid(None, -1)
  File "Species.py", line 96, in __init__
    Species.Bind(wx.grid.EVT_GRID_SELECT_CELL, self.OnSelectCell, Species.grid)
AttributeError: 'SpeciesGrid' object has no attribute 'OnSelectCell'

I create the grid at lines 94-95, and I bind it to the OnSelectCell function (defined on lines 76-77) on line 96. I have tried some things without success. How can I solve it?
Cheers!
Dani

#! /usr/bin/env python
# TestDialogs.py

import wx, MySQLdb, wx.lib.intctrl, wx.grid
ID_SPECIES=1

class SpeciesGrid(wx.Frame):
	def __init__(self, parent, id, title = 'Select species'):
		db= MySQLdb.connect(host='localhost', user='acrocephalus' , passwd='acrsci00', db='Ornithobase')
		cursor = db.cursor()
		def Order():
			sql = 'select OrderScientific from Species'
			cursor.execute(sql)
			result = cursor.fetchall()
			result2 = []
			x = 0
			for record in result:
				for field in record:
					users = str(field)
					result2.append(users)
					x += 1
			return result2

		def Family():
			sql = 'select Family from Species'
			cursor.execute(sql)
			result = cursor.fetchall()
			result2 = []
			x = 0
			for record in result:
				for field in record:
					users = str(field)
					result2.append(users)
					x += 1
			return result2

		def Genus():
			sql = 'select Genus from Species'
			cursor.execute(sql)
			result = cursor.fetchall()
			result2 = []
			x = 0
			for record in result:
				for field in record:
					users = str(field)
					result2.append(users)
					x += 1
			return result2

		def Species():
			sql = 'select Species from Species'
			cursor.execute(sql)
			result = cursor.fetchall()
			result2 = []
			x = 0
			for record in result:
				for field in record:
					users = str(field)
					result2.append(users)
					x += 1
			return result2

		def EnglishName():
			sql = 'select EnglishName from Species'
			cursor.execute(sql)
			result = cursor.fetchall()
			result2 = []
			x = 0
			for record in result:
				for field in record:
					users = str(field)
					result2.append(users)
					x += 1
			return result2

		def OnSelectCell(self, event):
			print event.GetRow()

		self.Order = Order()
		self.Family = Family()
		self.Genus = Genus()
		self.Species = Species()
		self.EnglishName = EnglishName()
		wx.Frame.__init__(self, parent, id, title, size=(900,650))
#Define main panel
		panel = wx.Panel(self, -1)
		vbox = wx.BoxSizer(wx.VERTICAL)
	#Define sizers
		#Horizontal sizers
		SpeciesTableSizer = wx.BoxSizer(wx.HORIZONTAL)
		BtnSizer = wx.BoxSizer(wx.HORIZONTAL)

	#Add species table widget
		Species = wx.grid.Grid(panel, -1, size=(826,550))
		Species.CreateGrid(10396,4)
		Species.Bind(wx.grid.EVT_GRID_SELECT_CELL, self.OnSelectCell, Species.grid)
		Species.SetColLabelValue(0, 'Order')
		Species.SetColLabelValue(1, 'Family')
		Species.SetColLabelValue(2, 'Scientific name')
		Species.SetColLabelValue(3, 'English name')
		Species.SetRowLabelSize(0)
		for i in range(0,len(self.Order)):
			Species.SetCellValue(i,0,self.Order[i])
		for i in range(0,len(self.Family)):
			Species.SetCellValue(i,1,self.Family[i])
		for i in range(0,len(self.Genus)):
			Species.SetCellValue(i,2,self.Genus[i]+' '+self.Species[i])
		for i in range(0,len(self.EnglishName)):
			Species.SetCellValue(i,3,self.EnglishName[i])
		Species.AutoSize()
		SpeciesTableSizer.Add(Species, wx.ALIGN_CENTER | wx.ALL,0 )
	
	#Add buttons
		useBtn = wx.Button(panel, -1, 'Use')
		rmvBtn = wx.Button(panel, -1, 'Remove')
		doneBtn = wx.Button(panel, -1, 'Done')
		BtnSizer.Add(useBtn, 0, wx.ALL, 5)
		BtnSizer.Add(rmvBtn, 0, wx.ALL, 5)
		BtnSizer.Add(doneBtn, 0, wx.ALL, 5)
		vbox.Add(SpeciesTableSizer, 0, wx.ALIGN_CENTER | wx.ALL, 5)
		vbox.Add(BtnSizer, 0, wx.ALIGN_CENTER | wx.ALL, 5)

		panel.SetSizer(vbox)
		self.Centre()
		self.Show(True)

app = wx.App()
SpeciesGrid(None, -1)
app.MainLoop()

[acrocephalus]

I have read the traceback carefully and I have seen where the problem is, so I defined the OnSelectCell function just after creating the SpeciesGrid class.

Dani