How to validate row if it's compliant before pushing it to pgsql?

Question

Say table structure is like:

CREATE TABLE foo (
    id INTEGER  NOT NULL,
    enter_time TIMESTAMP NOT NULL,
    comment TEXT
);   

Now in python, say I get data like this:

foo_user = {"id": 123, "enter_time": None, "comment": ''}

How can I manually validate this data before sending this to pgsql?

Is there any library which already do this by pulling schema information from pgsql and doing validation on that?

Answer 1

The first step is to retrieve the expected data type from the DB itself, this can be accomplished, as suggested by @gaurav) using a:

SELECT column_name, data_type FROM information_schema.columns where ...

This gives to you the type schema, this can be used as 'validation schema'

simple example - diy

Here a simple example limited to check if input data can be typed (it will be casted by postgres in the same manner, maybe)

from datetime import datetime
schema = {"id": "integer", "enter_time": "TIMESTAMP", "comment": 'text'}

def is_valid(v, validator):
    # dummy validator, we try to apply a constructor
    # returns only True/False, 
    # If False... we don't know why!
    # here you can use a RE for check if input is syntactically correct
    try:
        validator(v)
    except:
        return False
    else:
        return True
    


# type to validator
binding = {'integer':int,
           'TIMESTAMP': datetime.fromtimestamp,
           'text':str}

#validation schemaa
val_schema = {k:binding[v] for k,v in schema.items()}



foo_user = {"id": 123, "enter_time": None, "comment": ''}

for k,v in foo_user.items():
    print (k, ':', is_valid(v, val_schema[k]))


# 
# id : True
# enter_time : False
# comment : True

better approach

For the second step, there are specialized validation libraries, they can do typing, clipping, and schema validation (like 2 fields password that must be identical) and a lot of useful stuff.

I've worked a lot with voluptuous but many choices are available up to now, do a good survey before adopt one of that library in your lib stack.