On Aug 21, 5:25 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Aug 21, 2008, at 4:28 PM, Jeff wrote:
>
>
>
>
>
> > Hello. I'm trying to writing something to generate full text searches
> > for postgres. Here's the function I've got so far:
>
> > from sqlalchemy import sql
> > import operator
>
> > def full_text(fields, text):
> > def alternate(items):
> > for i in items[:-1]:
> > yield i
> > yield sql.text(" ' ' ")
> > yield items[-1]
>
> > return "%s @@ to_tsquery('%s')" % (
> > sql.func.to_tsvector(
> > reduce(operator.add, alternate(fields))
> > ), text)
>
> > You pass it a list of columns to match against and a full-text string
> > to search with, and it returns a string that you can use in a filter()
> > clause. It works fine if you only pass one or two column names--any
> > more than that, and it dies. At first, I thought it was something
> > wrong with my generator, but it turns out there's something wrong with
> > the way I'm concatenating columns and raw text--that, or there's a bug
> > there. Below is a simple example of what goes wrong.
>
> > from sqlalchemy import *
>
> > table1 = Table('table1', MetaData(),
> > Column('col1', String()),
> > Column('col2', String()),
> > Column('col3', String()),
> > )
>
> > #these work
> > print table1.c.col1 + table1.c.col2 + table1.c.col3
> > print table1.c.col1 + text('sdf') + table1.c.col2 + table1.c.col3
> > print table1.c.col1 + text('sdf') + table1.c.col2
>
> > #these don't
> > print table1.c.col1 + table1.c.col2 + text('sdf')
> > print table1.c.col1 + table1.c.col2 + table1.c.col3 + text('sdf')
> > print table1.c.col1 + text('sdf') + table1.c.col2 + text('sdf')
> > print table1.c.col1 + text('sdf') + text('sdf')
>
> > The ones that don't work die with "AttributeError: 'NoneType' object
> > has no attribute 'adapt_operator'" on line 1328 in sqlachemy/sql/
> > expression.py
>
> > Is this a bug, or am I doing this wrong? And all that aside, is there
> > an existing way to generate a full-text search like that?
>
> in general, text() is intended primarily for fully constructed SQL
> statements, and does not implement the semantics of an element used
> within an expression. For individual literal components, use the
> literal() function which produces a bind parameter, which will have
> all the semantic properties of any other column-oriented expression
> element. if that doesn't resolve your issue we can try again with
> something more concrete.
Michael,
Thanks, as usual, for the help. Sorry I didn't respond earlier--I was
on vacation. I tried what you suggested, but I couldn't figure out
how to apply it properly. If I just change the line:
yield sql.text(" ' ' ")
to:
yield sql.literal(" ' ' ")
then it requires that I pass in parameters when I actually run the
query--which is kind of silly in this case.
I think I should explain better what I'm trying to do with that
generator, as it's a bit weird. I need a string like this:
table.col1 || ' ' || table.col2 || ' ' || table.col3
which I can then pass to posgres's to_tsvector() (via sql.func). The
extra spaces are necessary for the full-text search to work properly.
So, is there a better way to do this? I think I'm overcomplicating
it.
Thanks again,
Jeff
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
