SQL Query is not executing WHERE clause

[hammer]

I am not getting an error with a Query but it seems to not recognize WERE clause because it returns every row regardless. See listBy_Loc method below where i pass it a parameter for the Select statement. The parameter indicates it is passed to the Select statement on the debug screen (hover over loc )but regardless of what it is, the query returns ALL. I have four possible Loc entries but have inputted data with just one location present at this time but the sort by the other three locations return ALL rows as well. I have checked the table with DB Browser and know the Loc field has only one location name for all it's entries. does anyone see anything wrong with SELECT statement?

#con=sqlite3.connect('bovine.db');
#c=con.cursor();
#c.execute(""" CREATE TABLE cattleData
#           (tagNo integer,
#            Loc text,
#            sex text,
#            type text)
#           """)
#c.execute(""" CREATE TABLE locations
#           (locName text)
#           """);



opList=["Location","Add","Edit"]


class MyDataBase:
    def __init__(self,db_path):
        self.db_path=db_path

    def connect(self):
        self.con=sqlite3.connect(self.db_path)
        self.c=self.con.cursor()
    
    def listBy_Loc(self,loc):
        self.c.execute('SELECT * FROM cattleData WHERE Loc = loc;')
        cattle=self.c.fetchall()
        return cattle

    def locationList(self):
        self.c.execute('''SELECT * FROM locations;''')
        records=self.c.fetchall()
        return records

class MyApp(TabbedPanel):
    def __init__(self,data_base,myCow):
        super(MyApp, self).__init__()
        self.db=data_base
        self.do_default_tab=False
        self.myCow=myCow
 
        for i in opList:
            myTPHM=TabbedPanelHeader()
            myTPHM.text=str(i)
            self.add_widget(myTPHM)
            if str(i)=='Location':
                myTPLoc=TabbedPanel()
                myTPLoc.do_default_tab=False
                myTPHM.content=myTPLoc
                #for j in locList:
                for row in self.db.locationList():
                #self.locationList():
                    #locationName=str(j)
                    myTPHL=TabbedPanelHeader()
                    myTPHL.text=row[0]
                    myTPLoc.add_widget(myTPHL)
                    stkLoc=StackLayout(orientation='tb-lr',spacing=4)
                    myTPHL.content=stkLoc
                    ### add animals to loc tab
                    for row2 in self.db.listBy_Loc(row[0]):
                        tagNo=row2[0]
                        sex=row2[2]
                        bredBy='Shadow'
                        calfTag='55'
                        Cow=myCow(tagNo,sex,bredBy,calfTag)
                        Cow.bind(on_press=self.cowBtn_Press)
                        #
                        stkLoc.add_widget(Cow)
                        #...
                
            if str(i)=='Add':
                myTPHM.content=self.enterScreen()
        
        
    

[menator01]

This self.c.execute('SELECT * FROM cattleData WHERE Loc = loc;') is wrong. You have your variable in the quotes.

Try using fstring. self.c.execute(f'SELECT * FROM cattleData WHERE Loc = {loc};')
or do it this way self.c.execute('SELECT * FROM cattleData WHERE Loc = ' + loc)

[hammer]

Thanks- i made the changes and understand now why that is. However
I now have below error
I have three rows of data in this table where the Loc column is "Hermas" for all three. This is also the parameter passed.
I do not fully understand below. Loc is the column name, not Hermas????

Error:
Exception has occurred: OperationalError       (note: full exception trace is shown but execution is paused at: build)
no such column: Hermas
  File "/home/mark/Python Environments/FirstEnvProject/Bovine2.py", line 47, in listBy_Loc
    self.c.execute('SELECT * FROM cattleData WHERE Loc = ' + loc)
  File "/home/mark/Python Environments/FirstEnvProject/Bovine2.py", line 85, in __init__
    for row2 in self.db.listBy_Loc(row[0]):
  File "/home/mark/Python Environments/FirstEnvProject/Bovine2.py", line 179, in build (Current frame)
    return MyApp(my_db,myCow)
  File "/home/mark/Python Environments/FirstEnvProject/Bovine2.py", line 183, in <module>
    CreateApp().run()

[ndc85430]

Try using fstring.

Please don't suggest this (or string concatenation) - SQL queries should make use of placeholders ("prepared statements"), so as not to be vulnerable to SQL injection. See Bobby Tables.

[jamesaarr]

Hi mate, there are two possible causes that I can think of that are causing the above errors. Try something similar to the below, but look at bobby tables as suggested above to make sure it's protected.

self.c.execute('select * from cattleData where Loc = '%{loc}%')

See if that works. I believe in your one it is trying to find where the Loc column matches the data in the Hermas column. Don't use my suggestion exactly, I just want to give you an idea, read bobby tables as I was doing this the wrong way and dropped a table during testing.

Thanks,
James

[ibreeden]

Hi @hammer,
I don't see 'Hermas' in your code but I think it is string data to be selected.
First something about syntax: string data must be in single quotes. So the correct syntax is:

"SELECT * FROM cattleData WHERE Loc = 'Hermas'"

So if you choose to use f-string ( which is deprecated, see Bobby Tables) you should do:

columnvalue = "Hermas"
f"SELECT * FROM cattleData WHERE Loc = '{columnvalue}'"

But it is better to use a placeholder. In this case you need not use the single quotes. But you need to give the value as a tuple: (columnvalue,). (Note the comma!)

columnvalue = "Hermas"
self.c.execute("SELECT * FROM cattleData WHERE Loc = ?", (columnvalue,))

( @jamesaarr : when you want to use the wildcard character "%" you need to use "like" instead of "=".

self.c.execute(f"select * from cattleData where Loc like '%{loc}%'")

)

[jamesaarr]

Hi @hammer,
I don't see 'Hermas' in your code but I think it is string data to be selected.
First something about syntax: string data must be in single quotes. So the correct syntax is:

"SELECT * FROM cattleData WHERE Loc = 'Hermas'"

So if you choose to use f-string ( which is deprecated, see Bobby Tables) you should do:

columnvalue = "Hermas"
f"SELECT * FROM cattleData WHERE Loc = '{columnvalue}'"

But it is better to use a placeholder. In this case you need not use the single quotes. But you need to give the value as a tuple: (columnvalue,). (Note the comma!)

columnvalue = "Hermas"
self.c.execute("SELECT * FROM cattleData WHERE Loc = ?", (columnvalue,))

( @jamesaarr : when you want to use the wildcard character "%" you need to use "like" instead of "=".

self.c.execute(f"select * from cattleData where Loc like '%{loc}%'")

)

Hi mate,

You're absolutely correct, I was offering a hint rather than the answer ;)

Thanks

[hammer]

Problem Resolved- Thanks all for the insight. I used the PLACEHOLDER style suggestion. I have been using that style on INSERT but not SELECT. I need to research more about why/when using placeholders is important.