string formatting using the % operator

[William Gill]

Dan Sommers wrote:
> On Mon, 13 Jun 2005 15:12:54 GMT,
> William Gill <noreply at gcgroup.net> wrote:
> 
> 
>>I am using the % operator to create queries for a db app.  It works fine
>>when exact strings, or numbers are used, but some queries need partial
>>matching that use the '%' as a wildcards. So for example the resultant
>>string should be 'WHERE name LIKE %smith%'  (would match silversmith,
>>smithy, and smith).  Is there any way to get something like
> 
> 
>>       searchterm = 'smith'
>>       sql += 'WHERE name LIKE %s'  %  searchterm
> 
> 
>>to return 'WHERE name LIKE %smith%'    I have tried using escapes,
>>character codes for the % sign, and lots of other gyrations with no
>>success.  The only thing that works is if I modify searchterm first:
> 
> 
>>       searchterm = 'smith'
>>       searchterm ='%'+'smith'+'%'
>>       sql += 'WHERE name LIKE %s'  %  searchterm
> 
> 
>>Any Ideas?
> 
> 
> Let the DB-API do more work for you:
> 
>     cursor = connection.cursor( )
>     sql = """SELECT column2, columns3 FROM table WHERE name LIKE %s"""
>     values = ('%%%s%%' % searchterm,) # note that this is a tuple
>     cursor.execute( sql, values )
> 
> HTH,
> Dan
> 

I can't tell you how many times I looked at the table of format codes 
and missed this.

Thanks everyone!