CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Mark
Mark

Reputation: 92461

Insert column based on related column value

This seems like a real beginner question, but I'm having trouble finding a simple answer. I have simplified this down to just the bare bones with a simple data model representing a one-to-many relationship:

class Room(db.Model):
    __tablename__ = 'rooms'
    id        = db.Column(db.Integer, primary_key=True)
    name      = db.Column(db.String(128), unique=True)
    capacity  = db.Column(db.Integer)
    events    = db.relationship('Event', backref='room')


class Event(db.Model):
    __tablename__ = 'counts'
    id               = db.Column(db.Integer, primary_key=True)
    unusedCapacity   = db.Column(db.Integer)
    attendance       = db.Column(db.Integer)
    room_id          = db.Column(db.Integer, db.ForeignKey('rooms.id'))

Event.unusedCapacity is calculated as Room.capacity - Event.attendance, but I need to store the value in the column — Room.capacity may change over time, but the Event.unusedCapacity needs to reflect the actual unused capacity at the time of the Event.

I am currently querying the Room and then creating the event:

room = Room.query.get(room_id) # using Flask sqlAlchemy
event = event(unusedCapacity = room.capacity - attendance, ...etc)

My question is: is there a more efficient way to do this in one step?

Upvotes: 1

Views: 143

Answers (2)

Ilja Everilä
Ilja Everilä

Reputation: 52997

As noted in the comments by @SuperShoot, a query on insert can calculate the unused capacity in the database without having to fetch first. An explicit constructor, such as shown by @tooTired, could pass a scalar subquery as unusedCapacity:

class Event(db.Model):
    ...
    def __init__(self, **kwgs):
        if 'unusedCapacity' not in kwgs:
            kwgs['unusedCapacity'] = \
                db.select([Room.capacity - kwgs['attendance']]).\
                where(Room.id == kwgs['room_id']).\
                as_scalar()
        super().__init__(**kwgs)

Though it is possible to use client-invoked SQL expressions as defaults, I'm not sure how one could refer to the values to be inserted in the expression without using a context-sensitive default function, but that did not quite work out: the scalar subquery was not inlined and SQLAlchemy tried to pass it using placeholders instead.

A downside of the __init__ approach is that you cannot perform bulk inserts that would handle unused capacity using the table created for the model as is, but will have to perform a manual query that does the same.

Another thing to look out for is that until a flush takes place the unusedCapacity attribute of a new Event object holds the SQL expression object, not the actual value. The solution by @tooTired is more transparent in this regard, since a new Event object will hold the numeric value of unused capacity from the get go.

Upvotes: 2

tooTired
tooTired

Reputation: 179

SQLAlchemy adds an implicit constructor to all model classes which accepts keyword arguments for all its columns and relationships. You can override this and pass the kwargs without unusedCapacity and get the room capacity in the constructor:

class Event(db.Model):
    # ...
    #kwargs without unusedCapacity
    def __init__(**kwargs):
        room = Room.query.get(kwargs.get(room_id))
        super(Event, self).__init__(unusedCapacity = room.capacity - kwargs.get(attendance), **kwargs)


#Create new event normally
event = Event(id = 1, attendance = 1, room_id = 1)

Upvotes: 1

Related Questions