Decorator to help with legacy database that aren't 1NF

substrcomparatormaker.py

def substrcomparatormaker(column, i, j=None):
    """Return a comparator that tests for the substring of 'column'
    from 'i' to 'j', as specified with Python slice values.  This
    means setting the start column to i + 1 because Python is 0-based
    and SQL is 1-based, and setting the length to j - i.

    Please don't create new tables that use this! It's only for legacy,
    unnormalized data where multiple values are lumped together in a single
    column.

    For example:

    class Invoice(Base):
        uglyaddressstring = Column(String(64))

        # ...

        @comparable_using(substrcomparatormaker(uglyaddressstring, 0, 2))
        @property
        def state(self):
            return self.uglyaddressstring[0:2]

    Find all invoices from customers in Montana:

        session.query(Invoice).filter_by(state='MT')

    Invoices from customers in Montana and Hawaii:

        session.query(Invoice).filter(Invoice.state.in_(('MT', 'HI')))
    """

    class SubstrComparator(ColumnProperty.Comparator):
        """Subclass of Comparator that looks at a slice of a column"""

        if j is None:
            columnsubstr = func.substr(column, i + 1)
        else:
            columnsubstr = func.substr(column, i + 1, j - i)

        def __eq__(self, other):
            """Compare the substring to the given value"""
            return self.columnsubstr == other

        def in_(self, other):
            """Look for the substring in the given list of values"""
            return self.columnsubstr.in_(other)

    return SubstrComparator

The point behind this is that I have to work with some legacy tables where multiple values are stored in single columns. For instance, we have a "customer" column that stores several properties concatenated together into a single string. With this decorator, I can say "bytes[0:3] are field 'foo', and bytes[3:6] are field 'bar'", then can do queries on "table.foo" and "table.bar" as though they were their own separate columns.

Bonus points to anyone who figures out how to roll the property decorator into substrcomparatormaker so that methods don't end up with nested decorators.

P.S. I considered writing a function that also returned a lambda that handled the return self.value[i:j] pattern, but ended up with so many methods that needed more advanced logic (calling .upper(), etc.) that I decided not to.

P.P.S. Please forgive me. I know it's an abomination, but it had to be done. Maybe you can also use this monstrosity.