Re: Compare two tables in a secondary database

On Fri, 23 Dec 2011 03:33:14 +0200, Edvinas Narbutas
<enarbutas@gmail.com> wrote:

>item_search_results = itemSearch.objects.raw(
> '''SELECT * FROM invTypes WHERE invTypes.typeName LIKE '%s%'
>LIMIT 0, 10''', [search_query]
>).using(
> 'DataDump'
>)

>I get this error. "not enough arguments for format string", which im
>guessing the LIKE isnt working because this query works.
>

Presuming the interface 1) uses %s for parameter placeholder and 2)
safely escapes/quotes the parameters, then you need to reformulate that
query. (Oh, and did you want SQL wildcard % on both sides of the
parameter?)

MySQLdb, which uses %s, would probably complain about your shown
example for a number of reasons...

First, you have '%s%', and that second % is not escaped -- so it
will be seen as the start of a second placeholder rather than a literal
% in the result. You'd need '%s%%' to have a single % at the end of the
parameter.

Second, MySQLdb quotes parameters before putting them into the
placeholder, so you'd end up with

... like ''something'%'

Note the quotes in the result -- those are single quotes, not a
double quote at the beginning; so you have the "like" term being an
empty string followed by garbage..

Reformulated to fit the MySQLdb parameter handling you should use
something like:

"select * from invTypes where invTypes.typeName like %s limit 0, 10",
["%" + search_query + "%"]

wherein all modification of the "search_query" (prepending/appending SQL
% wildcards) is done to the parameter, NOT the placeholder, since you
need them inside the quoting that the adapter provides; and NO quotes
around the %s placeholder.
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.