DB-API format string conventions

[Dan Sommers]

On Tue, 28 Dec 2004 19:08:10 +0800,
Craig Ringer <craig at postnewspapers.com.au> wrote:

> IMO it'd also be very nice to support **kw calling style, ie to make:

> cursor.execute("SELECT somerow FROM table WHERE otherrow = %(name)s",
>                {'name': 'fred'})

> equivalent to:

> cursor.execute("SELECT somerow FROM table WHERE otherrow = %(name)s",
>                name = 'fred')

> frankly, I simply think it's a nicer and more readable calling style
> when one is passing a list of parameters directly to .execute() rather
> than passing an existing dict. That's just a trivial cosmetic thing,
> though, and while it'd be nicer the mixing of the two styles may cost
> more in confusion than the latter style gains in readability.

I am in no way an expert in this area, but after a round or two of
abstracting, layering, and refactoring, I can't recall having an
explicit list of parameters that close to an SQL literal or a call to
cursor.execute.

I usually end up with a "back-end" that maps higher level constructs,
like dicts or other objects, to database concepts, like rows and tables.
At best, the "top" of the back-end knows about the higher level
constructs creates lists of column names and passes dictionary-like
objects down the "bottom" of the back-end and functions that build SQL
code from lists of column names, interact with the database, trap common
errors, write to logs, etc.  Those lower level functions know about
cursors and result sets, and can [relatively] easily turn rows from the
database into dictionaries based on nothing but information in the
cursor (and maybe some help from a callback function or object factory).

OTOH, I am a very big fan of the cohesion and layering concepts I
learned from years and years of structured programming, and a lot of my
ideas of what should be separate from what have fallen by the wayside.

> So ... anybody for a DB-API 2.1 with mandatory pyformat support and a
> tuple dbmodule.paramstyles for supported styles?

I'd be all for that, but I'm not holding my breath.  ;-)

Regards,
Dan

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