quoting %

[Dan Sommers]

On Fri, 15 Oct 2004 22:15:15 +0200,
Gandalf <gandalf at geochemsource.com> wrote:

>> I don't know what kind of database and/or API you're using, but
>> DB-API compliant (Python PEP 249) libraries will put the right
>> quotation marks (if any) around your strings *and* quote any internal
>> troublesome characters correctly.

> Can you please send an example? I'm using the DB-API extensively, but
> I have my own conversion functions for date/time types. It would be
> great to have a general method.

Here's a snippent from one of my programs:

    def delete( self, tablename, column_names, row ):
        cursor = self.connection.cursor( )
        sql = ("DELETE FROM %s WHERE " % tablename
               + column_names[ 0 ] + " = %s")
        params = tuple( row[ :1 ] )
        cursor.execute( sql, params ) # <-- look here
        self.connection.commit( )

By the time it gets to the "look here" line, sql looks like this:

    DELETE FROM tablename WHERE columnname = %s

(but tablename and columnname are actual table and column names).  Note
that cursor.execute quotes the rest of the WHERE clause *correctly*,
even if params contains quote or percent or whatever characters.

FWIW, I would probably be a bit more bold nowadays, and construct sql
and params more like this:

    sql = "DELETE FROM %(table)s WHERE %(column)s = %(target)s"
    params = { 'table' : tablename,
               'column' : column_names[ 0 ],
               'target' : row[ :1 ] }

assuming that the database module in question supports the pyformat
paramstyle, or like this:

    sql = "DELETE FROM %s WHERE %s = %s"
    params = (tablename, column_names[ 0 ], row[ :1 ])

if it didn't.

See also PEP 249 <http://www.python.org/peps/pep-0249.html>, especially
the bit about paramstyle and cursor.execute (and footnotes 2 and 5).

HTH,
Dan

-- 
Dan Sommers
<http://www.tombstonezero.net/dan/>
Never play leapfrog with a unicorn.