How to use like pattern matching with PostgreSQL and Python with multiple percentage (%) symbols?

Question

I am trying to pattern match with the LIKE LOWER('% %') command however I think the fact that I am using a python variable with %s is mucking it up. I can't seem to find any escape characters for the percentage symbol and my program gives me no errors. Is this the problem or is there something else I'm missing. It does work if I just run LIKE %s however I need to be able to search like not equals.

# Ask for the database connection, and get the cursor set up
conn = database_connect()
if(conn is None):
    return ERROR_CODE
cur = conn.cursor()
print("search_term: ", search_term)
try:
    # Select the bays that match (or are similar) to the search term
    sql = """SELECT fp.name AS "Name", fp.size AS "Size", COUNT(*) AS "Number of Fish"
               FROM FishPond fp JOIN Fish f ON (fp.pondID = f.livesAt)
              WHERE LOWER(fp.name) LIKE LOWER('%%s%') OR LOWER(fp.size) LIKE LOWER('%%s%')
              GROUP BY fp.name, fp.size"""
    cur.execute(sql, (search_term, ))
    rows = cur.fetchall()
    cur.close()                     # Close the cursor
    conn.close()                    # Close the connection to the db
    return rows
except:
    # If there were any errors, return a NULL row printing an error to the debug
    print("Error with Database - Unable to search pond")
cur.close()                     # Close the cursor
conn.close()                    # Close the connection to the db
return None

Never use a bare except:. It will catch everything, even MemoryError. At the very least use except Exception:. — Bakuriu
– Bakuriu, Commented May 17, 2016 at 11:22

mhawke · Accepted Answer · 2016-05-17 10:44:09Z

9

Instead of embedding the ampersands in the query string, you could wrap the search term string in ampersands, and then pass that to cursor.execute():

sql = 'SELECT * from FishPond fp WHERE LOWER(fp.name) LIKE LOWER(%s)'
search_term = 'xyz'
like_pattern = '%{}%'.format(search_term)
cur.execute(sql, (like_pattern,))

The query is simplified for the purpose of example.

This is more flexible because the calling code can pass any valid LIKE pattern to the query.

BTW: In Postgresql you can use ILIKE for case insensitive pattern matching, so the example query could be written as this:

sql = 'SELECT * from FishPond fp WHERE fp.name ILIKE %s'

As noted in the documentation ILIKE is a Postgresql extension, not standard SQL.

edited May 17, 2016 at 10:44

answered May 17, 2016 at 10:39

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nikihub · Accepted Answer · 2016-05-17 10:30:09Z

2

You can escape % with another %

>>> test = 'test'
>>> a = 'LIKE %%s%'
>>> a % test
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: incomplete format
>>> 
>>> a = 'LIKE %%%s%%'
>>> a % test
'LIKE %test%'

P.S. you also have two placeholders, but you are passing only one argument in execute

answered May 17, 2016 at 10:30

nikihub

3211 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to use like pattern matching with PostgreSQL and Python with multiple percentage (%) symbols?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related