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
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for pgsql_table-0.3.7-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | f5f93e37429f12b40619b8aedba08f2d550abca08c25d3ca98cf3922889c693e |
|
MD5 | c175f467a4669c0d94ead7b29815e6fe |
|
BLAKE2b-256 | c745ce01a8de4217859c76adf4b49177fe7eb12e8b62b9267dc1cd37e0ed5eb7 |