How to enable/disable logging in SQLAlchemy

Introduction

Logging is an essential feature when developing applications with database operations to track SQL statements and errors. This tutorial demonstrates enabling and disabling logging in SQLAlchemy, a popular ORM framework for Python.

Basic Setup

Before diving into logging, ensure you have SQLAlchemy installed: pip install sqlalchemy

Now, set up a basic SQLAlchemy engine:

from sqlalchemy import create_engine
engine = create_engine('sqlite:///example.db', echo=True)

The echo parameter is a quick way to enable logging. If set to True, SQLAlchemy will log all the SQL it executes to the standard output.

Using Python’s Logging Module

To have more control over logging, use Python’s built-in logging module:

import logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger('sqlalchemy.engine')

Now, SQLAlchemy logs its executed SQL to wherever the current logging configuration sends its outputs, such as a file or the console.

Configuring Loggers

To enable or disable logging dynamically, adjust the logger levels:

logger.setLevel(logging.INFO)  # To enable logging
logger.setLevel(logging.NOTSET) # To disable logging

You can also configure the logging to be more granular:

logger.setLevel(logging.WARNING) # Only logs warnings and above

For SQLAlchemy-specific logs, adjust the levels like this:

logger = logging.getLogger('sqlalchemy.engine.base.Engine')
logger.setLevel(logging.INFO)

Advanced Configuration with DictConfig

If your application requires a more complex logging setup, you can use dictConfig:

from logging.config import dictConfig

dictConfig({
    'version': 1,
    'disable_existing_loggers': False,
    'formatters': {
        'default': {
            'format': '%(asctime)s - %(name)s - %(levelname)s - %(message)s'
        }
    },
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
            'formatter': 'default'
        }
    },
    'loggers': {
        'sqlalchemy.engine': {
            'level': 'INFO',
            'handlers': ['console']
        }
    }
})

This allows for multiple handlers, formatters, and loggers with detailed configuration.

Filtering Log Output

To limit the output, you might want to filter what gets logged:

class MyFilter(logging.Filter):
    def filter(self, record):
        return 'SELECT' in record.msg

logger.addFilter(MyFilter())

This custom filter only logs messages that contain the word ‘SELECT’.

Disabling Logging in Tests

During testing, you might want to disable logging to clean up test output:

import os

def setUp(self):
    logging.getLogger('sqlalchemy.engine').setLevel(logging.NOTSET)
    os.environ['SQLALCHEMY_ECHO'] = 'false'

def tearDown(self):
    logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
    os.environ['SQLALCHEMY_ECHO'] = 'true'

This approach is useful in unit test setups where echoing SQL statements is irrelevant.

Integrating with an Application Framework

When using SQLAlchemy with frameworks like Flask or Django, you’ll configure logging through the framework’s preferred mechanisms. For Flask, you could:

if app.debug:
    logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

Always ensure your logging configuration aligns with your application’s setup and environment.

Conclusion

In conclusion, enabling and disabling logging in SQLAlchemy is straightforward, whether through the echo flag, the logging module, or application-specific configurations. These methods allow you to control the verbosity of your application’s database interactions, making debugging efficient and production logs clean.