On 8/27/06, Sol <[EMAIL PROTECTED]> wrote:
> Hello,
> i stumbled upon some strange behavior when using the LIKE construct
> within selects. When we want to select all items that start with a 'n', like
>
> >>> list(item_table.select("name LIKE 'n%'").execute())
>
> we will get an exception.
>
> This seems to happen due to a string formatting on the SQL statement, as
>
> >>> list(item_table.select("name LIKE 'n%%'").execute())
>
> works just fine. Now, if this behavior will be consistent all over SA
> and across SA versions this may just be ok, if not i would consider it
> a bug. Shall i fill a ticket?
This has always been the case with Python, since Python uses `%` as
the string formatting character. If you want a raw ``%`` symbol, you
have to double it up. Well, you don't *always* have to double it up,
but it's good practice.
>>> print "name like 'n%'"
name like 'n%'
That time worked with a single % - but it worked because I used the
string immediately and didn't apply any formatting to it. However,
when working with large systems and toolkits like SQLAlchemy you can't
always know that string formatting isn't going to be applied to your
input - especially with SQL Queries since some dialects use '%' as
placeholders.
>>> print "%s like 'n%'" % 'name'
Traceback (most recent call last):
File "<stdin>", line 1, in ?
TypeError: not enough arguments for format string
Since SQLAlchemy may do further work with your query string, it's good
practice to use %%.
Note that you can also use column operator methods here::
item_table.c.name.like('n%%')
There are even 'startswith' and 'endswith' methods that will generate the above.
list(item_table.select(item_table.c.name.startswith('n')).execute())
--
Jeff Shell
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
