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 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.