Metadata-Version: 2.4
Name: pyposconnector
Version: 0.1.9
Summary: PostgreSQL Connector with TimescaleDB and pgvector support for Python 3.12+
License-File: LICENSE
Author: MinhSonCQF
Author-email: nguyen.minhson1511@gmail.com
Requires-Python: >=3.12,<4.0
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Requires-Dist: loguru (>=0.7.3,<0.8.0)
Requires-Dist: pandas (>=2.3.3,<3.0.0)
Requires-Dist: pgvector (>=0.4.2,<0.5.0)
Requires-Dist: psycopg2-binary (>=2.9.11,<3.0.0)
Requires-Dist: sqlalchemy (>=2.0.48,<3.0.0)
Project-URL: Homepage, https://github.com/johnnyb1509/PostgreSQLConnector
Description-Content-Type: text/markdown

# 🚀 PostgresConnector

## 📖 Introduction
Welcome to **PostgresConnector**, the ultimate database connection package built for our team's data engineering and AI workflows. 

This package simplifies interactions with PostgreSQL databases by automating tedious tasks like schema evolution, data type mapping, and bulk upserts. It goes beyond standard SQL by providing native, out-of-the-box support for **TimescaleDB** (for time-series data) and **pgvector** (for AI embeddings).

## Change logs 2026-06:
### 1. Native NumPy & PyArrow Data Type Translation
When working with heavy data-analysis frameworks like Pandas, data columns are frequently compiled into low-level C-based NumPy primitives (e.g., `numpy.int64`, `numpy.float64`, or optimized Unicode string blocks like `numpy.str_`). Standard PostgreSQL drivers (`psycopg2`) strictly reject these types, throwing cryptic errors such as:
> `ProgrammingError: can't adapt type 'numpy.int64'` or `numpy string dtypes are not allowed`

`PostgresConnector` implements a **Native Python Sanitization Layer** directly at the database boundary inside all write operations (`upsert_data`, `replace_table`, and `delete_and_insert`). It dynamically intercepts incoming records, extracts their pure scalar elements via memory pointer stripping (`.item()`), and flawlessly casts them to standard PostgreSQL types:
* `numpy.integer` / `int64` $\rightarrow$ `BIGINT`
* `numpy.floating` / `float64` $\rightarrow$ `DOUBLE PRECISION`
* `numpy.str_` / Unicode arrays $\rightarrow$ `TEXT`
* `numpy.bool_` $\rightarrow$ `BOOLEAN`

### 2. Strict Schema Separation (Multi-Tenant Routing)
Unlike default drivers that query only the `public` namespace, this connector features explicit multi-schema resolution. When establishing a connection, passing a specific `schema` ensures that tables are isolated dynamically. 

All diagnostic operations—such as `inspect().has_table()` and structural migrations (`_add_missing_columns`)—explicitly target your specified schema, ensuring that **Schema Evolution** functions seamlessly without creating false-positive duplicate tables in the `public` space.

### ✨ Key Features
* **Smart Upsert (`ON CONFLICT DO UPDATE`):** Blazing fast data ingestion with conflict resolution strategies (`last`, `sum`, `skip`).
* **Auto Schema Evolution:** Automatically adds missing columns to your database tables based on your Pandas DataFrames.
* **Native JSONB Support:** Automatically detects nested Python dictionaries/lists and maps them to PostgreSQL `JSONB` format.
* **TimescaleDB Integration:** Easily convert standard tables into hypertables for optimized time-series data storage.
* **pgvector for AI:** Automatically detects lists of floats (embeddings) and creates Vector columns with HNSW/IVFFlat indexing for fast similarity searches.

---

## 📂 Directory Structure

This project is managed using [Poetry](https://python-poetry.org/). The standard structure looks like this:

```text
PostgreSQLConnector/
│
├── pyproject.toml           # Poetry configuration, metadata, and dependencies
├── README.md                # This documentation file
├── src/       # The actual Python module
│   ├── __init__.py
│   └── postgres_connector.py
└── notebooks/               # (Optional) Tutorials and examples
    └── Tutorial.ipynb

```

## 💻 Installation
This package is published on PyPI. You can easily install it into your project using your preferred package manager.

Using Poetry (Recommended):

```bash
poetry add PostgreSQLConnector
```

Using pip:

```bash
pip install PostgreSQLConnector
```

## 🛠️ Dependencies
This package relies on several powerful Python libraries to function properly.

```pandas``` - For data manipulation and structures.

```SQLAlchemy``` - For database connection and ORM capabilities.

```psycopg2-binary``` - The most popular PostgreSQL adapter for Python.

```pgvector``` - For handling vector data types and AI embeddings in SQLAlchemy.

```loguru``` - For beautiful, easy-to-read logging.


## 🚀 Quick Start
Here is a quick example of how to connect and upsert data using the connector:

```python
import pandas as pd
from postgres_connector import PostgresConnector

# 1. Initialize the connection
pg = PostgresConnector(
    host='localhost', 
    database='my_database', 
    username='my_user', 
    password='my_password'
)

# 2. Prepare your data
data = {
    'id': [1, 2],
    'name': ['Alice', 'Bob'],
    'role': ['Admin', 'User']
}
df = pd.DataFrame(data)

# 3. Upsert into the database (Creates table if it doesn't exist!)
pg.upsert_data(
    df=df, 
    target_table='team_members', 
    primary_key='id'
)

# 4. Close the connection
pg.dispose()
```
For more advanced use cases, including **TimescaleDB** and **pgvector** for AI embeddings, please refer to the Tutorial.ipynb file included in this repository.

## 👨‍💻 Creator
Created by: Nguyen Minh Son, CQF (MinhSonCQF)

Contact / Support: nguyen.minhson1511@gmail.com

