[DB-SIG] pyformat Parameter Style
magnus at thinkware.se
Mon May 12 22:01:17 EDT 2003
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
>>>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
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
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:
"""_quote(value) -> string
This function transforms the Python value into a string suitable 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:
elif isinstance(value, StringType):
return "'%s'" % value.replace("'", "''")
elif isinstance(value, LongType):
elif hasattr(value, '_quote'):
elif have_datetime and type(value) in \
return "'%s'" % 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
MySQLdb works the same (but using "format"). Everything is made a string.
>>> import MySQLdb
>>> conn = MySQLdb.connect(...)
>>> cur = conn.cursor()
>>> 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
self.errorhandler(self, TypeError, m)
File "G:\Python22\Lib\site-packages\MySQLdb\connections.py", line 33, in
raise errorclass, errorvalue
TypeError: an integer is required
>>> cur.execute('SELECT name FROM whisky_brand where id=%s', 2)
Magnus Lycka (It's really Lyckå), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program
More information about the DB-SIG