ProgressivePostgres

ProgressivePostgres is a Python client library for interacting with a TimescaleDB (or PostgreSQL) instance, configured via environment variables. It integrates seamlessly with the Zeitgleich library for improved time-series handling and includes optional features for bridging MQTT data ingestion through SwampClient.

ProgressivePostgres provides:

• Time-Series Data Models — Integration with TimeSeriesData and MultiOriginTimeSeries.
• Environment-Based Configuration — Minimizes boilerplate; simply use a .env file.
• Automatic Table Creation — Optionally create hypertables for your data if they do not already exist.
• Extra Columns Handling — Decide how to manage columns beyond the expected set: ignore, error, or append.
• Asynchronous MQTT Integration — If combined with an MQTT client, seamlessly push sensor data into TimescaleDB.

Table of Contents

• Features
• Installation
• Configuration
  • Environment Variables
• Usage & Examples
• TODOs
• License

Features

• Simple DB Client: Quickly execute queries, insert data, and retrieve time-series rows.
• Timestamp Normalization: TimeSeriesData can parse and convert timestamps among multiple formats (ISO, RFC3339, UNIX, etc.).
• Automatic Hypertable Creation: Create TimescaleDB hypertables on-the-fly if desired.
• Multi-Origin Data Model: Use MultiOriginTimeSeries for simultaneously managing data from multiple sensors or devices.
• Optional MQTT Bridge: Combine with an MQTT client for real-time sensor data ingestion.

Installation

Install ProgressivePostgres (and any needed dependencies) via:

pip install ProgressivePostgres

or clone the repository locally with the provided Makefile:

make install

Configuration

ProgressivePostgres uses environment variables for configuration, read from a prefix that you pass to the Client(name="TS") constructor.

Environment Variables

Usage & Examples

To see working code samples, please refer to the examples directory in this repository. Highlights include:

• Basic Example: Demonstrates how to connect to a local TimescaleDB instance, run simple queries, and handle .env environment variables.
• MQTT Logger Example: Combines ProgressivePostgres with an MQTT client, pushing messages from topics into TimescaleDB.
• Zeitgleich Example: Showcases using TimeSeriesData and MultiOriginTimeSeries for multi-sensor data insertion and retrieval.

Example .env

A typical .env file might look like:

TS_DB_HOST="localhost"
TS_DB_PORT="5444"
TS_DB_NAME="timeseries"
TS_DB_USER="postgres"
TS_DB_PASS="pwd"
TS_LOG_LEVEL="DEBUG"
TS_ORIGIN_SPLIT_CHAR="/"
TS_ORIGIN_JOIN_CHAR="_"
TS_TIMESTAMP_COLUMN="timestamp"
TS_VALUE_COLUMN="value"
TS_EXTRA_COLUMNS_HANDLING="append"
TS_CREATE_TABLES_IF_NOT_EXIST="true"

Load these environment variables in your Python script using python-dotenv:

from dotenv import load_dotenv

load_dotenv()

Then instantiate a client:

from ProgressivePostgres import Client

client = Client(name="TS")  # "TS" will be the prefix for env variables
# ...

TODOs

• Automatic Migrations: Provide tools to manage schema migrations automatically.
• Advanced Query Builder: Add an optional query builder for more complex queries (joins, filters, etc.).
• Transaction Handling: More robust transaction management (automatic rollback on certain errors).
• Comprehensive Testing: Add unit and integration tests across various DB versions.
• Enhanced MQTT Integration: Provide additional examples.

License

Licensed under the MIT License.