Escaping SQL "ILIKE" using bilbioteca psycopg2

Asked

Viewed 29 times

0

I followed that topical here in the stack to try to create a select in which I look for a person using the ILIKE function, but I have the return of "None", even making changes in the column and table to see if it generates a different error, the return is the same "None"

def searchPeople(name):
    try:
        conn = Connection()
        cur = conn.cursor()
        conn.set_client_encoding('LATIN1')
        name= name.replace('=', '==').replace('%', '=%').replace('_', '=_')
        result = cur.execute("SELECT * FROM people WHERE name ilike %(like)s",dict(like='%'+ name+'%'))
        print result
        cur.close()
    except Exception as e:
        return e
    finally:
        conn.close()
python postgresql flask

  • Syntax error in SQL expression, fix to: SELECT * FROM people WHERE name ilike '%(like)s'. But you effectively want the lines where the field name contains the string '(like)s'?

  • I’m sorry, when it comes time to move to the forum I wrote along nameilike . I am making an autocomplete form and I will pass this function to the view, in this case the user will start writing a letter and the rest will appear, this string %(like)s was the way I found to make the escape, then I replace her

1 answer

1


Using the string concatenation operator ||:

cur.execute("SELECT * FROM people WHERE name ilike '%%' || %s || '%%'; ", (name,))

Using the string concatenation function CONCAT():

cur.execute("SELECT * FROM people WHERE name ilike CONCAT('%%', %s, '%%'); ", (name,))

Browser other questions tagged python postgresql flask

You are not signed in. Login or sign up in order to post.