core-db#

This project/library contains common elements related to database engines and provides clients to simplify the connections…

Python Versions License Pipeline Status Docs Status Security

Documentation Contents#

Index:

Features#

Multiple Database Engine Support

  • PostgreSQL (via psycopg)

  • MySQL (via PyMySQL)

  • Oracle (via oracledb)

  • MS SQL Server (via pyodbc)

  • MongoDB (via pymongo)

  • IBM DB2 (via ibm-db)

  • Snowflake (via snowflake-connector-python)

Security

  • Parameterized queries with placeholders to prevent SQL injection

  • Automatic column name validation against injection patterns

  • String value escaping for safe SQL operations

  • Secure identifier validation (alphanumeric and underscore only)

SQL Operations

  • SELECT queries with optional column specification

  • Batch INSERT operations with configurable chunk sizes (default: 500 records per batch)

  • DELETE operations with conditional clauses or primary key filtering

  • MERGE/UPSERT operations (database-specific implementations)

  • CREATE TABLE DDL generation with type mapping and optional PRIMARY KEY, UNIQUE, and NOT NULL constraints

  • Support for temporary table creation

Data Retrieval

  • fetch_one(): Fetch single record as tuple

  • fetch_record(): Fetch single record as dictionary with column names

  • fetch_all(): Fetch all records as iterator of tuples

  • fetch_records(): Fetch all records as iterator of dictionaries

  • Automatic column metadata extraction

Connection Management

  • Context manager support (with statement) for automatic cleanup

  • Automatic connection establishment and closure

  • Automatic commit on exit

  • Connection testing capabilities

  • Factory pattern for dynamic client instantiation

Type Handling

  • Python-to-SQL type mapping (int, float, str, bool, dict, list)

  • JSON type support for dict/list serialization

  • Database-specific type conversions

  • Timestamp/epoch conversion functions

ETL Support

  • Abstract base classes for database-based ETL processes

  • Batch-based record processing with configurable batch sizes

  • Connection lifecycle management within ETL workflows

  • Integration with core-etl framework

  • Dynamic query generation for incremental loads

Developer Experience

  • Comprehensive type hints throughout the codebase

  • Detailed docstrings with usage examples

  • Abstract interfaces for custom implementations

  • Multi-row INSERT for optimal performance

  • Standardized interface across all database engines

Installation#

Install from PyPI using pip:

pip install core-db
uv pip install core-db      # Or using UV...
pip install -e ".[dev]"     # For development...

Setting Up Environment#

  1. Install required libraries:

pip install --upgrade pip
pip install virtualenv

  1. Create Python virtual environment:

virtualenv --python=python3.12 .venv

  1. Activate the virtual environment:

source .venv/bin/activate

Install packages#

pip install .
pip install -e ".[dev]"

Check tests and coverage#

python manager.py run-tests
python manager.py run-coverage

Contributing#

Contributions are welcome! Please:

  1. Fork the repository

  2. Create a feature branch

  3. Write tests for new functionality

  4. Ensure all tests pass: python manager.py run-tests

  5. Run linting: pylint core_db

  6. Run security checks: bandit -r core_db

  7. Submit a pull request

License#

This project is licensed under the MIT License. See the LICENSE file for details.

Support#

For questions or support, please open an issue on GitLab or contact the maintainers.

Authors#