sqlalchemy-mate

What it is and what it does

sqlalchemy_mate is a wrapper library around SQLAlchemy that provides convenience functions for common database operations. It focuses on two main areas: securely loading database credentials from multiple sources (JSON files, environment variables, AWS S3, AWS KMS) without embedding secrets in code, and performing bulk insert/update/upsert operations more efficiently than row-by-row approaches.

The library extends SQLAlchemy's ORM with an ExtendedBase class that adds methods like smart_insert, update_all, and upsert_all directly to your model classes. It also provides an EngineCreator utility that abstracts away connection string construction, supporting PostgreSQL, Redshift, and other databases. The smart_insert strategy attempts bulk operations but falls back to smaller batches when primary key conflicts occur, reducing total commits compared to one-by-one inserts.

Use it for:

• Load database credentials from JSON files or environment variables without hardcoding secrets in application code.
• Perform bulk inserts of thousands of records while gracefully handling primary key conflicts without one-by-one retry logic.
• Automatically update or upsert multiple records by primary key in a single operation using ORM models.
• Simplify SQLAlchemy connection setup across different database engines (PostgreSQL, Redshift) with a unified API.
• Integrate AWS KMS decryption into credential loading for cloud-deployed applications.

Worth the install?

AI-flagged interpretation of the facts on this page — verify before relying

Simplifies SQLAlchemy code with helper functions for credential management, bulk insert/update/upsert operations, and ORM extensions that reduce boilerplate.

Yes, if you regularly work with SQLAlchemy and need to handle bulk operations or credential management. The library is actively maintained, has no known vulnerabilities, and low install friction. It's most valuable for applications doing frequent bulk inserts with potential conflicts or managing credentials across multiple environments. For simple CRUD operations or single-row inserts, the overhead may not justify the dependency.

Install

sqlalchemy-mate on PyPI

pip install sqlalchemy-mate

uv add sqlalchemy-mate

poetry add sqlalchemy-mate

Installing sqlalchemy-mate

Before you install

Low install friction with only two runtime dependencies (sqlalchemy and prettytable). Actively maintained with recent commits; last release was 799 days ago but repository shows ongoing activity.

License in practice

MIT license is permissive; you can use this package in commercial and proprietary projects without restriction.

Quickstart

pip install sqlalchemy_mate

from sqlalchemy_mate.api import EngineCreator
ec = EngineCreator.from_env(prefix="DB_DEV")
engine = ec.create_postgresql_psycopg2()

from sqlalchemy_mate.api import ExtendedBase
User.smart_insert(engine, data)

Requires sqlalchemy and prettytable as runtime dependencies; Python 3.8 or later.

Verify before relying

• Whether smart_insert actually reduces commit count compared to standard bulk operations in practice.
• Performance characteristics of smart_insert strategy with very large datasets.
• Compatibility with SQLAlchemy 2.0+ async features and modern connection pooling.

Package facts

Evidence: sqlalchemy_mate-2.0.0.3-py3-none-any.whl

Tags

More Database packages