[DB-SIG] suggested change in the docs.

Magnus Lyckå magnus at thinkware.se
Sun May 18 23:23:37 EDT 2003

At 17 May 2003 23:45:51 +0200, Philip Jägenstedt wrote:
>I discovered today that %d substitution did not work for MySQLdb (which
>has paramstule='format'). It took me quite some time to figure out why,
>and found this answer on a newgroup discussion:
>"because there's no %d substitution in mysqldb or any other DB adapters.
>There's only the %s substitution."

This is deeper than you thought...

There was a discussion about this on the mailing list this week (last week for
for people living in Asia, Australia etc ;). Look in the archives. Here are 

From: Chris Cogdon <chris at cogdon.org>
>You should ALWAYS use 's', too, regardless of the data type.

From: "M.-A. Lemburg" <mal at lemburg.com> (Who wrote the DB-API if I'm not 
>The DB API says "Python extended format codes" meaning that all
>valid Python formatting codes (including their parameters) may
>be used.

From: Andy Todd <andy47 at halfcooked.com>
>If your db module says it supports 'pyformat' as the paramstyle then any 
>of these format codes should be supported.
(Andy refers to http://www.python.org/doc/current/lib/typesseq-strings.html)

From: Jekabs Andrushaitis <jekabs.andrusaitis at tietoenator.com>
>Most of DB modules I have used understand only 's' type qualifier in 
>pyformat argument style (ive tried it in psycopg and one of mysql modules).
>I do not really care much about this, but it creates alot of confusion, 
>since normally in Python % all the qualifiers can be used...

Is there more than one driver for MySQL?

BTW, I'm not sure Jekabs is right about psycopg, since its author
Federico Di Gregorio <fog at initd.org> replied:
>if psycopg fails with %d and %f, then it is a bug. please report it. :)
and previously he had written:
>not to criticize pypgsql, but if its quoting code quotes integers, it is
>quite dumb and does a little bit too much work (i can understand why it
>does it that way, though.)
>other adapter are a little bit smarter...

>If this is true,

As you might have figured out by know, there isn't a full concensus
on the issue. It seems that the fact that only %s have been used in
the examples have misled people. Or maybe the DB-API document was less
explicit in an earlier revision?

As far as I understand, the paramstyles "format" or "pyformat" are
only used for backends that aren't able to pass in parameters on the
server side, i.e. PostGreSQL, MySQL and SQLite. Of these, the the
MySQLdb, sqlite and pyPgSQL drivers only allow %s (or %(name)s) while
pgdb and psycodb follow the DB-API standard, and allow all format
codes. I don't know about PoPy.

>I suggest that the docs (DatabaseAPI-2.0.html) are
>updated to specifically mention this under the paramstyle section. This
>is important, because it's easy to be mislead by "ANSI C printf format
>codes, e.g. '...WHERE name=%s'" into believed that any of the regular
>printf substitutions should work.

They should!!! I don't think changing the DB-API spec will do much
good. People will still assume that if %s works, so should %f. As
Python programmers we take this kind of orthogonality for granted,
and it's one of Python's great strengths that we can do that. The
fact that Python feels familiar even if we venture into a new field,
and don't have a lot of odd execptions, is a big reason why it's so
easy to learn.

Chris Cogden and I got into a long discussion that probably bored all
the others. The argument put forward for allowing only %s was that the
application programmer should not be bothered with *how* the values are
translated. For the other paramstyles---?, :1 and :name---you let the db
interface determine how to pass the values, and there is no reason that
it should be different in interfaces using 'format' or 'pyformat'.

Other people should talk for themselves. My standpoint is that:

* If %s is permitted, so should %d and %f etc. Otherwise, it's very
   misleading. Regardless of what the DB-API says, the issue will continue
   to will pop up here, on python-tutor, on comp.lang.python etc.

* It's trivial to change the code so that all format codes work. I've
   presented the modified code for sqlite and pyGreSQL.

* If we *don't* want to permit %d and %f etc, we should use ? and
   :name instead of %s and %(name)s.

* I've posted code that transforms ? notation to %s, so supporting
   paramstyle 'qmark' in the drivers that support 'format' today is no
   problem. At least noone has pointed out any problems with my code yet.
   To support paramstyle 'named' instead of 'pyformat' is equally simple.

* On changing from format/pyformat to qmark/named, we must either have
   some kind of autodetect or a mode setting to make the drivers backward
   compatible, but that's no big deal. It's something we should decide
   how to do though.

* I think it would be great if the paramstyles format and pyformat
   could eventually be deprecated, and all drivers could implement
   qmark or named or both. Those are the only two that are in line
   with the SQL standard.

As far as I understand, the following drivers support qmark today:

odbc, mxODBC, kInterbase, pyDB2, sapdb.

If we could add support for sqlite, PostgreSQL and MySQL to that
list, it would be possible to support a lot of backends with the
same code, as long as we use simple enough SQL statements...

For Oracle, there is DCOracle2 that supports 'numeric', and
cx_Oracle that supports 'named'. The driver for Sybase supports
Transact-SQL's @-parameter, which is not in line with the DB-API
standard at all.

I hope I'm correct with these. There are a few remaining drivers
(Ingres, Informix etc) that I haven't investigated.

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 

More information about the DB-SIG mailing list