text() TextClauses and like literals

[Mark Mitchell]

I'm having an issue I'm curious if anyone else has come across. When
I use a like SQL expression within a text() clause, I receive the
following exception:

... [my app code] ...
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 838 in scalar
return self.execute(object, *multiparams, **params).scalar()
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 848 in execute
return Connection.executors[c](self, object, multiparams, params)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 899 in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 911 in _execute_compiled
self.__execute_raw(context)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 920 in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 962 in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/default.py', line 123 in do_execute
cursor.execute(statement, parameters)
TypeError: 'dict' object is unindexable

The code to replicate this exception is pretty simple:

count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'0000%\''))

That is my error, here is my solution:

count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'0000%%\''))

Having two percent signs doesn't escape the %, it is still passed to
my postgres database with two % signs, but postgres interprets that
fine, and I don't get the SQLAlchemy exception.

Anyone else have this issue -- I thought I'd post b/c it took me a
fair amount of time to find the above solution :-).

The reason I am using literals + text clause for these queries is that
postgres will not use my index for like 'query%' queries otherwise (if
it is a bindable parameter it won't use the index).

Question on another note -- is there a utility/convenience function to
escape a string for before using it in a query (since I'm using
literals here I need to watch for SQL injection attacks)?

Thanks,

-Mark

[Michael Bayer]

On Sep 17, 2008, at 9:19 AM, Mark Mitchell wrote:

> The code to replicate this exception is pretty simple:
>
> count = con.scalar(select(['count(*)'], from_obj=my_table,
> whereclause=text('mytable.column
> like \'0000%\''))
>
> That is my error, here is my solution:
>
> count = con.scalar(select(['count(*)'], from_obj=my_table,
> whereclause=text('mytable.column
> like \'0000%%\''))
>

psycopg2's paramstyle is %(name)s, so the % needs escaping. to
replicate:

import psycopg2

conn = psycopg2.connect(user='scott', password='tiger', database='test')
cursor = conn.cursor()
cursor.execute("SELECT count(*) WHERE mytable.column like '0000%'", {})

[Mark Mitchell]

Thanks! That is good to know. Having trouble finding the docs for
psycopg2 -- any idea off the top of your head what the escape syntax
is for %? (When I put in '%%', pyscopg2 outputted %%, not %, to
postgres (but didn't throw the exception)).

I found the home page for pyscopg2 but no docs.
http://initd.org/projects/psycopg2

-Mark