You are right. Just as i got the same result I saw your post. And this:
> Second, MySQLdb quotes parameters before putting them into the
> placeholder, so you'd end up with
>
> ... like ''something'%'
I saw that happen in the query ^_^
This is the final code for it:
class item_search(models.Model):
typeID = models.SmallIntegerField(primary_key = True)
typeName = models.CharField(max_length = 200)
search_query = request.GET[u'query']
item_search_results = item_search.objects.raw(
"\
SELECT \
typeID, typeName \
FROM \
invGroups, \
invTypes \
WHERE \
invTypes.groupID = invGroups.groupID AND \
invGroups.categoryID IN (7, 8, 18, 20) AND \
invTypes.typeName LIKE %s AND \
invTypes.published = 1 \
ORDER BY \
typeName \
LIMIT 0, 10",
['%' + search_query + '%']
).using(
'DataDump'
)
for name in item_search_results:
results.append(name.typeName)
When i wrote it i modified the search_query before the actual raw
query. But i like how you did it in the params, one less line of code
:) . Thanks for helping me, i appreciate it greatly. :)
On Fri, Dec 23, 2011 at 5:44 PM, Dennis Lee Bieber
<wlfr...@ix.netcom.com> wrote:
> On Fri, 23 Dec 2011 03:33:14 +0200, Edvinas Narbutas
> <enarbu...@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
> wlfr...@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+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
