[DB-SIG] pyformat Parameter Style

[Magnus Lyckå]

At Sat, 10 May 2003 19:54:30 +0200, M.-A. Lemburg wrote:
>>On Saturday, May 10, 2003, at 10:23 US/Pacific, Andy Todd wrote:
>>>Is there a definitive specification of the pyformat parameter style 
>>>anywhere?
>>>
>>>The usual sources come up dry. It is mentioned in the DB-API 
>>>specification (http://www.python.org/peps/pep-0249.html) but not defined.
>>>
>>>It seems from observation that one should simply put '%(<parameter 
>>>name>)s' in the query string and then pass a mapping with a 
>>>corresponding key when calling the execute method. But I'd just like to 
>>>make absolutely sure.
>>>
>>>In particular, I'm curious if the 's' actually means anything (like 
>>>string?). Should I be using different characters for different data 
>>>types, and if so, which ones?
>
>The DB API says "Python extended format codes" meaning that all
>valid Python formatting codes (including their parameters) may
>be used.

I've heard repeatedly that only %s should be used. This is the
first time I've heard someone contradict this (however reasonable
it seems). Maybe this should be clarified in the spec? (No, it's
not clear if people misunderstand it reagrdless of what words in
the text you can point to. ;)

Is it just because of a misunderstanding that a number of drivers
get this wrong, or is there some reason to turn integers and strings
into floats.

sqlite seems to choke on anything but %(...)s. I guess it's consistent
with the Postgresql driver Gerhard works with?

You either get "TypeError: an integer is required" (e.g. d, i and o) or
"TypeError: bad argument type for built-in operation" (e.g. f and e).

The culprit is here:

def _quote(value):
     """_quote(value) -> string
     This function transforms the Python value into a string suitable to 
send to
     the SQLite database in a SQL statement.  This function is automatically
     applied to all parameters sent with an execute() call.  Because of this a
     SQL statement string in an execute() call should only use '%s' [or
     '%(name)s'] for variable substitution without any quoting."""
     if value is None:
         return 'NULL'
     elif isinstance(value, StringType):
         return "'%s'" % value.replace("'", "''")
     elif isinstance(value, LongType):
         return str(value)
     elif hasattr(value, '_quote'):
         return value._quote()
     elif have_datetime and type(value) in \
             (DateTime.DateTimeType, DateTime.DateTimeDeltaType):
         return "'%s'" % value
     else:
         return repr(value)

This seems to be identical with pypgsql, wher eI found this:

_quote(value) -> string
     This function transforms the Python value into a string suitable to send
     to the PostgreSQL database in a insert or update statement.  This function
     is automatically applied to all parameter sent vis an execute() call.
     Because of this an update/insert statement string in an execute() call
     should only use '%s' [or '%(name)s'] for variable subsitution without any
     quoting.

MySQLdb works the same (but using "format"). Everything is made a string.

 >>> import MySQLdb
 >>> conn = MySQLdb.connect(...)
 >>> cur = conn.cursor()
 >>> MySQLdb.paramstyle
'format'
 >>> cur.execute('SELECT name FROM whisky_brand where id=%d', 2)
Traceback (most recent call last):
   File "<interactive input>", line 1, in ?
   File "G:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 95, in execute
     return self._execute(query, args)
   File "G:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 110, in 
_execute
     self.errorhandler(self, TypeError, m)
   File "G:\Python22\Lib\site-packages\MySQLdb\connections.py", line 33, in 
defaulterrorhandler
     raise errorclass, errorvalue
TypeError: an integer is required
 >>> cur.execute('SELECT name FROM whisky_brand where id=%s', 2)
1L
 >>>


--
Magnus Lycka (It's really Lyck&aring;), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program