Creating a logging handler to connect to Oracle?

Question

So right now i need to create and implement an extension of the Python logging module that will be used to log to our database. Basically we have several python applications(that all run in the background) that currently log to a random mishmash of text files. Which makes it almost impossible to find out if a certain application failed or not.

The problem given to me is to move said logging to text files to an oracle DB. The tables have already been defined, and where things need to be logged to but right now, im looking at adding another logging handler that will log to the DB.

I am using python 2.5.4 and cx_Oracle and the applications in general can be ether run as a service/daemon or a straight application.

I'm just mainly curious about what would be the best possible way to go about this. Few questions:

1. If any errors occur with cx_Oracle, where should these errors be logged to? If its down would it be best to just go and have the logger retreat to the default text file?

2. Awhile back we started enforcing that people use sys.stderr/stdout.write instead of print, so worst case scenario we wouldn't run into any issues with print becoming deprecated. Is there a way to seamlessly make all of the thousands of sys.std calls be piped directly into the logger, and have the logger pickup the slack?

3. After every logged message, should the script automatically do a commit? (there's going to be several dozen a second.)

4. What is the best way to implement a new handler for the logging system? Inheriting from the basic Handler class seems to be easiest.

Any ideas / suggestions would be great.

Answer 1

In case you're interested in commercial off-the-shelf solution, I can suggest looking at logFaces. There is a contributed package for using it directly with Python applications (see download page). Didn't try it myself but Python gurus say it's good. In any case I wouldn't couple database logging directly with an application. Too risky for database down times, maint. times and other surprises. Unless, of course, all your application produce is the log. Logging into database should be an option, not the must. Because one day you'll want to "travel light" and not carrying those scary dependencies with you all the way.

Sorry if the answer is too philosophical :)

Answer 2

1. If errors occur with cx_Oracle, it's probably best to log these to a text file.
2. You could try redirecting sys.stdout and sys.stderr to file-like objects which log whatever's written to them to a logger.
3. I would guess you do want to commit after each event, unless you have strong reasons for not doing this. Alternatively, you can buffer several events and write them all in a single transaction every so often.
4. Below is an example which uses mx.ODBC, you can probably adapt this to cx_Oracle without too much trouble. It's meant to be Python DB-API 2.0 compliant, I think.

The standalone Python logging distribution (before logging was added to Python) is at http://www.red-dove.com/python_logging.html and although the logging package in Python is much more up to date, the standalone distribution contains a test directory which has a lot of useful examples of derived handler classes.

#!/usr/bin/env python
#
# Copyright 2001-2009 by Vinay Sajip. All Rights Reserved.
#
# Permission to use, copy, modify, and distribute this software and its
# documentation for any purpose and without fee is hereby granted,
# provided that the above copyright notice appear in all copies and that
# both that copyright notice and this permission notice appear in
# supporting documentation, and that the name of Vinay Sajip
# not be used in advertising or publicity pertaining to distribution
# of the software without specific, written prior permission.
# VINAY SAJIP DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
# ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL
# VINAY SAJIP BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR
# ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER
# IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT
# OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
#
# This file is part of the standalone Python logging distribution. See
# http://www.red-dove.com/python_logging.html
#
"""
A test harness for the logging module. An example handler - DBHandler -
which writes to an Python DB API 2.0 data source. You'll need to set this
source up before you run the test.

Copyright (C) 2001-2009 Vinay Sajip. All Rights Reserved.
"""
import sys, string, time, logging

class DBHandler(logging.Handler):
    def __init__(self, dsn, uid='', pwd=''):
        logging.Handler.__init__(self)
        import mx.ODBC.Windows
        self.dsn = dsn
        self.uid = uid
        self.pwd = pwd
        self.conn = mx.ODBC.Windows.connect(self.dsn, self.uid, self.pwd)
        self.SQL = """INSERT INTO Events (
                        Created,
                        RelativeCreated,
                        Name,
                        LogLevel,
                        LevelText,
                        Message,
                        Filename,
                        Pathname,
                        Lineno,
                        Milliseconds,
                        Exception,
                        Thread
                   )
                   VALUES (
                        %(dbtime)s,
                        %(relativeCreated)d,
                        '%(name)s',
                        %(levelno)d,
                        '%(levelname)s',
                        '%(message)s',
                        '%(filename)s',
                        '%(pathname)s',
                        %(lineno)d,
                        %(msecs)d,
                        '%(exc_text)s',
                        '%(thread)s'
                   );
                   """
        self.cursor = self.conn.cursor()

    def formatDBTime(self, record):
        record.dbtime = time.strftime("#%m/%d/%Y#", time.localtime(record.created))

    def emit(self, record):
        try:
            #use default formatting
            self.format(record)
            #now set the database time up
            self.formatDBTime(record)
            if record.exc_info:
                record.exc_text = logging._defaultFormatter.formatException(record.exc_info)
            else:
                record.exc_text = ""
            sql = self.SQL % record.__dict__
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            import traceback
            ei = sys.exc_info()
            traceback.print_exception(ei[0], ei[1], ei[2], None, sys.stderr)
            del ei

    def close(self):
        self.cursor.close()
        self.conn.close()
        logging.Handler.close(self)

dh = DBHandler('Logging')
logger = logging.getLogger("")
logger.setLevel(logging.DEBUG)
logger.addHandler(dh)
logger.info("Jackdaws love my big %s of %s", "sphinx", "quartz")
logger.debug("Pack my %s with five dozen %s", "box", "liquor jugs")
try:
    import math
    math.exp(1000)
except:
    logger.exception("Problem with %s", "math.exp")