"LIKE" filter and psycopg2

[Steve Howe]

Hello all,

I'm having trouble using SQLAlchemy 0.50.rc3 and "like" query filters with the
psycopg2 adapter:

class Activity(Base):
__tablename__ = 'activities'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(100), index=True)

[...]
filter_name = 'john';
activities = db_session.query(model.Activity)
activities = activities.filter(model.Activity.name.like('%%' + filter_name +
'%%'))

The query run from the above statements does not get expanded by the adapter:

SELECT activities.id AS activities_id, activities.name AS activities_name
FROM activities
WHERE activities.name LIKE %(name_1)s

This syntax:

activities = activities.filter("name ~~ '%%%s%%'" % filter_name) )

... will produce a valid SQL:

SELECT activities.id AS activities_id, activities.name AS activities_name
FROM activities
WHERE name ~~ '%john%'

However, it raises this error:

[...]
self.dialect.do_execute(cursor, statement, parameters, context=context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc3-
py2.5.egg/sqlalchemy/engine/default.py", line 122, in do_execute
cursor.execute(statement, parameters)
TypeError: 'dict' object is unindexable

I'm stuck. What should I be doing ? Use another syntax ? Replace psycopg2's
paramstyle to non-escaping mode ?

My environment:

Python 2.5.2
SQLAlchemy 0.5.0.rc3
PostgreSQL 8.30
psycopg 2.0.7
Ubuntu 8.04

--
Best Regards,
Steve Howe

[Michael Bayer]

On Nov 12, 2008, at 12:14 PM, Steve Howe wrote:

>
> Hello all,
>
> I'm having trouble using SQLAlchemy 0.50.rc3 and "like" query
> filters with the
> psycopg2 adapter:
>
> class Activity(Base):
> __tablename__ = 'activities'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(100), index=True)
>
> [...]
> filter_name = 'john';
> activities = db_session.query(model.Activity)
> activities = activities.filter(model.Activity.name.like('%%' +
> filter_name +
> '%%'))
>
> The query run from the above statements does not get expanded by the
> adapter:
>
> SELECT activities.id AS activities_id, activities.name AS
> activities_name
> FROM activities
> WHERE activities.name LIKE %(name_1)s

the bind parameter %(name_1)s is being populated with the value of '%
%' + filter_name + '%%'. This should be all you need.

> This syntax:
>
> activities = activities.filter("name ~~ '%%%s%%'" % filter_name) )
>
> ... will produce a valid SQL:
>
> SELECT activities.id AS activities_id, activities.name AS
> activities_name
> FROM activities
> WHERE name ~~ '%john%'
>
> However, it raises this error:
>
> [...]
> self.dialect.do_execute(cursor, statement, parameters,
> context=context)
> File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc3-
> py2.5.egg/sqlalchemy/engine/default.py", line 122, in do_execute
> cursor.execute(statement, parameters)
> TypeError: 'dict' object is unindexable
>
> I'm stuck. What should I be doing ? Use another syntax ? Replace
> psycopg2's
> paramstyle to non-escaping mode ?

its not clear to me what is actually going wrong in that case. does
it work if you use a raw psycopg2 script ?

[Steve Howe]

Hello Michael,

> its not clear to me what is actually going wrong in that case. does
> it work if you use a raw psycopg2 script ?

Yes it does, however I figured out the print statement from the other block
was just printing what would be sent to the adapter and not to the database -
that confused me.

It's working now, thanks, I needed the "ILIKE" function.