Process fields in SQLAlchemy model (using flask_sqlalchemy)

Question

I am using SQLAlchemy through flask_sqlalchemy. A model receives input from HTML forms. I would like this input to be stripped of any tags. Instead of doing this several times in the code before assignment, I thought it might be better to implement this somehow in the model object.

The possibilities I could think of were:

• Derive own column types
• Wrap a proxy class around the column types
• Define kind of a decorator that does the above
• Modify the model object to intercept assignments

The first three solutions seem more elegant, but I don't understand how I need to implement these. The main reason is that I don't understand how exactly SQLAlchemy extracts the table structure and column types from the column variables, and how assignment to these is handled, in particular when access through the flask_sqlalchemy class.

I played around with the last option in the list above, and came up with this (partial) solution:

import bleach

class Example(db.Model):
    __tablename__ = 'examples'
    id = db.Column(db.Integer, primary_key=True)
    field1 = db.Column(db.Text)
    field2 = db.Column(db.String(64))

    _bleach_columns = ('field1', 'field2')

    def __init__(self, **kwargs):
        if kwargs is not None:
            for key in Example._bleach_columns:
                kwargs[key] = bleach.clean(kwargs[key], tags=[], strip=True)
        super(Example, self).__init__(**kwargs)

This works when creating objects using Example(field1='foo', field2='bar'). However, I am uncertain how to handle the assignment of individual fields. I was thinking of something along these lines, but am unsure about the parts marked as ASSIGN:

    def __setattr__(self, attr, obj):
        if(attr in Example._bleach_columns):
            ASSIGN(..... , bleach.clean(obj, tags=[], strip=True))
        else:  
            ASSIGN(..... , obj)

More generally, my impression is that this is not the best way to handle tag filtering. I'd therefore appreciate any hint on how to best implement this behaviour, ideally with a decorator of new column types.

It looks like this could be done with a TypeDecorator (link) that applies bleach in process_bind_param. However, I could not figure out how to apply this decorator to the flask_sqlalchemy based column definition in the db.Model-derived class above.

Answer 1

I finally managed to solve this... which was easy, as usual, once one understands what it all is about.

The first thing was to understand that db.Column is the same than SQLAlchemy's column. I thus could use the same syntax. To implement variable length strings, I used a class factory to return the decorators. If there is another solution to implement the length, I'd be interested to hear about it. Anyway, here is the code:

def bleachedStringFactory(len):

    class customBleachedString(types.TypeDecorator):

        impl = types.String(len)

        def process_bind_param(self, value, dialect):
            return bleach.clean(value, tags=[], strip=True)

        def process_result_value(self, value, dialect):
            return value

    return customBleachedString


class Example(db.Model):
    __tablename__ = 'examples'
    id = db.Column(db.Integer, primary_key=True)
    field1 = db.Column(bleachedStringFactory(64), unique=True)
    field2 = db.Column(bleachedStringFactory(128))