problem, query whit Sqlite using wxpython

[Bosterk]

Hello, colleagues, Greetings

I'm developing this code but I have problems on the part of a database query

look at the full function code in which I have problems other than

data base is

import sqlite3 as lite

con = lite.connect('MiBase.sqlite')
c = con.cursor()
c.execute("""create table inventario
(codigo real, producto text, costo real, primary key (codigo))""")
con.commit()
c.close()

full code

import wx 
import sqlite3 as lite

class InsertarDato(wx.Frame):
    def __init__(self, parent, id, title):
        wx.Frame.__init__(self, parent, id, title, size = (280, 200))
        
        panel = wx.Panel(self, -1)
        
        gs = wx.FlexGridSizer(3, 2, 9, 9)
        vbox = wx.BoxSizer(wx.VERTICAL)
        hbox = wx.BoxSizer(wx.HORIZONTAL)
        
        codigo = wx.StaticText(panel, -1, "Codigo")
        producto = wx.StaticText(panel, -1, "Producto")
        costo = wx.StaticText(panel, -1, "Costo")
        self.sp = wx.TextCtrl(panel, -1, "", size = (60, -1))
        self.tc1 = wx.TextCtrl(panel, -1, size = (150, -1))
        self.tc2 = wx.TextCtrl(panel, -1, size = (150, -1))
        
        gs.AddMany([(codigo), (self.tc1, 1, wx.LEFT, 10),
                    (producto), (self.tc2, 1, wx.LEFT, 10),
                    (costo), (self.sp, 0, wx.LEFT, 10)])
        
        vbox.Add(gs, 0, wx.ALL, 10)
        vbox.Add((-1, 30))
        
        guardar = wx.Button(panel, -1, "Guardar", size = (-1, 30))
        salir = wx.Button(panel, -1, "Salir", size = (-1, 30))
        buscar = wx.Button(panel, -1, "Buscar", size = (-1, 30))
        hbox.Add(guardar)
        hbox.Add(salir, 0, wx.LEFT, 5)
        hbox.Add(buscar, 0, wx.RIGHT, 10)
        vbox.Add(hbox, 0, wx.ALIGN_CENTER | wx.BOTTOM, 10)
        
        self.Bind(wx.EVT_BUTTON, self.OnGuardar, id = guardar.GetId())
        self.Bind(wx.EVT_BUTTON, self.OnSalir, id = salir.GetId())
        self.Bind(wx.EVT_BUTTON, self.OnBuscar, id = buscar.GetId())
        
        panel.SetSizer(vbox)
        
        self.Centre()
        self.Show(True)
        
    def OnGuardar(self, event):
        try:
            con = lite.connect("MiBase.sqlite")
            cur = con.cursor()
            codigo = self.tc1.GetValue()
            costo = self.sp.GetValue()
            producto = self.tc2.GetValue()
            cur.execute("insert into inventario values (?, ?, ?)", (codigo, producto, costo))
            con.commit()
            self.tc1.Clear()
            self.sp.Clear()
            self.tc2.Clear()
            
        except lite.Error, error:
            dlg = wx.MessageDialog(self, str(error), "Ha ocurrido un error")
            dlg.ShowModal()
            
    def OnSalir(self, event):
        self.Destroy()
     
    def OnBuscar(self, event):
        con = lite.connect("MiBase.sqlite")
        cur = con.cursor()
        resultado = self.tc1.GetValue()
        sql = ("""SELECT * FROM inventario WHERE codigo = '%r'""" %(resultado))
        cur.execute(sql)
        resul = "%s" %resultado
        self.tc1.SetValue(sql)
        
app = wx.App()
InsertarDato(None, -1, "Dialogo de Inventario")
app.MainLoop()

My problem

def OnBuscar(self, event):
        con = lite.connect("MiBase.sqlite")
        cur = con.cursor()
        resultado = self.tc1.GetValue()
        sql = ("""SELECT * FROM inventario WHERE codigo = '%s'""" %(resultado))
        cur.execute(sql)
        resul = "%s" %resultado
        self.tc1.SetValue(sql)

\Frame.pyw", line 70, in OnBuscar
cur.execute(sql) error

Thanks for reading

I hope your answer

excellent forum

[woooee]

I would suggest something like the following. I am no SQL expert, but using a dictionary to hold the variables eliminates SQL injection problems, or so we think.

cur.execute('SELECT * FROM inventario WHERE codigo==:dic_var', \
                    {"dic_var":resultado})
        recs_list=cur.fetchall()
        #
        #-----take a look at the contents of recs_list FYI
        print recs_list

Also, this code

def OnBuscar(self, event):
    con = lite.connect("MiBase.sqlite")
    cur = con.cursor()

establishes a connection every time the function is called. You should establish a connection once, and store as an instance variable of the class, or pass "cur" to the function (and "con" if using a commit). An example:

class InsertarDato(wx.Frame):
    def __init__(self, parent, id, title):
        self.con = lite.connect("MiBase.sqlite")
        self.cur = self.con.cursor()

    def OnBuscar(self, event):
        resultado = self.tc1.GetValue()
        self.cur.execute('SELECT * FROM inventario WHERE codigo==:dic_var', \
                    {"dic_var":resultado})
        recs_list=self.cur.fetchall()
        print recs_list

[Bosterk]

Hey thanks for the reply

I am grateful

I doubt arises a

to place data into a sql query wx.textctrl?

namely

name: ___________

Phono: ______

address: _________

etc

I ask this because the data come in dictionaries and I would like to know how to put in text boxes