pyconnpg 1.5.0
A simplified Python library for PostgreSQL CRUD operations.
Requires Python: >=3.10
Keywords: postgresql, database, connection

# PostgreSQL Connection Helper

A lightweight Python utility class for connecting to PostgreSQL databases, executing CRUD operations, and performing common table actions (insert, update, delete, add column, etc.).
Supports environment variable configuration.



## 🚀 Features:

* Connect easily to PostgreSQL using direct arguments or environment variables

* Insert single or multiple rows

* Batch inserts using execute_values for high performance

* Conditional inserts with conflict checks (ON CONFLICT DO NOTHING)

* Table maintenance utilities (add columns, update rows, delete rows)

* Check for record existence (check_exists, check_exists_long)

* Fetch specific column values based on conditions

* Clean and explicit API for common database actions


## ⚙️ Usage
1. Basic Connection
    ```python
    import pyconnpg 

    db = pyconnpg.Connect(
        host='hostname or IP',
        port=5432,
        db='mydatabase',
        user='myuser',
        password='mypassword',
    )

    # Or using context manager which automatically closes the connection.
    with pyconnpg.Connect(
        host='hostname or IP',
        port=5432,
        db='mydatabase',
        user='myuser',
        password='mypassword',
    ) as db:
        print(db.pg_version)
        

    ```

2. Using Environment Variables

    You can define these variables in your shell or home directory .pgenv file or load your env file using dotenv:
    | Variable    | Description                 |
    | ----------- | --------------------------- |
    | `PG_HOST`   | PostgreSQL host             |
    | `PG_PORT`   | PostgreSQL port             |
    | `PG_DB`     | Database name               |
    | `PG_USER`   | Database username           |
    | `PG_PASSWD` | Database password |


    Then, simply initialize:
    ```python
    import pyconnpg
    db = pyconnpg.Connect()
    ```
    You need to specify the schema and table name when performing read/write operations like this:
    ```python
    db = pyconnpg.Connect(schema='schema_name', table='table_name')
    ```

## 🧪 Example Workflow
### 1. `check_exists(column_name: str, find) -> bool` 
Checks if a record exists in the table.
```python
# Example
exists = db.check_exists(column_name='user_id', find='U001')
print(exists)  # True or False
```
### 2. `check_exists_long(where: str) -> bool` 
Checks record existence using complex WHERE clauses.
```python
# Example
exists = db.check_exists_long("user_id='U001' AND status='active'")
```

### 3. `insert(**kwargs)` 
Inserts a single row into the connected table.
```python
# Example
db.insert(product='Widget', version=3)
```

### 4. `batch_insert(data: list[dict]) -> bool`
Batch insert using efficient execute_values.
```python
# Example
records = [
    {"id": "2025-10-21_1", "date": "2025-10-21", "users": "user1", "usage": 3},
    {"id": "2025-10-21_2", "date": "2025-10-21", "users": "user2", "usage": 5}
]
db.batch_insert(records)
```

### 5. `insert_many(data: list[dict]) -> bool`
Batch insert using standard executemany.
```python
# Example
records = [
    {"id": "2025-10-21_1", "date": "2025-10-21", "users": "user1", "usage": 3},
    {"id": "2025-10-21_2", "date": "2025-10-21", "users": "user2", "usage": 5}
]
db.insert_many(records)
```

### 6. `insert_with_conflict_check(check_conflict, **kwargs)`
Inserts a record but ignores duplicates on conflict.\
`check_conflict`: column name of the primary key or unique constraint.
```python
# Example
db.insert_with_conflict_check(
    check_conflict='id',
    product='Gizmo',
    version=2
)
```

### 7. `update_row(column_name: str, value, where: str)`
Updates a row matching a condition.
```python
# Example
db.update_row(column_name='version', value=4, where="id='2025-10-21_1'")
```

### 8. `add_column(column_name: str, col_type: str)`
Adds a new column if it does not exist.
```python
# Example
db.add_column(column_name='last_login', col_type='timestamp')
```

### 9. `get_value_match(column_name: str, where_column: str, match: str) -> str`
Fetches a specific value when a condition matches.
```python
# Example
email = db.get_value_match('email', 'username', 'john_doe')
print(email)
```
### 10. `delete_row(condition: str)`
Deletes rows that satisfy the given condition.
```python
# Example
db.delete_row("user_id='U001'")
```

### 11. `close() -> None`
Closes the PostgreSQL connection.

```python
# Example
db.close()
```

### 12. `table_exists(schema_name: str, table_name: str) -> bool`
Checks if table exists under the schema.
```python
# Example
is_exists = db.table_exists(schema_name="public", table_name="users")
```


## 🧱 Requirements

* Python ≥ 3.10+
* PostgreSQL ≥ 13
* psycopg2
* cryptography
* dotenv