wxpython GUI issue with database

[hdk]

Hi All,

I am very new to python Gui programming and am trying to create a simple Gui which will get a set of results from an sql query which goes into Oracle and display these results in the text box below. If u can also help me get the positioning of my labels,combo box and buttons to re-size if I expand the frame that would be great. Code below. Thank u sooo much!

import wx
import cx_Oracle
   

class MainWindow(wx.Panel):
    def __init__(self,parent,id):
        wx.Panel.__init__(self,parent,id)

        self.background = wx.Panel(self)

        lblHeader = wx.StaticText(self,-1,label='TITLE',pos=(100,10),style=wx.ALIGN_CENTER)
        font = wx.Font(15,wx.BOLD,wx.NORMAL,wx.ITALIC)
        lblHeader.SetFont(font)

        lblComboBox = wx.StaticText(self,-1,label='Fund_id:',pos=(5,65))
        self.Fund_id = wx.ComboBox(self, -1, pos=(120,60), size=(100,25), choices=('fund'), style=wx.CB_DROPDOWN)
        lblStartDate = wx.StaticText(self, -1, label='Start_date:', pos=(5,105))

        self.StartDate = wx.TextCtrl(self, -1, pos=(120,100), size=(100,25))
        lblEndDate = wx.StaticText(self,-1,label='End_date:',pos=(5,145))

        self.EndDate=wx.TextCtrl(self,-1,pos=(120,140),size=(100,25))

        self.myButton = wx.Button(self, label='Execute', pos=(50,200), size=(100,25))

        self.Bind(wx.EVT_BUTTON,self.OnExecute,id= self.myButton.GetId())
        Exit = wx.Button(self, label='Exit', pos=(200,200), size=(100,25))

        self.Bind(wx.EVT_BUTTON, self.OnExit, id=Exit.GetId())

        self.transferArea = wx.TextCtrl(self, pos=(20,250),size= (1000,200),style = wx.TE_READONLY | wx.TE_MULTILINE)


    def OnExecute(self,event):
       # try:
        connection = cx_Oracle.connect('/@db')
        cursor = connection.cursor()
        fund_id = self.Fund_id.GetValue()
        start_date = self.StartDate.GetValue()
        end_date = self.EndDate.GetValue()
        sql="""Select * from table where fund_id = '%s' and datetime >= to_date('%s','DD/MM/YYYY') and datetime <= to_date('%s','DD/MM/YYYY')""" % (fund_id,start_date,end_date)
        print sql
        cursor.execute(sql)
        result = cursor.fetchall()
        return result

    def OnExit(self,e):
        self.Close()  # Close the frame.

app = wx.App()
frame = wx.Frame(None,-1,"MY TITLE")
MainWindow(frame,-1)
frame.Show(1)
app.MainLoop()

[hdk]

FYI - I keep getting the below error when I hit execute. Tested my sql in Oracle and it does work

Traceback (most recent call last):
File "Gui_test.py", line 43, in OnExecute
cursor.execute(sql)
TypeError: expecting None or a string

[sneekula]

I looked over your code and made the following observations:

To expand your widgets with the frame you need to use a sizer and not absolute positioning.

You sql command string might have errors due to:
1) choices in the combobox needs to be a list of valid choices for fund_id
2) starting and ending dates need to be valid or use a default

BTW, you should be able to rewrite that long sql string this way:

sql = """
        Select * from table where fund_id = '%s' and 
        datetime >= to_date('%s','DD/MM/YYYY') and 
        datetime <= to_date('%s','DD/MM/YYYY')
        """ % (fund_id,start_date,end_date)

Works well with Python's sqlite3

[Stefano Mtangoo]

you need to brush yourself with wx's Layout management. It is very simple once you grasp the basics.
Check here:
http://zetcode.com/wxpython/layout/

[hdk]

Hi sneekula

Thank you very much for your response. I will look into the sizer.
Regarding the sql string. I am dumbfounded as to why this does not work and I keep getting this error. I did print the sql when I run the program to see what the result is and tested that result in Oracle which does work so have no idea why I keep getting this type error.

[hdk]

Hi.

I figures out the Oracle issue now just ran into another issue. I was my results from my sql to display in the static text box (read only) but I want to add a header and then followed by the result shown line by line without the format of a list. any ideas or pointers?

class MainWindow(wx.Panel):
    def __init__(self,parent,id):
        wx.Panel.__init__(self,parent,id)

        self.background = wx.Panel(self)
        
        header = ['Date','Perf']

        
        lblHeader = wx.StaticText(self,-1,label='AHL PERF CALCULATION',pos=(100,10),style=wx.ALIGN_CENTER)
        font = wx.Font(15,wx.BOLD,wx.NORMAL,wx.ITALIC)
        lblHeader.SetFont(font)
        
        
        lblComboBox = wx.StaticText(self,-1,label='Fund_id:',pos=(5,65))
        self.Fund_id = wx.ComboBox(self, -1, pos=(120,60), size=(100,25), choices=['fund1','fund2'], style=wx.CB_DROPDOWN)
        lblStartDate = wx.StaticText(self, -1, label='Start_date:', pos=(5,105))

        self.StartDate = wx.TextCtrl(self, -1, pos=(120,100), size=(100,25))
        lblEndDate = wx.StaticText(self,-1,label='End_date:',pos=(5,145))

        self.EndDate=wx.TextCtrl(self,-1,pos=(120,140),size=(100,25))

        self.myButton = wx.Button(self, label='Execute', pos=(50,200), size=(100,25))

        self.Bind(wx.EVT_BUTTON,self.OnExecute,id= self.myButton.GetId())
        Exit = wx.Button(self, label='Exit', pos=(200,200), size=(100,25))

        self.Bind(wx.EVT_BUTTON, self.OnExit, id=Exit.GetId())

        self.transferArea = wx.TextCtrl(self, pos=(20,250),size= (1000,500),style = wx.TE_READONLY | wx.TE_MULTILINE)

            
    def OnExecute(self,event):
       # try:
        connection = cx_Oracle.connect('/@resd1')
        cursor = connection.cursor()
        fund_id = self.Fund_id.GetValue()
        print fund_id
        start_date = self.StartDate.GetValue()
        print start_date
        end_date = self.EndDate.GetValue()
        print end_date
        sql="""Select * from table where fund_id = '%s' and datetime >= to_date('%s','DD/MM/YYYY') and datetime <= to_date('%s','DD/MM/YYYY')""" %(str(fund_id),str(start_date),str(end_date))
        print sql
        cursor.execute(sql)
        result = []
        for line in cursor:
            line = [str(entry) for entry in line]
            result.append(line)
            print line

#THIS IS WHERE I AM GOING WRONG!
        for line in result:
            header = ['Date','Perf']
            self.transferArea.SetValue(str(header)+"\n")
            self.transferArea.SetValue(str(line))

    def OnExit(self,event):
        self.Destroy()  # Close the frame.

app = wx.App()
frame = wx.Frame(None,-1,"AHL PNL")
MainWindow(frame,-1)
frame.Show(1)
app.MainLoop(

[Stefano Mtangoo]

Hi.

I figures out the Oracle issue now just ran into another issue. I was my results from my sql to display in the static text box (read only) but I want to add a header and then followed by the result shown line by line without the format of a list. any ideas or pointers?

class MainWindow(wx.Panel):
    def __init__(self,parent,id):
        wx.Panel.__init__(self,parent,id)

        self.background = wx.Panel(self)
        
        header = ['Date','Perf']

        
        lblHeader = wx.StaticText(self,-1,label='AHL PERF CALCULATION',pos=(100,10),style=wx.ALIGN_CENTER)
        font = wx.Font(15,wx.BOLD,wx.NORMAL,wx.ITALIC)
        lblHeader.SetFont(font)
        
        
        lblComboBox = wx.StaticText(self,-1,label='Fund_id:',pos=(5,65))
        self.Fund_id = wx.ComboBox(self, -1, pos=(120,60), size=(100,25), choices=['fund1','fund2'], style=wx.CB_DROPDOWN)
        lblStartDate = wx.StaticText(self, -1, label='Start_date:', pos=(5,105))

        self.StartDate = wx.TextCtrl(self, -1, pos=(120,100), size=(100,25))
        lblEndDate = wx.StaticText(self,-1,label='End_date:',pos=(5,145))

        self.EndDate=wx.TextCtrl(self,-1,pos=(120,140),size=(100,25))

        self.myButton = wx.Button(self, label='Execute', pos=(50,200), size=(100,25))

        self.Bind(wx.EVT_BUTTON,self.OnExecute,id= self.myButton.GetId())
        Exit = wx.Button(self, label='Exit', pos=(200,200), size=(100,25))

        self.Bind(wx.EVT_BUTTON, self.OnExit, id=Exit.GetId())

        self.transferArea = wx.TextCtrl(self, pos=(20,250),size= (1000,500),style = wx.TE_READONLY | wx.TE_MULTILINE)

            
    def OnExecute(self,event):
       # try:
        connection = cx_Oracle.connect('/@resd1')
        cursor = connection.cursor()
        fund_id = self.Fund_id.GetValue()
        print fund_id
        start_date = self.StartDate.GetValue()
        print start_date
        end_date = self.EndDate.GetValue()
        print end_date
        sql="""Select * from table where fund_id = '%s' and datetime >= to_date('%s','DD/MM/YYYY') and datetime <= to_date('%s','DD/MM/YYYY')""" %(str(fund_id),str(start_date),str(end_date))
        print sql
        cursor.execute(sql)
        result = []
        for line in cursor:
            line = [str(entry) for entry in line]
            result.append(line)
            print line

#THIS IS WHERE I AM GOING WRONG!
        for line in result:
            header = ['Date','Perf']
            self.transferArea.SetValue(str(header)+"\n")
            self.transferArea.SetValue(str(line))

    def OnExit(self,event):
        self.Destroy()  # Close the frame.

app = wx.App()
frame = wx.Frame(None,-1,"AHL PNL")
MainWindow(frame,-1)
frame.Show(1)
app.MainLoop(

Is is it MUST that you should use Oracle? If not try other databases

[hdk]

Hi,

I have to use Oracle. Have sorted that issue out tho.

Got another question though. I am using a simple textCTRL are to display my results but formatting this so that all the words are aligned is a pain. Is there a way to write the results to a grid so each field would be in a cell? Similiar to excel?

[Stefano Mtangoo]

check wxGridGrid and its brothers. I remember Snee wrote one on wxPython snippet sticky