On 13 July 2002, Joel Shprentz said:
> Within a Quixote application running with FastCGI, what is the best
> strategy for opening and closing database connections and for handling
> database errors?

Depends on your application and your definition of "best".  ;-)

> Before sending queries to a database with Python Database API v2.0 (and
> most other database APIs), a connection must be established.  Where in a
> Quixote application should the connection be opened?  In the driver
> script?  In a subclass of Publisher?  In a function or method that handles
> a request?

For the MEMS Exchange site, we subclass SessionPublisher and do it in
the constructor:

from quixote.publish import SessionPublisher
[...]
class VFabPublisher(SessionPublisher):

    # times to retry a request that failed due to a conflict error
    CONFLICT_RETRIES = 3 # same as Zope

    def __init__ (self, root_namespace):
        debug("%s starting up" % self.__class__.__name__)
        SessionPublisher.__init__(self, root_namespace)

        self.read_config("/www/conf/vfab.conf")
        self.setup_logs()
        base.init_database()      # <<< this opens the DB connection
        enable_ptl()
        sess_mgr = base.get_session_manager()
        self.set_session_manager(sess_mgr)

        [...]

In the CGI/FastCGI version of our driver script, everything is quite
straightforward:

def main():
    vfab = VFabPublisher()
    vfab.publish_cgi()

if __name__ == '__main__':
    main()

The SCGI version (which is what we actually use now), does basically the
same thing, but with a bit more indirection because the SCGI package
provides a Quixote handler.  (Hmmm: maybe that code should move to
Quixote -- we already have code in Quixote to handle CGI, FastCGI, and
mod_python.)

> Should the connection ever be closed explicitly?  It will be closed
> automatically when Apache and FastCGI terminate the application.

I think you answered your own question.  Really depends on your
database, doesn't it?

> Database APIs can raise many types of exceptions.  Queries can fail,
> connections can be lost, and disks can fill.  Where is the best place to
> convert a database exception into a subclass of Quixote's publisher error?
> (I have my eye on Publisher.finish_failed_request).

No, that would be too late.  By the time you get to
finish_failed_request(), Quixote has already determined that the error
is *not* a PublishError -- ie. it's going to log an application error
and return 5xx to the HTTP client.  I guess you could change that
behaviour, but that seems evil.

OTOH: database errors are *not* publishing errors -- they are
application errors, just like when your application can't read a file
that it expects to be there.  IMHO the appropriate response to either
"can't read expected file" or "can't connect to database" is to log and
email a traceback, and report "Internal Server Error" to the poor user.

Probably the easiest solution is to override
Publisher._generate_internal_error(); the default one is (deliberately)
quite simple and not very informative.  Keep in mind that by this point,
your application code might be unusable -- you might be dealing with a
SyntaxError or ImportError in some key module, so you really can't
depend on using any of your standard PTL templates.  If you want to give
extra information on certain types of errors, you could use
sys.exc_info() to examine the exception object and act accordingly.

> Database errors can occur very early (when establishing a connection).  In
> the regrettably common case where the database server is down and the web
> server is up, we want to provide a meaningful message to the user.  This
> suggests that the database connection should be established somewhere
> within Publisher.try_publish (or methods it calls).

For that case, you probably want to wrap the database open in
try/except, and set an attribute on your Publisher subclass to record
failure.  Then override start_request(), and make it raise an exception
(which could be a PublishError subclass if you want -- depends on
whether you want to return 4xx or 5xx to the client) that results in an
error page that explains the problem to the user.

Whatever you do, you're venturing into undocumented territory.  Make
sure you're using Quixote 0.5 (custom error-handling improved a lot),
keep publish.py close at hand, and May The Source Be With You.

        Greg
--
Greg Ward - software developer                gward@mems-exchange.org
MEMS Exchange                            http://www.mems-exchange.org