Ok, so I got this to work. I couldn't find anything online about
using the like statement in queries with the python db api's, so here
is what I did, in case anyone else runs into this.
#this would actually come from a POST request
kw = "test"
sql = """
SELECT MAX(`table`.`id`)
FROM `table`
WHERE `table`.`name` LIKE %s
GROUP BY `table`.`original_id`;"""
kw = "%" + kw + "%"
cursor.execute(sql, (kw,))
So the db module handles the quotes as Jonathan stated, so no need for
those. For some reason you need to add the % to the keyword then pass
it as a tuple to the 2nd paramater of the execute function. Using %
in the actual query does not work, even if you escape it with a double
%%.
Not sure if this is the best way but it seems to work as expected now.
On Apr 15, 2:03 pm, AJ <[EMAIL PROTECTED]> wrote:
> I read the related article, but i'm still having trouble with a few
> items. The first is that it seems like I have to pass the string
> into execute and can't store it in a variable.
>
> I tried this:
>
> kw = 'test'
>
> sql = """
> SELECT MAX(`table`.`id`)
> FROM `table`
> WHERE `table`.`name` LIKE %s
> GROUP BY `table`.`original_id`;"""
>
> cursor.execute(sql, [kw])
>
> and I get the error "Not all arguments converted during string
> formatting"
>
> I'd rather do it that way so that I can read the query better. This
> works however:
>
> cursor.execute("SELECT MAX(`table`.`id`) FROM `table` WHERE
> `table`.`name` LIKE %s GROUP BY `table`.`original_id`;", [kw])
>
> The 2nd issue comes from not knowing how to do a query with like this:
>
> `some`.`field` like '%est%';
>
> I try this:
>
> cursor.execute("SELECT MAX(`table`.`id`) FROM `table` WHERE
> `table`.`name` LIKE '%%%s%%' GROUP BY `table`.`original_id`;", [kw])
>
> And I get a sql error, it looks like there are quotes going on the
> inside of the %'s:
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '%'test'% GROUP BY...
>
> On Apr 15, 12:02 pm, Jonathan Ballet <[EMAIL PROTECTED]> wrote:
>
> > AJ wrote:
>
> > [...]> sql = """
> > > SELECT MAX(`table`.`id`)
> > > FROM `table`
> > > WHERE `table`.`name` LIKE '%(kw)s'
> > > GROUP BY `table`.`original_id`;"""
>
> > > sql = sql % {'kw' : '%%' + query + '%%'}
> > > cursor.execute(sql)
>
> > [...]
>
> > This is the wrong way to do this, and your problem explains why.
>
> > Take a look at [1] ; you were right, the db module handles those things for
> > you.
> > Typically, you use "%s" in your query where you want to put an external
> > value, you give a list of
> > values to the "execute" method and the database's backend will correctly
> > handle the value's
> > quotation for you.
>
> > In addition to protect you from SQL injections, it will give you more
> > portability between different
> > databases (since they don't handle quotations the same way).
>
> > - Jonathan
>
> > [1]
> > :http://www.djangoproject.com/documentation/model-api/#executing-custo...
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
