Postgres Pro
Downloads
Home   >   mailing lists

Thread: parameterized full text search problem

parameterized full text search problem

From
Don Parris
Date:
Hi all,

I am using Python3 with psycopg2 on Kubuntu 12.10.  I have an SQL query like so:
SELECT * FROM entity WHERE to_tsvector('english', entity_name) @@ to_tsquery('english', 'steak');

I want the user to be able to pass in the search term at runtime, something akin to:
search_term = input(Search Payee: )
cur.execute("""SELECT * FROM entity WHERE to_tsvector(%s, entity_name) @@ to_tsquery(%s, %s);""",
        ("english" "english", search_term))

Running the script at the command line, I get:
python3 dbconnect_test.py
Search for Payee: compare foods
Traceback (most recent call last):
  File "dbconnect_test.py", line 46, in <module>
    main()
  File "dbconnect_test.py", line 27, in main
    ("english", "english", search_term))
psycopg2.ProgrammingError: syntax error in tsquery: "compare foods"

I tried this approach (from the example in the documentation, using %(str)s in place of %s:
cur.execute("""SELECT * FROM entity WHERE to_tsvector(%(str)s, entity_name) @@ to_tsquery(%(str)s, %(str)s);""",
        {'str': "english", 'str': "english", 'str': search_term})

With this result:
Search for Payee: compare foods  # I enter a search term
Traceback (most recent call last):
  File "dbconnect_test.py", line 46, in <module>
    main()
  File "dbconnect_test.py", line 27, in main
    {'str': "english", 'str': "english", 'str': search_term})
psycopg2.ProgrammingError: invalid name syntax
LINE 1: SELECT * FROM entity WHERE to_tsvector('compare foods', enti...


Clearly, I am missing something, but am not sure what.  I greatly appreciate any help I can get on this.

Thanks,
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE

Re: parameterized full text search problem

From
Joe Abbate
Date:
On 12/03/13 20:47, Don Parris wrote:
> I want the user to be able to pass in the search term at runtime,
> something akin to:
> search_term = input(Search Payee: )
> cur.execute("""SELECT * FROM entity WHERE to_tsvector(%s, entity_name)
> @@ to_tsquery(%s, %s);""",
>         ("english" "english", search_term))

You're missing a comma betwen the two "english" words.

> I tried this approach (from the example in the documentation, using
> %(str)s in place of %s:
> cur.execute("""SELECT * FROM entity WHERE to_tsvector(%(str)s,
> entity_name) @@ to_tsquery(%(str)s, %(str)s);""",
>         {'str': "english", 'str': "english", 'str': search_term})

You need to give each unique element to be replaced a unique name, like

"""SELECT * FROM entity WHERE to_tsvector(%(lang)s,
 entity_name) @@ to_tsquery(%(lang)s, %(term)s);"""
{'lang': "english", 'term': search_term}

I suggest you read about Python dictionaries as well.

Joe

Fwd: parameterized full text search problem

From
Don Parris
Date:
Because I forgot to pay attention to the e-mail headers....  Sorry about that!  Anyway, this one is solved.

---------- Forwarded message ----------
From: Don Parris <parrisdc@gmail.com>
Date: Tue, Mar 12, 2013 at 9:30 PM
Subject: Re: [psycopg] parameterized full text search problem
To: Joe Abbate <jma@freedomcircle.com>


On Tue, Mar 12, 2013 at 9:05 PM, Joe Abbate <jma@freedomcircle.com> wrote:
On 12/03/13 20:47, Don Parris wrote:
> I want the user to be able to pass in the search term at runtime,
> something akin to:
> search_term = input(Search Payee: )
> cur.execute("""SELECT * FROM entity WHERE to_tsvector(%s, entity_name)
> @@ to_tsquery(%s, %s);""",
>         ("english" "english", search_term))

You're missing a comma betwen the two "english" words.

With the commas between the two "english" words did not change much.

> I tried this approach (from the example in the documentation, using
> %(str)s in place of %s:
> cur.execute("""SELECT * FROM entity WHERE to_tsvector(%(str)s,
> entity_name) @@ to_tsquery(%(str)s, %(str)s);""",
>         {'str': "english", 'str': "english", 'str': search_term})

You need to give each unique element to be replaced a unique name, like

"""SELECT * FROM entity WHERE to_tsvector(%(lang)s,
 entity_name) @@ to_tsquery(%(lang)s, %(term)s);"""
{'lang': "english", 'term': search_term}

I suggest you read about Python dictionaries as well.

Thanks Joe.  Partly, was taking the example in the documentation a little too seriously (using "str").  Silly me.  There is one other catch.  I was passing in two words from the command line: "compare foods". 
Using just "compare" by itself brought up the record.  As long as I pass only one word of the field value I am searching for, it works.  It breaks when I enter anything more.

BUT... it *does* work.  I probably should code an exception for when the user tries to type in more than one word at the prompt to catch that and offer them a second chance.

Thanks again!
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE



--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE