pgsql-table

light PosgreSQL ORM with JSON model config


Keywords
orm, pgsql, postgresql, model, database-connection, filter, python, sql
License
MIT
Install
pip install pgsql-table==0.1.9

Documentation

Installing

pip install pgsql-table

Importing

import sql

class Foo:
    def __init__(ID=None, name=None):
        self.ID = ID
        self.name = name

class Table(sql.Table)
    type = Foo
    fields = {
        "name": {}
    }

Introduction

Imagine you have to store in database user profile object. You get following JSON as input from API endpoint:

{
    "nickname": "XXX_()_XXX",
    "gender": "female", #Allowed value one from "male", "female", "other"
    "interested_in": ["friendship", "dating"], #Allowed values many from "friendship", "dating", "relationship"
    "height": 170, #Allowed values min 100 max 200 integer
    "birthday": "2001-07-17", #Only valid dates
    "weight": 69.9, #Allowed floats,
    "has_cats": False
}

Also user profile table has 25 other fields and we have another 37 tables. Are we going to list all of them in update queries? And what about selecting, filtering and converting them into objects?

Define class

First of all let us describe our profile object. Every time we select data from user profile we need to return objects of this class:

class Profile:
    def __init__(
            self,
            ID=None,
            nickname=None,
            gender=None,
            interested_in=None,
            birthday=None,
            height=None,
            weight=None,
            has_cats=None
        ):
        self.ID = ID, #Just avoiding "id" as it is builtin function
        self.nickname = nickname
        self.gender = gender
        self.interested_in = interested_in
        self.birthday = birthday
        self.height = height
        self.weight = weight
        self.has_cats = has_cats

Define table

Ok now let us describe table in JSON:

import sql

class Table(sql.Table):
    name = 'user_profile' #Actual table name
    type = Profile #The class we described above
    fields = {
        "ID":{
            "field": "id", #Object property is "ID" but in table column is "id"
            "type": "int",
            "insert": False,
            "update": False
        },
        "nickname": {
        },
        "gender": {
            "options":{
                "male",
                "female",
                "other"
            }
        },
        "interested_in": {
            "options": {
                "dating",
                "friendship",
                "relationship"
            },
            "array": True #This allows to accept multiple values for this field
        },
        "height": {
            "type": "int", #Default was string, only int between range accepted
            "min": 100,
            "max": 200
        },
        "birthday": {
            "type": "date" #Only valid date is accepted
        },
        "weight": {
            "type": "float" #Only castable to float accepted
        },
        "has_cats": {
            "type": "bool"
        }
    }

Create table

That is not all. Now we need to create actual table:

SET search_path TO test;

-- THIS IS LIST OF AVAILABE VALUES FOR THE gender FIELD
CREATE TYPE USER_PROFILE_GENDER AS ENUM
(
    'female',
    'other',
    'male'
);

-- AND THIS IS FOR interedted_in FIELD
CREATE TYPE USER_PROFILE_INTERESTED_IN AS ENUM
(
    'dating',
    'relationship',
    'friendship'
);

CREATE TABLE user_profile
(
    /**
        IN REAL WORLD I WOULD HAVE ONLY FOLLOWING FIELD IN user_profile:
            user_id BIGINT NOT NULL REFERENCES users(id) PRIMARY KEY,
        BUT LET US PRETEND IT IS NOT user_profile TABLE AND HAS ITS OWN id
    **/
    id BIGSERIAL PRIMARY KEY,
    nickname TEXT,
    gender USER_PROFILE_GENDER, -- ONLY AVAILABLE VALUE
    interested_in USER_PROFILE_INTERESTED_IN[], -- SPOT THE [] THIS MEANS MULTIPLE AVAILABE VALUES
    height INT,
    birthday TIMESTAMP WITHOUT TIME ZONE,
    weight TEXT,
    has_cats BOOL
);

Usage

Insert

And finally we can advance to usage, let us create a function which creates a user profile:

#This are just hipotethical functions releasing free db connection from connection pool
from sql import Error #I am not sure about this yet
from config import get_db, put_db

def create(data):
    try:
        insert = Table.insert(data)
    except Exception as error:
        raise error

    try:
        db = get_db()
        cursor = db.cursor()
        cursor.execute("INSERT INTO test.user_profile ("+insert.fields()+") " #List all insert fields
                       "VALUES("+insert.fields('%s')+") " #Instead of insert fields generate '%s' for fields
                       "RETURNING "+Table.select(), #Table.select() lists all fields having select=True (default)
                       insert.values())
        profile = Table.create(cursor.fetchone()) #Create Profile object with what we have inserted
    except Exception as error:
        raise Error('general_error')
    finally:
        db.commit()
        put_db(db)

    return profile

insert.fields() generates nickname, gender, interested_in, height, birthday, weight, has_cats string

insert.fields('%') generates %s, %s, %s, %s, %s, %s, %s

Table.select() generates user_profile.nickname, user_profile.gender, user_profile.interested_in, user_profile.height, user_profile.birthday, user_profile.weight, user_profile.has_cats string

Table.create(cursor.fetchone()) creates object of class Profile and fills in values from table row. As you will see in the usage example insert is returning actual Profile object and all this is done with a single query! (Without writing a single field name)

Using

We should call this function now, pretend the data came from API call:

profile = create({
    "nickname": "XXX_()_XXX",
    #"gender": "female", #We skipped gender
    "interested_in": ["friendship", "dating"],
    "birthday": "2001-07-17",
    "height": 169.99,
    "weight": 69.99,
    "has_cats": False,
    "something": "that_we_dont_have"
    })
print(profile.__dict__)

This will print {'ID': 20, 'nickname': 'XXX_()_XXX', 'gender': None, 'interested_in': ['friendship', 'dating'], 'birthday': datetime.datetime(2001, 7, 17, 0, 0), 'height': 169, 'weight': '69.99', 'has_cats': False}

Error checking

Allowed value checks:

try:
    profile = create({
        "gender": "zebra"
        })
except Error as error:
    print(error.code, error.message)

Prints invalid_value Invalid value zebra for field gender

try:
    profile = create({
        "interested_in": ["being_sober"]
        })
except Error as error:
    print(error.code, error.message)

Prints invalid_value Invalid value being_sober for field gender

Select

Getting by ID

Getting single row is most trivial thing. You do not have to list all select fields by yourself and you do not have to create object from returned row:

def get(ID):
    if not ID:
        raise Error('missing_input')

    try:
        db = get_db()
        cursor = db.cursor()
        cursor.execute("SELECT "+Table.select()+" FROM test.user_profile WHERE id=%s", (ID,))
        profile = Table.create(cursor.fetchone())
    except Exception as error:
        raise Error('general_error')
    finally:
        db.commit()
        put_db(db)

    return profile

The module used for executing queries is psycopg2 which is well known library for working with Postgres in Python. We use parametrized query with a single parameter. One thing I want to note is never forget comma in (ID,) after single parameter or you will kill database!

A little usage of get function:

try:
    profile = get(20)
    print(profile.interested_in)
    #prints ['friendship', 'dating']
except Exception as error:
    # In case nothing found
    print(error)

Getting by filter criterias

This is where lazy people should get happy. Imagine you have to filter and order some table with many columns in every possible way

def get_all(criterias={}, order={}):

    where = Table.where(criterias)
    print(where.clause('1=1'))
    print(where.values())

    total = None
    result = []
    try:
        db = get_db()
        cursor = db.cursor()
        cursor.execute("SELECT "+Table.select()+", COUNT(*) OVER() "
                       "FROM test.user_profile "+
                       where.clause('1=1') + ' ' + # AND something=something
                       Table.order(order, 'ID', 'desc'), #New order, default order field, default order method
                       where.values())

        while True:
            try:
                data = cursor.fetchone()
                if total is None:
                    total = data[Table.offset()]
                profile = Table.create(cursor.fetchone())
                result.append(profile)
            except TypeError:
                return result
    except Exception as error:
        raise Error('general_error')
    finally:
        db.commit()
        put_db(db)

    #return total, result
    return result

This is the usage of get_all with filter criterias

# This section is for only JSON dumping our result
import json
import datetime
def json_dump(value):
    if isinstance(value, datetime.date):
        return str(value)
    elif hasattr(value, 'json') and callable(value.json):
        return value.json()
    return value.__dict__

#This is usage of get_all method
result = get_all({
        "weight":{
            "from": 45,
            "to": 71
        },
        "birthday":{
            "to": "2002-01-01"
        },
        "gender": "female",
        "interested_in": ["friendship", "dating"]
    })

#This is dumping in JSON
print(json.dumps(result, default=json_dump))

#It will print
#[{"ID": [48], "nickname": "XXX_()_XXX", "gender": null, "interested_in": ["friendship", "dating"], "birthday": "2001-07-17 00:00:00", "height": 169, "weight": "69.99", "has_cats": false}, {"ID": [50], "nickname": "XXX_()_XXX", "gender": null, "interested_in": ["friendship", "dating"], "birthday": "2001-07-17 00:00:00", "height": 169, "weight": "69.99", "has_cats": false}]

What does where.clause('1=1') do:

where = Table.where(data)
print(where.clause('1=1')

If data is empty it will output WHERE 1=1. This is useful not to break query if generated where clause is followed by your custom AND my_custom=criteria

If data is not empty, in case of our previous example it will output:

WHERE user_profile."gender"=%s AND %s = ANY(user_profile."interested_in") AND %s = ANY(user_profile."interested_in") AND user_profile."birthday"<=%s AND user_profile."weight">=%s AND user_profile."weight"<=%s

For the same filter criteria data where.values() will output:

['female', 'friendship', 'dating', '2002-01-01 00:00:00', '45.0', '71.0']

As result every %s in generated where clause has its own value.

Filtering options

int, float and date require "field_name":{"from":from_value, "to":to_value}, or at least from or to

Fields with single option value require that value matched one of defined options: "field_name": "allowed_option_value"

Fields with option arrays require that value was array and array items where allowed option values: "field_name":["allowed_value1", "allowed_value2"], even single value requires array to be passed

In case of optionless strings LIKE 'my_value%' expression is used and if we have "field_name":"abc", every value is selected which starts with 'abc'

Every value is casted in field type before using, cast errors also protect while processing unknown input.

Ordering

For order we dedicated second parameter in our get_all function:

result = get_all(order={'birthday','desc'})

In case you do not pass order we have default order criteria specified in our select query as

Table.order(order, 'ID', 'desc')

Where ID is our field name. One of those keys defined in Table.fields (Not actual table field name, in case of id, id is table column name and ID is property name of Profile object)

Update

Creating update function

def save(ID, data):
    try:
        print(data)
        update = Table.update(data)
    except Exception as error:
        raise error

    try:
        db = get_db()
        cursor = db.cursor()
        cursor.execute("UPDATE test.user_profile "
                       "SET "+update.fields()+" "
                       "WHERE id=%s "
                       "RETURNING "+Table.select(),
                       update.values(ID))
        profile = Table.create(cursor.fetchone())
    except Exception as error:
        raise Error('general_error')
    finally:
        db.commit()
        put_db(db)

    return profile

Using update

try:
    profile = save(20, {"gender":"female", "weight":45})
    print(profile.__dict__)
    #{'ID': (20,), 'nickname': 'XXX_()_XXX', 'gender': 'female', 'interested_in': ['friendship', 'dating'], 'birthday': datetime.datetime(2001, 7, 17, 0, 0), 'height': 169, 'weight': '45.0', 'has_cats': False}
except Exception as error:
    # In case nothing updated
    print(error)