Skip to main content

light PosgreSQL ORM with JSON model config

Project description

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. Following example shows how to setup simple Product model module:

product.py

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:

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:

import product
print(product.add(data))

This will result following query execution:

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"

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:

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"

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

The following query will be generated and executed:

SELECT
    product.id, product.name, product.price, product.category_id,
    category.id, category.name
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

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

pgsql-table-0.3.8.tar.gz (8.3 kB view hashes)

Uploaded Source

Built Distribution

pgsql_table-0.3.8-py2.py3-none-any.whl (8.8 kB view hashes)

Uploaded Python 2 Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page