string formatting using the % operator

[Dan Sommers]

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

-- 
Dan Sommers
<http://www.tombstonezero.net/dan/>