python logging to database

Question

I'm seeking a way to let the python logger module to log to database and falls back to file system when the db is down.

So basically 2 things: How to let the logger log to database and how to make it fall to file logging when the db is down.

Answer 1

I recently managed to write my own database logger in Python. Since I couldn't find any example I thought I post mine here. Works with MS SQL.

Database table could look like this:

CREATE TABLE [db_name].[log](     [id] [bigint] IDENTITY(1,1) NOT NULL,     [log_level] [int] NULL,     [log_levelname] [char](32) NULL,     [log] [char](2048) NOT NULL,     [created_at] [datetime2](7) NOT NULL,     [created_by] [char](32) NOT NULL, ) ON [PRIMARY] 

The class itself:

class LogDBHandler(logging.Handler):     '''     Customized logging handler that puts logs to the database.     pymssql required     '''     def __init__(self, sql_conn, sql_cursor, db_tbl_log):         logging.Handler.__init__(self)         self.sql_cursor = sql_cursor         self.sql_conn = sql_conn         self.db_tbl_log = db_tbl_log      def emit(self, record):         # Set current time         tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(record.created))         # Clear the log message so it can be put to db via sql (escape quotes)         self.log_msg = record.msg         self.log_msg = self.log_msg.strip()         self.log_msg = self.log_msg.replace('\'', '\'\'')         # Make the SQL insert         sql = 'INSERT INTO ' + self.db_tbl_log + ' (log_level, ' + \             'log_levelname, log, created_at, created_by) ' + \             'VALUES (' + \             ''   + str(record.levelno) + ', ' + \             '\'' + str(record.levelname) + '\', ' + \             '\'' + str(self.log_msg) + '\', ' + \             '(convert(datetime2(7), \'' + tm + '\')), ' + \             '\'' + str(record.name) + '\')'         try:             self.sql_cursor.execute(sql)             self.sql_conn.commit()         # If error - print it out on screen. Since DB is not working - there's         # no point making a log about it to the database :)         except pymssql.Error as e:             print sql             print 'CRITICAL DB ERROR! Logging to database not possible!' 

And usage example:

import pymssql import time import logging  db_server = 'servername' db_user = 'db_user' db_password = 'db_pass' db_dbname = 'db_name' db_tbl_log = 'log'  log_file_path = 'C:\\Users\\Yourname\\Desktop\\test_log.txt' log_error_level     = 'DEBUG'       # LOG error level (file) log_to_db = True                    # LOG to database?  class LogDBHandler(logging.Handler):     [...]  # Main settings for the database logging use if (log_to_db):     # Make the connection to database for the logger     log_conn = pymssql.connect(db_server, db_user, db_password, db_dbname, 30)     log_cursor = log_conn.cursor()     logdb = LogDBHandler(log_conn, log_cursor, db_tbl_log)  # Set logger logging.basicConfig(filename=log_file_path)  # Set db handler for root logger if (log_to_db):     logging.getLogger('').addHandler(logdb) # Register MY_LOGGER log = logging.getLogger('MY_LOGGER') log.setLevel(log_error_level)  # Example variable test_var = 'This is test message'  # Log the variable contents as an error log.error('This error occurred: %s' % test_var) 

Above will log both to the database and to the file. If file is not needed - skip the 'logging.basicConfig(filename=log_file_path)' line. Everything logged using 'log' - will be logged as MY_LOGGER. If some external error appears (i.e. in the module imported or something) - error will appear as 'root', since 'root' logger is also active, and is using the database handler.