sqlite-logbook

SQLAlchemy-backed logging for Python applications. Every log record is written to stdout and, when available, to a SQLite database or a MySQL, MariaDB, or PostgreSQL database.

Installation

python -m pip install sqlite-logbook

Usage

Initialize the database before creating a logger. The parent directory for a SQLite database must already exist.

import logging
from pathlib import Path

from sqlite_logbook import get_logger, init_db

database_path = Path("logs") / "application.sqlite3"
database_path.parent.mkdir(exist_ok=True)

init_db(database_path)
logger = get_logger("my_application", level=logging.INFO)

logger.info("Application started")
try:
	raise ValueError("An example error")
except ValueError as error:
	logger.error("Request failed", exc=error)

Each persisted record includes the logger name, timestamp, level, and formatted message. Passing exc to error or critical also appends the exception source location to the message. Stdout logging defaults to INFO and can be changed with the numeric LOGGING_STDOUT_LVL environment variable.

Design Decisions

• Stdout is the primary output. After init_db has been called, get_logger attaches a shared stdout handler to the named logger. Records at or above that handler's configured level are printed to stdout whether or not database persistence is active.
• The database is optional. If initialization fails, init_db leaves the package in stdout-only mode. A database handler is attached only after a session factory has been created successfully.
• Write failures are best-effort. Each record is added and committed in its own session. Exceptions raised while adding or committing are rolled back and suppressed, so a failed database write does not raise from that handler.
• Initialization state is module-level and process-local. The first successful init_db call stores an SQLAlchemy engine and session factory for the current Python process. Later calls return those same objects and warn when a different connection string is supplied. SQLAlchemy manages the underlying physical connections; the package does not keep one connection permanently open.
• SQLite enables WAL on connect. Local SQLite engines use a five-second connection timeout and execute PRAGMA journal_mode=WAL for each new DB-API connection.
• The database handler derives timestamps in UTC. It converts the logging record's creation time with datetime.fromtimestamp(..., tz=timezone.utc).

Database Backends

Use a SQLAlchemy connection URL and set local=False for a remote database.

from sqlite_logbook import init_db

init_db(
		"postgresql+psycopg2://logbook:password@db.example.com:5432/logbook",
		local=False,
)

The installed drivers support these URL forms:

• MySQL: mysql+pymysql://user:password@host:3306/database
• MariaDB: mariadb+pymysql://user:password@host:3306/database
• PostgreSQL: postgresql+psycopg2://user:password@host:5432/database

API

• init_db(connection_str, table_name=None, local=True) initializes the process-wide SQLAlchemy session factory and creates the log table. Set table_name to use a table other than logs.
• get_logger(name="app", level=logging.INFO) returns a standard-library logger that writes to stdout and, after successful initialization, the configured database.

Initialization is process-wide. A later init_db call with a different connection string leaves the original database in use and emits a warning. When database initialization fails, logging falls back to stdout-only output.

Development

The integration tests require MySQL, PostgreSQL, and MariaDB. Start the local services, run the full test suite, then tear them down.

docker compose up -d --wait
python -m pip install --upgrade pip build twine
python -m pip install -e .
python -m unittest discover -s unit_tests -v
python -m build
python -m twine check dist/*
docker compose down -v

The CI workflow runs the tests on Python 3.9 through 3.13.

Release Checklist

1. Update __version__ in sqlite_logbook/__init__.py.
2. Run the development test and build commands.
3. Commit the release and create a tag matching the version, for example v0.1.0.
4. Push the tag. The publish workflow creates the GitHub release and publishes to PyPI through trusted publishing.