dbengine 1.1.0

A Python package providing a unified interface for database operations, supporting Parquet, SQLite, and PostgreSQL backends.

DBEngine

A unified, production-ready database interface for Python that provides seamless access to SQLite, Parquet, and PostgreSQL databases through a consistent pandas-based API.

Features

• Multi-Database Support: Work with SQLite, Parquet files, and PostgreSQL databases using the same API
• Production-Ready: Built-in configuration management and error handling
• Pandas Integration: Native pandas DataFrame/Series support for all operations
• High Performance: Connection pooling, batching, and optimised data handling
• PostgreSQL Server Management: Programmatically start/stop PostgreSQL servers using Docker for development and testing
• Comprehensive Testing: Full test suite with validation and integration tests
• Easy Setup: Simple installation and configuration with sensible defaults

Quick Start

Installation

pip install dbengine

Basic Usage

from dbengine import create_database
import pandas as pd

# Create a SQLite database
db = create_database('sqlite', database='my_data')

# Create sample data
data = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35]
})

# Write data
db.write(table_name='users', item=data)

# Read data
users = db.query(table_name='users')
print(users)

# Query data
young_users = db.query(criteria=[('age', '=', 25)], table_name='users')
print(young_users)

PostgreSQL Server Management

import pandas as pd
from dbengine import create_postgres_server, PostgreSQLDatabase, DatabaseConfig

# Start a PostgreSQL server for development/testing
with create_postgres_server(port=5433) as server:
    # Create database configuration from server connection params
    config = DatabaseConfig(
        database_config={
            "db_type": "postgresql",
            "params": server.get_connection_params()
        }
    )

    # Create database connection
    db = PostgreSQLDatabase(config)

    # Use the database normally
    data = pd.DataFrame({'id': [1, 2], 'name': ['Alice', 'Bob']})
    db.write(table_name='users', item=data)

    # Database and server automatically cleaned up

Supported Databases

SQLite

• Use Case: Local development, testing, lightweight applications
• Features: File-based, serverless, ACID transactions
• Configuration: Simple file path specification

Parquet

• Use Case: Data analytics, archival, big data processing
• Features: Columnar storage, compression, fast analytics
• Configuration: Directory path with compression options

PostgreSQL

• Use Case: Production applications, multi-user systems
• Features: Full ACID compliance, connection pooling, advanced SQL
• Configuration: Host, port, credentials, SSL support
• Server Management: Docker-based server lifecycle management for development/testing

Configuration

DBEngine supports configuration files for different environments with YAML format.

Sample Configuration Files

There are sample configuration files in the sample_configs folder.

SQLite Configuration:

database:
  db_type: sqlite
  params:
    path: data/database.db

logging:
  level: INFO
  format: "%(asctime)s - %(name)s - %(levelname)s - %(message)s"
  handlers: null

PostgreSQL Configuration:

database:
  db_type: postgresql
  params:
    host: localhost
    port: 5432
    database: dbengine
    user: dbengine
    password: your_password_here

logging:
  level: INFO
  format: "%(asctime)s - %(name)s - %(levelname)s - %(message)s"
  handlers: null

Parquet Configuration:

database:
  db_type: parquet
  params:
    path: data/parquet

logging:
  level: INFO
  format: "%(asctime)s - %(name)s - %(levelname)s - %(message)s"
  handlers: null

Note: All configuration values must be set in the YAML configuration files.

Creating Configuration Files

DBEngine provides a convenient function to generate sample configuration files for any supported database type:

from dbengine import create_config_file

# Create a SQLite configuration file
create_config_file('config/sqlite_config.yaml', 'sqlite', load_config=True)

# Create a PostgreSQL configuration file
create_config_file('config/postgres_config.yaml', 'postgresql', load_config=False)

# Create a Parquet configuration file
create_config_file('config/parquet_config.yaml', 'parquet', load_config=True)

Function Parameters:

• path: Where to create the configuration file
• db_type: Database type ('sqlite', 'postgresql', or 'parquet')
• load_config: If True, returns a DatabaseConfig object; if False, only creates the file
• overwrite: If True, overwrites existing files; if False, loads existing files when load_config=True

Advanced Usage

PostgreSQL Server Management

DBEngine includes utilities to programmatically start and stop PostgreSQL servers using Docker, making it perfect for development workflows and testing:

import pandas as pd
from dbengine import PostgreSQLServerManager, PostgreSQLDatabase, DatabaseConfig

# Manual server lifecycle management
server = PostgreSQLServerManager(port=5433, database='my_test_db')
server.start()

try:
    # Create database configuration from server connection params
    config = DatabaseConfig(
        database_config={
            "db_type": "postgresql",
            "params": server.get_connection_params()
        }
    )

    # Create database connection
    db = PostgreSQLDatabase(config)

    # Perform database operations
    data = pd.DataFrame({'id': [1, 2, 3], 'value': ['a', 'b', 'c']})
    db.write(table_name='test_table', item=data)

    result = db.query(table_name='test_table')
    print(result)

finally:
    server.stop()

# Context manager (recommended)
with PostgreSQLServerManager(port=5434) as server:
    config = DatabaseConfig(
        database_config={
            "db_type": "postgresql",
            "params": server.get_connection_params()
        }
    )
    db = PostgreSQLDatabase(config)
    # Server automatically stopped when exiting context

Server Management Features:

• Docker Integration: Automatic container lifecycle management
• Port Configuration: Avoid conflicts with existing PostgreSQL instances
• Custom Databases: Create servers with specific database names and credentials
• Health Checks: Automatic server readiness detection
• Multiple Servers: Run multiple isolated PostgreSQL instances simultaneously

Examples

See the notebooks/ directory for comprehensive usage examples.

Testing

Run the comprehensive test suite:

# Run all tests
pytest

# Run with coverage
pytest --cov=src --cov-report=html

Note: PostgreSQL server management tests require Docker to be running. Tests will be automatically skipped if Docker is not available.

Development

Setup Development Environment

git clone https://github.com/tomemgouveia/dbengine.git
cd dbengine
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
pip install -e .

Prerequisites

For PostgreSQL server management features, you'll need:

• Docker: Required for PostgreSQL server management utilities

# Install Docker (macOS)
brew install docker

# Install Docker (Ubuntu/Debian)
sudo apt-get update && sudo apt-get install docker.io

# Start Docker daemon
sudo systemctl start docker  # Linux
# or use Docker Desktop on macOS/Windows

Code Quality

The project uses automated code quality tools:

# Format code
black src/ tests/

# Check imports
isort src/ tests/

# Lint code
flake8 src/ tests/

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Ensure all tests pass
6. Submit a pull request

License

MIT License - see LICENSE file for details.