Re: DB-API format string conventions

Discussion in 'Python' started by Craig Ringer, Dec 28, 2004.

  1. Craig Ringer

    Craig Ringer Guest

    On Tue, 2004-12-28 at 18:29, Craig Ringer wrote:

    > Would there be any interest in releasing a DB-API 2.1 with one
    > parameter style made MANDATORY, and a tuple of other supported styles in
    > .paramstyles ? I think existing modules implemented in Python could be
    > retrofitted to take extended printf quite easily, though at a small
    > performance cost when extended printf was used. Modules in pure C would
    > be more work, but still probably not a big deal.


    MySQLdb, psycopg, and pyPgSQL seem to all support 'pyformat' (python
    extended printf) though mysql lists 'format' in paramstyle. I'm not able
    to test any other DB interfaces at the moment, but if others support
    pyformat then perhaps that's a viable choice to make mandatory in a
    revision of the spec? That way any code could check for DB-API 2.1 and
    know it could use pyformat style in addition to any other styles the
    code permitted. Perhaps more importantly, it could also tell Python
    programmers they can rely on pyformat style being available.

    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.

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

    --
    Craig Ringer
     
    Craig Ringer, Dec 28, 2004
    #1
    1. Advertising

  2. Craig Ringer

    Steve Holden Guest

    Craig Ringer wrote:

    > On Tue, 2004-12-28 at 18:29, Craig Ringer wrote:
    >
    >
    >> Would there be any interest in releasing a DB-API 2.1 with one
    >>parameter style made MANDATORY, and a tuple of other supported styles in
    >>.paramstyles ? I think existing modules implemented in Python could be
    >>retrofitted to take extended printf quite easily, though at a small
    >>performance cost when extended printf was used. Modules in pure C would
    >>be more work, but still probably not a big deal.

    >
    >
    > MySQLdb, psycopg, and pyPgSQL seem to all support 'pyformat' (python
    > extended printf) though mysql lists 'format' in paramstyle. I'm not able
    > to test any other DB interfaces at the moment, but if others support
    > pyformat then perhaps that's a viable choice to make mandatory in a
    > revision of the spec? That way any code could check for DB-API 2.1 and
    > know it could use pyformat style in addition to any other styles the
    > code permitted. Perhaps more importantly, it could also tell Python
    > programmers they can rely on pyformat style being available.
    >
    > 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.
    >
    > So ... anybody for a DB-API 2.1 with mandatory pyformat support and a
    > tuple dbmodule.paramstyles for supported styles?
    >

    Well, you can certainly put me down as supporting less variability in
    allowed paramstyles, to the extent that it would allow much more
    application portability.

    However, you might not be aware that the reason that variability was
    included in the first place is to allow Python module authors to take
    advantage of features already available in the various underlying
    database platform support code - Oracle, for example, already supports
    numbered access :)1), and so on.

    So be aware that you are asking the various module authors for
    significant amounts of work, which may not be forthcoming under all
    circumstances.

    Also be aware that there have been various post-2.0 proposals for the DB
    API, which you might want to look up on Google and fold in to the
    current campaign.

    regards
    Steve
    --
    Steve Holden http://www.holdenweb.com/
    Python Web Programming http://pydish.holdenweb.com/
    Holden Web LLC +1 703 861 4237 +1 800 494 3119
     
    Steve Holden, Dec 28, 2004
    #2
    1. Advertising

  3. Craig Ringer

    Craig Ringer Guest

    On Tue, 2004-12-28 at 21:16, Steve Holden wrote:

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

    >
    > Well, you can certainly put me down as supporting less variability in
    > allowed paramstyles, to the extent that it would allow much more
    > application portability.
    >
    > However, you might not be aware that the reason that variability was
    > included in the first place is to allow Python module authors to take
    > advantage of features already available in the various underlying
    > database platform support code - Oracle, for example, already supports
    > numbered access :)1), and so on.


    I'm not actually against the number of choices available, I'm just
    concerned that there is currently no _single_ choice that can be
    guaranteed to work, and that it's hard to identify all styles a given
    API supports.

    Of course, even if one does confine ones self strictly to what can be
    guaranteed to work in the DB API, there are a multitude of differences
    in database SQL syntax and extensions to worry about. That is an issue
    any programmer will face - not just a Python DB-API 2.0 user, and I see
    it as a separate issue. Tackling that would involve building a DB
    abstraction layer like the various query builders available for Java - a
    very interesting, but much bigger, job that I'm not even remotely
    interested in looking at right now ;-)

    > So be aware that you are asking the various module authors for
    > significant amounts of work, which may not be forthcoming under all
    > circumstances.


    That's true, and something I'm keeping in mind. I'd be quite happy to
    implement the required changes for modules I'm familar with to help with
    that issue.

    That said, if pyformat was chosen as the required style it might not be
    too big a job at all. Many modules already support pyformat though not
    all advertise the fact, and for them it may be as simple as bumping the
    API version to 2.1 and adding a module-level var containing a tuple of
    all supported styles.

    > Also be aware that there have been various post-2.0 proposals for the DB
    > API, which you might want to look up on Google and fold in to the
    > current campaign.


    Indeed. I went looking for proposals specifically related to argument
    handling earlier, but still need to have a look for other API revision
    proposals.

    I thought it best to ask here to find out how much interest there would
    be in clarifying the API and adding a required format style before going
    ahead with actually writing a few patches and a draft PEP for comments.

    --
    Craig Ringer
     
    Craig Ringer, Dec 28, 2004
    #3
  4. Craig Ringer

    Dan Sommers Guest

    On Tue, 28 Dec 2004 19:08:10 +0800,
    Craig Ringer <> 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.
     
    Dan Sommers, Dec 28, 2004
    #4
  5. Denis S. Otkidach, Dec 28, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Craig Ringer

    DB-API format string conventions

    Craig Ringer, Dec 28, 2004, in forum: Python
    Replies:
    0
    Views:
    272
    Craig Ringer
    Dec 28, 2004
  2. ankur
    Replies:
    1
    Views:
    12,815
    Jan =?UTF-8?B?VGhvbcOk?=
    Aug 27, 2007
  3. Chris Angelico
    Replies:
    3
    Views:
    157
    Mark Lawrence
    Mar 1, 2013
  4. Peter Otten
    Replies:
    0
    Views:
    135
    Peter Otten
    Feb 28, 2013
  5. Rick Johnson
    Replies:
    0
    Views:
    143
    Rick Johnson
    Feb 28, 2013
Loading...

Share This Page