Passing string variable to the Postgres LIKE query [duplicate]

Question

In my flask app I am trying to pass a string to the query below:

tag = "Abcd"

conn = get_db_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM database WHERE message LIKE %s ESCAPE ''", (tag,))
data = cur.fetchall()
cur.close()
conn.close()

I receive blank result.

I use psycopg2. My Postgres database has message containing "Abcd" and it works fine if I simply do:

cur.execute("SELECT * FROM database WHERE message LIKE '%Abcd%'")

If I try to pass it as a variable "tag" is my syntax for LIKE query correct?

You must add the percent signs then: tag = "%Abcd%".

Michael Butscher
– Michael Butscher

2023-02-19 01:17:33 +00:00
Commented Feb 19, 2023 at 1:17 — Michael Butscher
– Michael Butscher, Commented Feb 19, 2023 at 1:17
LIKE '%%'||%s||'%%'.

Adrian Klaver
– Adrian Klaver

2023-02-19 16:51:45 +00:00
Commented Feb 19, 2023 at 16:51 — Adrian Klaver
– Adrian Klaver, Commented Feb 19, 2023 at 16:51

Divya Prakash · Accepted Answer · 2023-02-19 03:18:07Z

0

you need to add % in your tag.

Try this:

tag = '%Abcd%' #--->> change here, add %Abcd%

conn = get_db_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM database WHERE message LIKE %s ESCAPE ''", (tag,))
data = cur.fetchall()
cur.close()
conn.close()

answered Feb 19, 2023 at 3:18

Divya Prakash

1,0041 gold badge7 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Passing string variable to the Postgres LIKE query [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related