SQLite Query multiple column search

[whacky7]

Hi Guys,

As per my previous post, I am new to Python (Gui) and in the process of learning.
I am having a particular issue with a search query against SQLite Db.

What am I trying to achieve?
I have defined three search entries where I am trying to query a table and 2 columns. or Better yet, I would like to query the entire table 'assets2' but when I select * from assets2 it comes up with an argument error.

I have tried the following
AND statement:

cursor.execute("SELECT * FROM `assets2` WHERE `Asset_Serial` AND `Asset_Model` LIKE ?", ('%'+str(SEARCH2.get())+'%',))

OR statement:

cursor.execute("SELECT * FROM `assets2` WHERE `Asset_Serial` or `Asset_Model` LIKE ?", ('%'+str(SEARCH2.get())+'%',))

Below is the search2 snippet.

def Search2():
    if SEARCH2.get() != "":
        tree.delete(*tree.get_children())
        Database()
        cursor.execute("SELECT * FROM `assets2` WHERE `Asset_Serial` LIKE ?", ('%'+str(SEARCH2.get())+'%',))
        fetch = cursor.fetchall()
        for data in fetch:
            tree.insert('', 'end', values=(data))
        cursor.close()
        conn.close()

Thanks Whacky7

[ichabod801]

Are you assuming the like operator is applying to Asset_Serial and Asset_Model? Because it only applies to Asset_Model. It's the SQL version of this problem.

[whacky7]

Hi ichabod801,

Thanks for your reply. I am not sure on which other operator I can use in order to search across all columns within assets2? or as you mentioned, fine to search across both columns 'asset_serial' and 'asset_model'.

I am using SQLite3.

[ichabod801]

You misunderstand. You need to apply the like operator twice, once to each column.

[whacky7]

Oh, I See. I am getting stuck with syntax errors and not quite sure how to include the operator twice within the statement (cursor.execute)

[ichabod801]

SELECT * FROM assets2 WHERE Asset_Serial LIKE ? or Asset_Model LIKE ?

[whacky7]

Ichabod801,

Thanks so much mate. That worked! I was making errors on the following line of code:

('%'+str(SEARCH2.get())+'%',))