How to code efficiently for multiple database engines with Exceptions

[Joe Barnhart]

So my website runs under PostgresSQL today but will likely be switched to MySql at some point in the future.  My problem isn't with the DAL -- it performs fine for what it does, insulating me very well from the nuances of the SQL syntax for each engine.

No, my problem comes from Exceptions.  Each database engine defines its own Exceptions within its driver.  Even different drivers for the same database engine (psycopg2 vs pg8000) come with different Exception trees.  If I want to be able to handle database exceptions (and who doesn't?) how do I load and use the RIGHT set of Exceptions for each driver?

When using the pg8000 driver for example, the Exceptions raised are all of the form:  "gluon.contrib.pg8000.ProgrammingError" or some such.  Errors from psycopg2 and the mysql drivers are similarly formed.  I can't even use this in a try-except block unless I first import gluon.contrib.pg8000.  The same is true for exceptions from all other database engines.

Should we "alias" these database errors into a consistent tree inside web2py?  If we created a database exception tree inside web2py, and then added to the database driver module a section that raised the corresponding web2py Exception whenever a database exception was triggered, then we could just catch ProgrammingError (from web2py) instead of gluon.contrib.pg8000.ProgrammingError.

Or, should I just put somewhere in the db definition of the models a line like "import gluon.contrib.pg8000 as mydb" and then use "mydb.ProgrammingError" throughout my code?  I suppose I'd need to repeat this in every module I create, and I create a LOT of modules....

Anyway, is this a real problem or am I missing something obvious??

-- Joe

[Anthony]

According to the Python DB API specification, there should be a common set of exceptions, and they should be exposed as attributes on the connection object. Therefore, you should be able to do something like:

try:
    db.mytable.insert(**fields)
except (db._adapter.driver.IntegrityError, db._adapter.driver.OperationalError) as e:
    pass

The above should catch the IntegrityError and OperationalError of any DB API compliant driver.

Also, when making inserts or updates, if you don't care about the specific exception but just want to catch any database errors, the DAL API includes the following:

db.mytable._on_insert_error = my_insert_error_handler
db.mytable._on_update_error = my_update_error_handler

Anthony

[Anthony]

And more generally, you could always do something like:

try:
    db.mytable.insert(**fields)
except Exception as e:
    if type(e).__name__ == 'OperationalError':
        pass
    else:
        raise e

Anthony

[Joe Barnhart]

Sweet!  That's exactly what I'm looking for.  I was missing the db._adapter connection to pg8000 or whatever driver is used at the moment.  This should totally fix my wagon.

-- Joe

[Massimo Di Pierro]

I strongly recommend you do not use pg8000. We always run into issues with it. 

pip install psycopg2

Massimo

[Joe Barnhart]

Hi Massimo --

Two issues...

1.  I use a Mac.  For whatever reason, psycopg2 does not "pip install" on a Mac.  I researched it for a day or so -- even AFTER having had it WORKING on my system until I "upgraded" to El Capitan and it all quit.  I am so done with psycopg2 because its installation is crap.

2.  The future belongs to pypy and psycopg2 doesn't work with it.  pg8000 does.  There is a psycopg2.cffi but I don't know how mature it is.

It's all enough to make me want to switch to MySql!

-- Joe

[Julian Sanchez]

Hi Joe,

psycopg2 installs on mac (I have it on 3).  'pip install' will fail because psycopg2 needs the postgresql client libraries already installed.  If you have homebrew just do a 'brew postgresql' followed by 'pip install psycopg2'.  It should work.

Cheers,

Julian

[Joe Barnhart]

It's more complicated than that.  There are obsolete versions of some libraries, such as libel, which need to be replaced.  However, those libs are not easily replaced because you can't simply "ln" an entry into the directory where it needs to go.  There are workarounds, and potential problems with the workarounds.  This is all on El Capitan so your milage may vary.

It's just an unholy mess.  I'm really surprised the PostgresSQL and Apple people can't come up with a way around it.  Or the psycopg2 people.  Whoever.

-- Joe

[Joe Barnhart]

Of course that was LIBSSL before f-ing auto spell got ahold of it...

[tim.n...@conted.ox.ac.uk]

It may be a bad move, but I defined IntegrityError in a model, so it can be nice and terse everywhere else:

IntegrityError = idb._adapter.driver.IntegrityError

Where I have two different databases, I just added it to the database objects:

db.IntegrityError = db._adapter.driver.IntegrityError
idb.IntegrityError = idb._adapter.driver.IntegrityError

Allows for nice readable except statements

[Joe Barnhart]

Ooooh......  I like that.  I may think of that idea myself!  Good job.

-- Joe

[Dave S]

On Tuesday, September 13, 2016 at 11:25:29 AM UTC-7, Joe Barnhart wrote:

Ooooh......  I like that.  I may think of that idea myself!  Good job.

-- Joe

If going this way, I think I'd base it on Anthony's comment.

/dps

[Massimo Di Pierro]

On Mac my advice is to use Anaconda. Their conda package system too is great and handles all dependencies correctly.