Metadata-Version: 2.1
Name: pgsql-table
Version: 0.4.3
Summary: light PosgreSQL ORM with JSON model config
Home-page: https://github.com/hazardland/sql.py
Author: Vakhtang Zardiashvili
Author-email: hazardland@gmail.com
License: MIT
Keywords: orm,pgsql,postgresql,model
Platform: UNKNOWN
Requires-Python: >=3.6
Description-Content-Type: text/markdown
Requires-Dist: python-dateutil

**pgsql-table** is an PostgreSQL ORM which aims to simplify JSON based API implementation process. It allows direct usage of request JSON data securely for inserting updating and selecting.

<!-- MarkdownTOC autolink=true -->

- [Introduction](#introduction)
- [Setup](#setup)
- [Filter](#filter)
- [All](#all)

<!-- /MarkdownTOC -->


# Introduction
Following example shows how to setup simple Product model module:

**product.py**

```python
import sql
import category

class Product:
    def __init__(self, id, name):
        self.id = id
        self.name = name
        self.price = None
        self.category_id = None
        self.category = None

class Table(sql.Table):
    type = Product
    schema = 'site'
    name = 'product'
    fields = {
        'id': {'type':'int', 'insert':False, 'update':False},
        'name': {},
        'price': {'type':'float'},
        'category_id': {'type':'int'}
    }
    joins = {
        'category': {'table':category.Table, 'field':'category_id'}
    }

def add(data):
    return Table.add(data)

def save(id, data):
    return Table.save(id, data)

def get(id):
    return Table.get(id)

def all(filter={}, order={}, search={}):
    return Table.all(filter=filter,
                     order=order,
                     search=search)

def filter(page=1, limit=100, filter={}, order={}, search={}):
    return Table.filter(page=page,
                        limit=limit,
                        filter=filter,
                        order=order,
                        search=search)
```

Let us create our first product. In data we have JSON which came througth the API:
```python
data = {
    'name': 'Plumbus',
    'price': 9.99,
    'category_id': 1
}
```

Function product.add will insert product into products table and also return instance of Product object representing newly created record:
```python
import product
print(product.add(data))
```

This will result following query execution:
```sql
WITH "product" AS (
    INSERT INTO "site"."product" (name, price, category_id)
    VALUES (Plumbus, 9.99, 1) RETURNING product.id, product.name, product.price, product.category_id
)
SELECT product.id, product.name, product.price, product.category_id,category.id, category.name
FROM "product"
LEFT JOIN "site"."category" ON "category"."id"="product"."category_id"
```

# Setup
**pgsql-table** works with PostgreSQL using **psycopg2** connector module. It gets database connection using user defined Table.get_db function and returns using Table.put_db function. By this two function you can implement connection pool where get_db will accuire free connection from pool and put_db will return it back. Here is quick setup of ```config.py``` for **pgsql-table**:

```python
import sys
import os

import psycopg2
from psycopg2 import pool

from dotenv import load_dotenv, find_dotenv
load_dotenv(find_dotenv())

import logging as log
log.basicConfig(level=log.DEBUG)

# Ignore this part
if sys.platform.lower() == "win32":
    os.system('color')
class color():
    black = lambda x: '\033[30m' + str(x)+'\033[0;39m'
    red = lambda x: '\033[31m' + str(x)+'\033[0;39m'
    green = lambda x: '\033[32m' + str(x)+'\033[0;39m'
    yellow = lambda x: '\033[33m' + str(x)+'\033[0;39m'
    blue = lambda x: '\033[34m' + str(x)+'\033[0;39m'
    magenta = lambda x: '\033[35m' + str(x)+'\033[0;39m'
    cyan = lambda x: '\033[36m' + str(x)+'\033[0;39m'
    white = lambda x: '\033[37m' + str(x)+'\033[0;39m'

def get_db(key=None):
    if not hasattr(get_db, 'pool'):
        init_db()
    conn = getattr(get_db, 'pool').getconn(key)
    log.info(color.yellow('Using db connection at address %s'), id(conn))
    return conn

def put_db(conn, key=None):
    log.info(color.yellow('Releasing db connection at address %s'), id(conn))
    getattr(get_db, 'pool').putconn(conn, key=key)


def init_db():
    if hasattr(get_db, 'pool'):
        log.info(color.cyan('Db pool already initialized at address %s'), id(getattr(get_db, 'pool')))
        return
    try:
        setattr(get_db, 'pool', psycopg2.pool.ThreadedConnectionPool(1, 20, os.getenv("DB")))
        log.info(color.cyan('Initialized db'))
    except psycopg2.OperationalError as e:
        log.error(e)
        sys.exit(0)

# Attach db functions to orm
import sql
sql.Table.get_db = get_db
sql.Table.put_db = put_db
```

Last 3 lines renders ORM ready to use. **init_db** creates 20 connection pool to PosgreSQL. It uses .env file to get database connection string from environment variable **DB**. .env file contains ```DB="dbname=gs1 user=postgres password=1234 host=127.0.0.1 port=5432"```

# Filter

```python
product.all(filter={
        'price':{
            'from': 5,
            'to': 1
        },
        'category_id': 1
    },
    search={
        'name': 'plumbus',
        'category':{
            'name': 'plumbus'
        }
    },
    order={
        'field': 'price',
        'method': 'desc'
    }
    page=3,
    limit=50
)
```

The following query will be generated and executed:
```sql
SELECT product.id, product.name, product.price, product.category_id,category.id, category.name, COUNT(*) OVER()
FROM "site"."product"
LEFT JOIN "site"."category" ON "category"."id"="product"."category_id"
WHERE (product."name" ILIKE '%plumbus%'
    OR category."name" ILIKE '%plumbus%')
    AND (product."price">=5.0
    AND product."price"<=1.0
    AND product."category_id"=1)
ORDER BY product."price" DESC
LIMIT 50 OFFSET 100
```
Notice difference between ```filter``` and ```search```: While all criterias in ```filter``` must be matched in order to get record, From ```search``` at least one criteria must be matched. In shorts query looks like this: ```(search1 OR search2 OR search3) AND (filter1 AND filter2 AND filter3)```
.

The result off filter will be an object. ```result.total``` containts count of total items matching criterias. ```result.items``` contains list of item objects which represent ```Product``` class.

The result is also paged by ```limit``` parameter and is fetched for ```page```. ```page=3, limit=50``` results ```LIMIT 50 OFFSET 100 in query```.

# All
product.all() acts like product.filter() but result is simple list and result is not paged.


