Python – how to construct SQL with “AND” and “OR” with LIKE

def run_query_table_multi(  search, and_flag=False ):
        if and_flag is True:
            combo = " and "
        else:
            combo = " or  "
        try:
                conn = mariadb.connect(
                user=self.user,
                password=self.password,
                host=self.host,
                port=self.port,
                database=self.database
                )
        except mariadb.Error as e:
                print(f"Error connecting to MariaDB Platform: {e}", e)
                sys.exit(1)

            # Get Cursor
        cur = conn.cursor()

        my_sql_qry = "SELECT rec_id, title, http from recipe where"
        q_bld = ""
        while len(search) > 0:
            item = search.pop()
            if len(search) > 0:
                q_bld =  q_bld + "( title like " +   "'%" + item + "%'" + ")" + combo 
            else:
                q_bld =  q_bld  + "( title like "  +  "'%" + item + "%'" ")"

        try:
            cur.execute(my_sql_qry + q_bld) 
        except mariadb.Error as e:
            print(f"Error: {e}")
            return None

This will result in a query using arbitrary items in a list

 

run_query_table_multi( ["Ham', "Cheese"], True )

will output SQL ===>


SELECT rec_id, title, http from recipe where( title like '%Cheese%') and ( title like '%Ham%')

Here is how it looks after a bit of refactoring

Used functools filter to get rid of any blanks.   If you run “or” with a blank – it will select the entire table.

This can support as many as the db can take.

also pass what field you are selection on.

def build_query(self,  field, psearch, combo):
        like_field = "( " + field + " like " 
        q_bld = ""
        psearch = list(filter(None,psearch))
        print("qsearch: ", psearch)
        while len(psearch) > 0:
            item = psearch.pop()
            if len(item) < 1:
                continue;
            if len(psearch) > 0:
                q_bld =  q_bld + like_field +   "'%" + item + "%'" + ")" + combo 
            else:
                q_bld =  q_bld  + like_field   +  "'%" + item + "%'" ")"
        return q_bld

A sample call

 

my_sql_qry = "SELECT rec_id, title, http from recipe where"
field = "title"
combo = " and "

q_bld = self.build_query(field, psearch, combo)

try:
    cur.execute(my_sql_qry + q_bld)          
except mariadb.Error as e:
    print(f"Error: {e}")
    return None