SQLite Query multiple column search

SQLite Query multiple column search - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: SQLite Query multiple column search (/thread-17109.html)

SQLite Query multiple column search - whacky7 - Mar-28-2019

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

RE: SQLite Query multiple column search - ichabod801 - Mar-29-2019

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.

RE: SQLite Query multiple column search - whacky7 - Mar-29-2019

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.

RE: SQLite Query multiple column search - ichabod801 - Mar-29-2019

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

RE: SQLite Query multiple column search - whacky7 - Mar-29-2019

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)

RE: SQLite Query multiple column search - ichabod801 - Mar-29-2019

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

RE: SQLite Query multiple column search - whacky7 - Apr-01-2019

Ichabod801,

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

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