Something More Elegant

[Gabriel Genellina]

En Sat, 09 Jan 2010 11:01:25 -0300, Victor Subervi  
<victorsubervi at gmail.com> escribió:
> On Sat, Jan 9, 2010 at 8:39 AM, Tim Chase  
> <python.list at tim.thechases.com>wrote:
>
>> It would also help if you didn't pass the categoryID as a  
>> string-formatted
>> value, but as a proper parameter, something like
>>
>>  sql = "... where c.categoryid=?" % (store, store)
>>  cursor.execute(sql, (category_id,))
>>
>
> I now have the following:
>
>       sql = 'select distinct p.ID from %sPackages p join
> %sCategoriesPackages c where c.CategoryID=?;' % (store, store)
>       cursor.execute(sql, (categoryID,))
>       packageIDs = [itm[0] for itm in cursor]
>
> It threw this error:
>
> TypeError: not all arguments converted during string formatting
>       args = ('not all arguments converted during string formatting',)
>
>> You'd have to check the place-holder character for your particular
>> back-end:
>>
>>  >>> import <your database engine> as db
>>  >>> print db.paramstyle
>>
>> Printed "format". What's that mean? I use MySQLdb

That means, MySQLdb uses %s as a placeholder for parameter substitution --  
same as Python when doing string interpolation. Unfortunately this will  
confuse things. In your code above, the ? near the end should become %s --  
but you don't want THAT %s to be interpreted by Python at that time,  
instead it must remain as a literal %s until the cursor.execute line. You  
have to escape the % by doubling it: %%s

        sql = 'select distinct p.ID from %sPackages p join  
%sCategoriesPackages c where c.CategoryID=%%s;' % (store, store)
        cursor.execute(sql, (categoryID,))
        packageIDs = [itm[0] for itm in cursor]

-- 
Gabriel Genellina