Dynamically generated field in SqlAlchemy model with Postgres

Question

I want to create a table with a column for each hour of the day of Float type. How do I get rid of this verbose syntax:

from app import db

class HourlySchedule(db.Model):
    id = db.Column(
        db.Integer,
        primary_key=True
    )

    h0 = db.Column(db.Float, nullable=True)
    h1 = db.Column(db.Float, nullable=True)
    h2 = db.Column(db.Float, nullable=True)
    h3 = db.Column(db.Float, nullable=True)
    h4 = db.Column(db.Float, nullable=True)
    h5 = db.Column(db.Float, nullable=True)
    h6 = db.Column(db.Float, nullable=True)
    h7 = db.Column(db.Float, nullable=True)
    h8 = db.Column(db.Float, nullable=True)
    h9 = db.Column(db.Float, nullable=True)
    h10 = db.Column(db.Float, nullable=True)
    h11 = db.Column(db.Float, nullable=True)
    h12 = db.Column(db.Float, nullable=True)
    h13 = db.Column(db.Float, nullable=True)
    h14 = db.Column(db.Float, nullable=True)
    h15 = db.Column(db.Float, nullable=True)
    h16 = db.Column(db.Float, nullable=True)
    h17 = db.Column(db.Float, nullable=True)
    h18 = db.Column(db.Float, nullable=True)
    h19 = db.Column(db.Float, nullable=True)
    h20 = db.Column(db.Float, nullable=True)
    h21 = db.Column(db.Float, nullable=True)
    h22 = db.Column(db.Float, nullable=True)
    h23 = db.Column(db.Float, nullable=True)

Another question is how do I enforce checks on the values (e.g. 0 <= value <=1)?

As validation? Then how do i set validation neatly for 24 fields?

Can I instead add a check constraint with SqlAlchemy?

If you really want to have 24 columns in your table, there is nothing wrong with the verbose approach. You can write a validator for limits checking. — Selcuk
– Selcuk, Commented Jul 24, 2016 at 10:50
Then I will post the question "Dynamically generated validators in SqlAlchemy" — Alain1405
– Alain1405, Commented Jul 24, 2016 at 12:53

univerio · Accepted Answer · 2016-07-25 19:26:02Z

3

The key here is to realize that a class block is just a block of code, so you can put loops in there:

class HourlySchedule(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    for i in range(24):
        locals()["h{}".format(i)] = db.Column(db.Float)

    @validates(*("h{}".format(i) for i in range(24)))
    def _validate(self, k, h):
        assert 0 <= h <= 1
        return h

answered Jul 25, 2016 at 19:26

univerio

20.6k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mattew Whitt · Accepted Answer · 2016-07-24 14:49:16Z

You may need to play around with the order of calling super().__init__(*args, **kwargs), but this should theoretically work.

As for validation, the good thing about the validates decorator is that it takes multiple column names, so we can achieve dynamic field creation and validation like so:

from app import db
from sqlalchemy.orm import validates


class HourlySchedule(db.Model):
    id = db.Column(
        db.Integer,
        primary_key=True
    )

    def __init__(self, *args, **kwargs):
        self.colstrings = []

        for hour in range(0, 24):
            colstring = "h{}".format(hour)
            setattr(self, colstring, db.Column(db.Float, nullable=True))
            self.colstrings.append(colstring)

        super().__init__(*args, **kwargs)

    @validates(*self.colstrings)
    def validate_hours(self, key, hour): 
        assert 0 < hour < 1
        return hour

One thing I'd like to note, however, is that this is actually greatly increases the complexity of a rather simple concept. Instead of hiding model details, which are meant to be verbose so devs can easily understand model > table mappings, it might make more sense to either list out every column, or to rethink how you're structuring your data.

Collectives™ on Stack Overflow

Dynamically generated field in SqlAlchemy model with Postgres

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related