[Zope] Database connectors

[Dario Lopez-Kästen]

rieh25 said the following on 11/16/2006 09:12 PM:
> I wanted to do it because for some reason, my oracle connector (DCOracle)
> keeps disconnecting, and so I thought maybe it would be possible to define a
> python method to periodically check on it and reconnect it if necessary.
> 

i one ancient app that i am trying to modernise, I have a python script
that gets called by wget with cron every N minutes. That script calls a
zsql that does a select from dual, and the script catches any errors.

The set up is a s follows:

* My site uses several DCO2 connections objects, one for each of a bunch
of schemas in the database. We serve the site in a cluster of 6 nodes
(using zeo - nothing fancy: 4 nodes from one machine, 2 nodes from
another machine).

* for various reasons, the connecions get dropped sometimes, and we need
to catch that and restart the affected node. Hence the scripts.

* All my DCO2 connection objects are at the root of the site, so they
are easy to acquire.

* in the root of the site, I have a folder "heartbeat". That folder
contains one ZSQL method for each of the DCO2 connection objects, and
one Python script that is used to check one or all of the connections'
status.

The sql in the ZSQL methods is supersimple:

--<begin sql>--
select 'name_of_dco2_connection', sysdate from dual
--<end sql>--

and the Python script looks like this:

--<begin script>---
## Script (Python) "index"
##bind container=container
##bind context=context
##bind namespace=
##bind script=script
##bind subpath=traverse_subpath
##parameters=name=None
##title=
##
""" This script checks the status of connection 'name'. If 'name' is
None, check all connections
"""
if name is None:
    # Check all connections. Get list of zsql methods in
    # the same folder as this script.
    cobjs = [obj for obj in context.ZopeFind(container,
obj_metatypes=('Z SQL Method',))]
    for o in cobjs:
        # call each script: o[0] is the name,
        # date is the result from the script
        try:
            sys, date = (o[0], o[1]()[0].sysdate)
            print sys, str(date)
        except ConflictError:
            # apparently, ConflictErrors need to be passed on
            raise
        except Exception, Msg:
            # oops, something Oracle-y messed up
            print o[0], str(Exception), str(Msg)

    # return the results
    return printed

# try individual names
try:
    # see if the connection exists (actually we
    # check if there is a zsql named 'name'
    con = container[name]
except KeyError:
    print "There is no connection named %s"%name
    return printed

try:
    # found it, check it
    sys, date = name, con()[0].sysdate
    print sys, str(date)
    return printed
except ConflictError:
    # apparently, ConflictErrors need to be passed on
    raise
except Exception, Msg:
    print name, str(Exception), str(Msg)
    return printed

# We shouldn't be here, so aliens must have
# hijacked us
print "ALL YOUR BASE ARE BELONG TO US!"
return printed
--<end script>--

I have cron job that calls a shell script that uses wget to call

www.<insert your site here>.com/heartbeat/index

that shell script compares the output from wget to decied if there is an
error or if the connections are ok.

If there is an error, we restart the node that had problems.


Hope this helps, it works for us :-)

/dario


-- 
-- -------------------------------------------------------------------
Dario Lopez-Kästen, IT Systems & Services Chalmers University of Tech.
Lyrics applied to programming & application design:
"emancipate yourself from mental slavery" - redemption song, b. marley