populating wxgrid with data from mysql data base.

[Nemius]

Hi all,

I am working on program that will connect to MySQL database, colect data from table and show them in grid. Where user can edit cell, and then on enter key changes will bi save directly into database table. Curent problem is that I am stuck at importing data into grid. I know that need for loop and SetCellValue, but not sure how to configure that. Can anyone help me.

Here is code:

import wx
import wx.grid as gridlib
import MySQLdb

#Class main grid
class MyGrid(gridlib.Grid):
    def __init__(self, parent):
        gridlib.Grid.__init__(self, parent, )

        # Connecting to database
        def getData():
            db = MySQLdb.connect("127.0.0.1", "user", "pass", "database")
            cursor = db.cursor()
            query = "SELECT * FROM setup"
            cursor.execute(query)
            data = []
            res = cursor.fetchall()
            for i in res:
                data.append(i)
            return data

        #Column name
        colnames = ["idSetup", "idKomitent", "Drzava", "Prg", "Provajder", "ServerName", "DataBaseName", "Sifarnici",
                    "Promena"]

        #Setup
        data = getData()
        self.data = data
        self.colnames = colnames

        #Number of rows
        def GetNumberRows(self):
            return int(len(self.data))

        #Number of columns
        def GetNumberCols(self):
            return int(len(self.colnames))

        #Grid creation
        col = GetNumberCols(self)
        row = GetNumberRows(self)
        self.CreateGrid(row, col)
        self.ChangedValue = False

        #Seting up name of columns
        self.SetColLabelValue(0, "idSetup")
        self.SetColLabelValue(1, "idKomitent")
        self.SetColLabelValue(2, "Drzava")
        self.SetColLabelValue(3, "Prg")
        self.SetColLabelValue(4, "Provajder")
        self.SetColLabelValue(5, "ServerName")
        self.SetColLabelValue(6, "DataBaseName")
        self.SetColLabelValue(7, "Sifarnici")
        self.SetColLabelValue(8, "Promena")

        #Insertind data

        def GetValue(self, row, col):
            return str(self.data[row][1].get(self.GetColLabelValue(col), ""))

        def SetValue(self, row, col, value):
            self.data[row][1][self.GetColLabelValue(col)] = value
        #Here is where I am stuck
        for i in data:
            for k in i:
                self.SetCellValue(row, col, '%s')

        #Events
        self.Bind(gridlib.EVT_GRID_CELL_CHANGE, self.onCellChange)
        self.Bind(wx.EVT_KEY_DOWN, self.onKeyDown)

    def onCellChange(self, evt):
        pass

    #Enter key down
    def onKeyDown(self, evt):
        if evt.GetKeyCode() != wx.WXK_RETURN:
            evt.Skip()
            return

        if evt.ControlDown():  # the edit control needs this key
            evt.Skip()
            return

        self.DisableCellEditControl()
        success = self.MoveCursorRight(evt.ShiftDown())

        if not success:
            newRow = self.GetGridCursorRow() + 1

            if newRow < self.GetTable().GetNumberRows():
                self.SetGridCursor(newRow, 0)
                self.MakeCellVisible(newRow, 0)
            else:
                newRow = self.GetGridCursorRow() + 1
                self.appendRow()
                self.SetGridCursor(newRow, 0)
                self.MakeCellVisible(newRow, 0)
                self.ChangedValue = False

    def appendRow(self):
        pass

#Class main window
class GlavniProzor(wx.Frame):
    def __init__(self):
        wx.Frame.__init__(self, parent=None, title = "Editor", size = (500, 1000))
        self.Show()
        self.CenterOnScreen()

        #Panel
        panel = wx.Panel(self)
        mygrid = MyGrid(panel)

        #Box
        sizer = wx.BoxSizer(wx.VERTICAL)
        sizer.Add(mygrid, 1, wx.EXPAND)
        panel.SetSizer(sizer)
        sizer.Fit(self)
#Run
if __name__ == "__main__":
    app = wx.App()
    frame = GlavniProzor()
    app.MainLoop()

[Gribouillis]

Hi, it's been a long time since I last used wxpython, but I think it should be something like

for i, seq in enumerate(data):
    for j, v in enumerate(seq):
        self.SetCellValue(i, j, v)

You should find good examples in the Mouse vs Python series, which cover many things concerning wx python, see this article for an introduction to grids.

Also don't miss the class reference such as this one.

[Nemius]

Hi.

Thx for your reply.

I try this but not woking, I got error message "TypeError: String or Unicode type required". Also i alredy chek Mouse vs Python, but could not find any advance stuff, only basic, or I just dont know how to lok for awnser for my problem. I am trying to figure this one for 2 month now, and starting to give up. People say that you can find everyting on internet. I just canot, i post this question on Python facebook group, and admin just delete my post, whithout telling me why?? StackOwerflo is also dead end for me. I try to read wxPython manual and to look demo code, but that is just confusing stuff and its only for someone ho is expirienc whit some other programing language and want to learn python. For someone ho is starting to learn programing Python is not as easy as people telling. Oh well, my search continue.

Once again thanks for your replay.

[Gribouillis]

Can you post the whole exception traceback that gets printed instead of only TypeError: String or Unicode type required" ?

[Nemius]

Yeah, sure.

Traceback (most recent call last):
  File "C:/Users/*****/PycharmProjects/MySQL Setup TE/main.py", line 131, in <module>
    frame = GlavniProzor()
  File "C:/Users/*****/PycharmProjects/MySQL Setup TE/main.py", line 121, in __init__
    mygrid = MyGrid(panel)
  File "C:/Users/*****/PycharmProjects/MySQL Setup TE/main.py", line 74, in __init__
    self.SetCellValue(i, j, v)
  File "C:\Python27\lib\site-packages\wx-3.0-msw\wx\grid.py", line 2016, in SetCellValue
    return _grid.Grid_SetCellValue(*args, **kwargs)
TypeError: String or Unicode type required

Thx.

[Gribouillis]

The error means that you tried to insert in a grid cell a value v which type is not str or unicode, and the Grid type doesn't accept that. We can get more information about what this value was by using

for i, seq in enumerate(data):
    for j, v in enumerate(seq):
        try:
            self.SetCellValue(i, j, v)
        except TypeError:
            raise RuntimeError(('SetCellValue failed with TypeError', i, j, v))

When we'll see which value v was rejected, we'll be able to take appropriate action such as inserting nothing, or another value.

[Nemius]

I got this mesage.

Traceback (most recent call last):
  File "C:/Users/*****/PycharmProjects/MySQL Setup TE/main.py", line 134, in <module>
    frame = GlavniProzor()
  File "C:/Users/*****/PycharmProjects/MySQL Setup TE/main.py", line 124, in __init__
    mygrid = MyGrid(panel)
  File "C:/Users/*****/PycharmProjects/MySQL Setup TE/main.py", line 77, in __init__
    raise RuntimeError(('SetCellValue failed with TypeError', i, j, v))
RuntimeError: ('SetCellValue failed with TypeError', 0, 0, 7L)

I donot know why return 7L, when in table its only 7. But when i look what program colect from table i notice that he put L behinde numbers. This is how row look.

(7L, 1L, 'SRB', 'LDM Soft Robno materijalno poslovanje - Maloprodaja', 'MySQL ODBC 3.51 Driver', 'localhost', 'mySqlRmp', 'Sifarnici', datetime.datetime(2016, 6, 1, 6, 50, 39))

Maybe i geting error because its not properly colected from table?? Maybe i need to transform all data into string and then import into table. But problem is how i gona then edit cell and return changes back to the table?

Thank you for helping me.

[Gribouillis]

The 7L comes because you are using python 2 instead of python 3, which I disapprove, but it is your choice, and there are two integer types in python 2, namely int and long, so 7L is a long because the module you used to read the database returns long integers when it meets an integer.

As the Grid only accepts str and unicode value, we're going to convert the value

import datetime # <-- this near the top of your file

# ...

for i, seq in enumerate(data):
    for j, v in enumerate(seq):
        if isinstance(v, (int, long,)):
            v = str(v)
        elif isinstance(v, datetime.datetime):
            v = v.strftime('%Y-%m-%dT%H:%M:%S.%f')
        try:
            self.SetCellValue(i, j, v)
        except TypeError:
            raise RuntimeError(('SetCellValue failed with TypeError', i, j, v))

[Nemius]

It work.

Man, i just canot say how i grateful I am. Yeah i start whit python 2.7 and stil learning. Maybe that is what giving me the most problems, Python have to many versions, and everybody working whit different version.
Anyway, thank you so much for help. Now to figure out how to add new row and edit and save data from cell.

Thx.