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 django-users@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---