ProgressivePostgres 0.0.2

A TimescaleDB client library with environment-based config and time-series data handling.

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 advanced time-series handling and includes optional features for bridging MQTT data ingestion through SwampClient.

Developed at TU Wien, 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

Variable	Description	Default	Options
{PREFIX}_DB_HOST	Hostname for TimescaleDB/PostgreSQL.	localhost
{PREFIX}_DB_PORT	Port for TimescaleDB.	5432
{PREFIX}_DB_NAME	Database name.	timescale
{PREFIX}_DB_USER	Username for DB authentication.	postgres
{PREFIX}_DB_PASS	Password for DB authentication.	None
{PREFIX}_DB_AUTOCOMMIT	Whether to auto-commit each statement.	true	true, false
{PREFIX}_LOG_LEVEL	Log level.	DEBUG	DEBUG, INFO, WARNING, ERROR
{PREFIX}_ORIGIN_SPLIT_CHAR	Char used to split origins (e.g. machine1/sensorA).	/
{PREFIX}_ORIGIN_JOIN_CHAR	Char used to join origin parts for table naming.	/
{PREFIX}_TIMESTAMP_COLUMN	Name of the time column in DB.	timestamp
{PREFIX}_VALUE_COLUMN	Name of the primary value column.	value
{PREFIX}_CREATE_TABLES_IF_NOT_EXIST	Automatically create hypertables if missing.	true	true, false
{PREFIX}_EXTRA_COLUMNS_HANDLING	Handling of extra columns.	append	ignore, error, append

---

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.