sqlalchemy-config 0.4.6b0

Handle sqlalchemy connection params in a config file.

Getting Started with sqlalchemy-config

version: 0.4.6b0

The sqlalchemy-config package is a toolkit to handle database configuration parameters that are stored in a config file. This can locate, read, and extract the config parameters and create a sessionmaker object from them that is bound to the engine object.

There is online documentation for sqlalchemy-config.

Installation instructions

At present, sqlalchemy-config is undergoing development and no packages exist yet on PyPi. Therefore it is recommended that it is installed in either of the two ways listed below.

Installation using pip

You can install using pip from the root of the cloned repository, first clone and cd into the repository root:

git clone [e-mail]:cfinan/sqlalchemy-config.git
cd sqlalchemy-config

Then install using pip:

python -m pip install .

Or for an editable (developer) install run the command below from the root of the repository. The difference with this is that you can just do a git pull to update, or switch branches without re-installing:

python -m pip install -e .

Installation using conda

I maintain a conda package in my personal conda channel. To install this please run:

conda install -c cfin -c conda-forge sqlalchemy-config

Conda dependencies

There are also conda packaging files in ./resources/conda that mirror runtime dependencies for conda-based workflows. I use this to install requirements via conda and then install the package as an editable pip install.

However, if you find these useful then please use them. There are Conda environments for Python v3.11, v3.12 and v3.13.

Usage TL;DR

sqlalchemy-config is designed to handle database connection inputs coming from different sources. For example: a URL or SQLite file path on the command line, a config file path from a command-line argument, an environment variable, or a default file in the home directory.

The config files used in the example below have a similar structure to this see SQLAlchemy engine from config:

[mydb_mysql]
# Make sure this is only readable by you
# Make sure password is URL encoded, see: sqlalchemy_config.encode_password
db.url = mysql+pymysql://user:password@127.0.0.1:3306/db_cfg_arg

>>> import sqlalchemy_config as sqlc
>>> import os

>>> # Input from the command line (SQLite file path)
>>> dbarg = "mydb.db"

>>> # Prefix used for SQLAlchemy parameters in config sections
>>> conn_prefix = "db."

>>> # This represents a default location for config files containing parameters
>>> config_file = os.path.join(os.environ['HOME'], "mydbs_def.cnf")

>>> # dbarg is treated as SQLite file path
>>> sqlc.get_new_sessionmaker(dbarg, conn_prefix=conn_prefix, config_arg=config_file)
>>> sessionmaker(class_='Session', bind=Engine(sqlite:///mydb.db), autoflush=True, autocommit=False, expire_on_commit=True)

>>> # dbarg can also be a fully formed SQLAlchemy URL
>>> dbarg = "mysql+pymysql://user:password@127.0.0.1:3306/db_url_arg"
>>> sqlc.get_new_sessionmaker(dbarg)
>>> sessionmaker(class_='Session', bind=Engine(mysql+pymysql://user:***@127.0.0.1:3306/db_url_arg), autoflush=True, autocommit=False, expire_on_commit=True)

The original get_sessionmaker function is shown below, note that this is deprecated.

[mydb_mysql]
# Make sure this is only readable by you
# Make sure password is URL encoded, see: sqlalchemy_config.encode_password
db.url = mysql+pymysql://user:password@127.0.0.1:3306/db_cfg_arg

>>> import sqlalchemy_config as sqlc
>>> import os

>>> # Command-line URL/file argument
>>> url_arg = "mydb.db"

>>> # Section in the config file containing connection parameters
>>> config_section = 'mydb_mysql'

>>> # Prefix for SQLAlchemy connection parameters
>>> config_prefix = 'db.'

>>> # Optional command-line config file path
>>> config_arg = os.path.join(os.environ['HOME'], "mydbs_arg.cnf")

>>> # Environment variable name that may hold a config file path
>>> config_env = "MYDBS"

>>> # This represents a default location for config files containing parameters
>>> config_default = os.path.join(os.environ['HOME'], "mydbs_def.cnf")

>>> # URL/file argument takes priority when provided
>>> sqlc.get_sessionmaker(config_section, config_prefix, url_arg=url_arg, config_arg=config_arg, config_env=config_env, config_default=config_default)
>>> sessionmaker(class_='Session', bind=Engine(sqlite:///mydb.db), autoflush=True, autocommit=False, expire_on_commit=True)

>>> # URL argument can also be a full SQLAlchemy connection URL
>>> url_arg = "mysql+pymysql://user:password@127.0.0.1:3306/db_url_arg"
>>> sqlc.get_sessionmaker(config_section, config_prefix, url_arg=url_arg, config_arg=config_arg, config_env=config_env, config_default=config_default)
>>> sessionmaker(class_='Session', bind=Engine(mysql+pymysql://user:***@127.0.0.1:3306/db_url_arg), autoflush=True, autocommit=False, expire_on_commit=True)

>>> # URL missing: fall back to config_arg then config_env then config_default
>>> url_arg = None
>>> sqlc.get_sessionmaker(config_section, config_prefix, url_arg=url_arg, config_arg=config_arg, config_env=config_env, config_default=config_default)
>>> sessionmaker(class_='Session', bind=Engine(mysql+pymysql://user:***@127.0.0.1:3306/db_cfg_arg), autoflush=True, autocommit=False, expire_on_commit=True)

>>> # URL and config_arg missing: fall back to environment variable
>>> url_arg = None
>>> config_arg = None
>>> sqlc.get_sessionmaker(config_section, config_prefix, url_arg=url_arg, config_arg=config_arg, config_env=config_env, config_default=config_default)
>>> sessionmaker(class_='Session', bind=Engine(mysql+pymysql://user:***@127.0.0.1:3306/db_env_var), autoflush=True, autocommit=False, expire_on_commit=True)

>>> # URL, config_arg, and config_env missing: use default path
>>> url_arg = None
>>> config_arg = None
>>> config_env = None
>>> sqlc.get_sessionmaker(config_section, config_prefix, url_arg=url_arg, config_arg=config_arg, config_env=config_env, config_default=config_default)
>>> sessionmaker(class_='Session', bind=Engine(mysql+pymysql://user:***@127.0.0.1:3306/db_default), autoflush=True, autocommit=False, expire_on_commit=True)

See the API documentation for lower level functions.

Development

To set up a development environment, install the package with development dependencies:

pip install -e ".[dev]"

Run the test suite:

pytest tests/ -v

Run tests with coverage reporting:

pytest --cov=sqlalchemy_config --cov-report=term-missing tests/

Verify the coverage threshold (80%) is met:

pytest --cov=sqlalchemy_config --cov-fail-under=80 tests/

Lint and format code before committing:

ruff check --fix . && ruff format .

Change log

See CHANGELOG.md for the full change log.