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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
