python-sybase-misc

[python-sybase] How to deal with DatabaseError

[Greg W. <gwa...@py...>]

Am I the only one who thinks that there's an ugly flaw in the Python
Database API and it's called DatabaseError?  The fact that every driver
module has to implement and export its own DatabaseError, with no
commonality between the different modules, makes life unnecessarily
difficult.  (We're planning on migrating away from Sybase 11 to something
else, quite possibly Postgres, over the next year or so ... so I cannot
afford to write piles of code that catch Sybase.DatabaseError.)

I can only see two ways to workaround this flaw:

  * write a layer that wraps the DB-API and provides its own
    DatabaseError class, which is just an alias for (e.g.)
    Sybase.DatabaseError.  My abstraction layer (so far) is a
    ConnectionFactory class, but I suspect I'll need to add more classes
    than that.  Right now, I'm starting to write code like this:

      factory = getConnectionFactory(...)
      conn = factory.connect(...)

      try:
         # do stuff with conn
      catch factory.getDatabaseError(), err:
         # handle the error

    (Hmmm: I'm also writing something to wrap the connection; maybe that's
    where the DatabaseError class should be available).

  * write a layer that wraps the DB-API and translates every (e.g.)
    Sybase.DatabaseError to its own DatabaseError.  This will make more
    sense to the Java-heads around here, but it also means that *every*
    database operation will have to go through the abstraction/translation
    layer.  Yuck.

Are there other techniques for dealing with this flaw that I haven't
thought of?

        Greg

Re: [python-sybase] How to deal with DatabaseError

[Andrew M. <an...@ob...>]

>Am I the only one who thinks that there's an ugly flaw in the Python
>Database API [...]

Only one? 8-)

>Are there other techniques for dealing with this flaw that I haven't
>thought of?

I've seen people use things like:

    if ...:
        from pyPgSQL import PgSQL as dbapi2
    elif ...:
        import psycopg2 as dbapi2
    elif ...:
        import sybase as dbapi2

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/

Re: [python-sybase] How to deal with DatabaseError

[Greg W. <gwa...@py...>]

On 20 July 2006, Andrew McNamara said:
> >Am I the only one who thinks that there's an ugly flaw in the Python
> >Database API [...]
> 
> Only one? 8-)

No comment.  (This is the ugly flaw that's annoying me today.)

> I've seen people use things like:
> 
>     if ...:
>         from pyPgSQL import PgSQL as dbapi2
>     elif ...:
>         import psycopg2 as dbapi2
>     elif ...:
>         import sybase as dbapi2

What, in every script or module that does database interaction?  No thanks.
And what about the mythical script that migrates data from Sybase to
Postgres using both drivers?

BTW, PEP 249 (DB-API 2.0) has this under "Optional DB API Extensions":

    Connection Attributes .Error, .ProgrammingError, etc.

        All exception classes defined by the DB API standard should be
        exposed on the Connection objects as attributes (in addition
        to being available at module scope).

        These attributes simplify error handling in multi-connection
        environments.

        Warning Message: "DB-API extension connection.<exception> used"

Obviously this is no good unless all drivers that you want to use support
it.  I'll go see if I can whip up a patch for python-sybase.  In the worst
case, I could monkey-patch those attributes in to every Connection in my
abstraction layer.  (Blech.)

        Greg

Re: [python-sybase] How to deal with DatabaseError

[Greg W. <gwa...@py...>]

On 19 July 2006, I said:
> Obviously this is no good unless all drivers that you want to use support
> it.  I'll go see if I can whip up a patch for python-sybase.  In the worst
> case, I could monkey-patch those attributes in to every Connection in my
> abstraction layer.  (Blech.)

Patch attached.

        Greg

Re: [python-sybase] How to deal with DatabaseError

[Andrew M. <an...@ob...>]

>What, in every script or module that does database interaction?  No thanks.
>And what about the mythical script that migrates data from Sybase to
>Postgres using both drivers?

Well, if you have a large system composed of many modules, it doesn't seem
like much of an additional impost to put the "import...as" stuff in a
module (I've found that I invariably end up with a module that wraps the
dbapi to some degree other anyway).

If you're mixing adapters, sure, it's somewhat more painful. I guess you
could use something like:

    try:
        ... logic ..
    except (sybase.DatabaseError, PgSQL.DatabaseError), e:
        ... error logic...

If you have a common module, you can put that tuple in it, eg:

    DatabaseError = sybase.DatabaseError, PgSQL.DatabaseError

and then just catch with the tuple:

    try:
        ... logic ..
    except DatabaseError, e:
        ... error logic...

>        All exception classes defined by the DB API standard should be
>        exposed on the Connection objects as attributes (in addition
>        to being available at module scope).
[...]
>Obviously this is no good unless all drivers that you want to use support
>it.  I'll go see if I can whip up a patch for python-sybase.  In the worst
>case, I could monkey-patch those attributes in to every Connection in my
>abstraction layer.  (Blech.)

I must admit, I've never noticed that in the DBAPI spec, and a quick
survey suggests support for it is the exception rather than the rule.
It also doesn't help your second case above.

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/