CODEWITHSUNDEEP
pythonflasksqlalchemydata-modeling
Alain1405
Alain1405

Reputation: 2955

Dynamically generated field in SqlAlchemy model with Postgres

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?

Upvotes: 1

Views: 835

Answers (2)

univerio
univerio

Reputation: 20518

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

Upvotes: 3

Mattew Whitt
Mattew Whitt

Reputation: 2424

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.

Upvotes: -1

Related Questions