How do I create an SQLite 3 database handler for my Python logger?

Question

I have been trying to create a database handler for my logger with the built-in logging library in Python. I tried to do so by subclassing the main logging module's 'Handler' class but it is throwing many errors, when the StreamHandler class is perfectly fine, and it was written just like how my DatabaseHandler class was written.

I have checked the documentation for the logging library and there appears to be no class for creating custom logging handlers. So, I decided to write the DatabaseHandler class without inheriting attributes from the Handler class and the SQLite 3 code threw an error saying that there is no such table as the 'logs' table when I put in the first line to create that table if it didn't exist.

My code:

from logging import getLogger, StreamHandler, Formatter, Handler, NOTSET, getLevelName
from datetime import datetime as date_time
from sqlite3 import connect

class DatabaseHandler(Handler):
    def __init__(self, db_file):
        super().__init__(self)
        self.db_file = db_file
        self.db_file = connect(self.db_file)
    def emit(self, record):
            """
            Conditionally emit the specified logging record.
    
            Emission depends on filters which may have been added to the handler.
            Wrap the actual emission of the record with acquisition/release of
            the I/O thread lock. Returns whether the filter passed the record for
            emission.
            """
            self.db_file.executescript(
                'CREATE TABLE IF NOT EXISTS logs (date TEXT, '
                'time TEXT, lvl INTEGER, lvl_name TEXT, msg TEXT, '
                'logger TEXT, lineno INTEGER);'
                'INSERT INTO logs VALUES ("%s", "%s", %s, "%s", "%s", "%s", %s)' % (
                    date_time.now().strftime('%A, the %d of %B, %Y'),
                    date_time.now().strftime('%I:%M %p'),
                    record.levelno,
                    record.level,
                    record.msg,
                    record.name,
                    record.lineno
                    )
            )
            self.db_file.commit()
            self.db_file.close()

logger = getLogger(__name__)

logger_formatter = Formatter(
    fmt = '<LVL: %(levelno)s (%(levelname)s), LOGGER: %(name)s> - "%(message)s at %(asctime)s"',
    datefmt = '%I:%M %p on %A, the %d of %B, %Y'
)

logger_stream_handler = StreamHandler()

logger_stream_handler.setFormatter(logger_formatter)
logger_stream_handler.setLevel(10)
logger_database_handler = DatabaseHandler('test.db')

logger.addHandler(logger_stream_handler)
logger.addHandler(logger_database_handler)

logger.log(
    msg = 'Something happened',
    level = 10
)

print(connect('test.db').execute('SELECT name FROM sqlite_master WHERE type = "table"').fetchall())

The idea of how it will work is that it will be added as a handler for the logger and then everytime something is logged and a LogRecord is created, then it will also store the details of the LogRecord in the database's logs table.

I have also created an 'emit' method for the class as that's what I believe is the method that the Handler class in the logging __init__.py module file triggers when a new logging call is made and a LogRecord is created.

But, here's the problem: Whenever I call super().__init__(self) on the class' __init__ method to inherit the attributes from the Handler class, it throws this exception:

Traceback (most recent call last):
  File "<string>", line 47, in <module>
  File "<string>", line 7, in _init_
  File"/lib/python3.8/logging/_init_.py", line 865, in _init_
     self.level = checkLevel(level) 
  File "/lib/python3.8/logging/__init_.py", line 192, in checkLevel
     elif str(level) == level:
  File "/lib/python3.8/logging/__init_.py", line 1035, in _repr_
     level = getLevelName(self.level)
AttributeError: 'DatabaseHandler' object has no attribute 'level'

[Program finished]

[Program finished]

Answer 1

Ok, you were not that far...

The error in calling __init__ is caused by passing self which is interpreted as a level. And even if it is not a true error, creating a table is a Data Definition Language operation and should not be repeated on each message: it should go in the __init__ method:

def __init__(self, db_file):
    super().__init__()
    self.db_file = db_file
    self.db_file = connect(self.db_file)
    self.db_file.execute('CREATE TABLE IF NOT EXISTS logs (date TEXT, '
            'time TEXT, lvl INTEGER, lvl_name TEXT, msg TEXT, '
            'logger TEXT, lineno INTEGER)')

Later in emit you pass a record.level parameter which should be record.levelname. And you should not close the database in the emit method: a logger should be able to log more than one message! Furthermore, you are injecting the parameters in the query itself which is bad because it has been the cause of SQL injection attacks for decades. You should use a parameterized query:

        self.db_file.execute(
            'INSERT INTO logs VALUES (:1,:2,:3,:4, :5, :6, :7)', (
                date_time.now().strftime('%A, the %d of %B, %Y'),
                date_time.now().strftime('%I:%M %p'),
                record.levelno,
                record.levelname,
                record.msg,
                record.name,
                record.lineno
                )
        )
        self.db_file.commit()

And as the default level is NOTSET you should set it on both the logger and its handler:

logger = getLogger(__name__)
logger.setLevel(10)

logger_database_handler = DatabaseHandler('test.db')

logger.addHandler(logger_database_handler)
logger_database_handler.setLevel(10)

logger.log(
    msg = 'Something happened',
    level = 10
)

After those changes you should find the record content in the logs table...