Insert column based on related column value

1

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?

python sqlalchemy flask-sqlalchemy

share|improve this question

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a before_insert event).
  
  – SuperShoot
  Nov 22 '18 at 0:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  A subquery can achieve it on insert: INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;, but I don't know how to achieve that using ORM and am curious to see the answer!
  
  – SuperShoot
  Nov 22 '18 at 0:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.
  
  – Mark Meyer
  Nov 22 '18 at 5:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SuperShoot __init__ could just set unusedCapacity to a scalar subquery that uses the passed room_id and attendance, a bit like @tooTired did it, but without the fetch. For some reason a context sensitive default did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P).
  
  – Ilja Everilä
  Nov 22 '18 at 5:58
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)
  
  – SuperShoot
  Nov 22 '18 at 6:11
  

  
  
  
  

 | 
show 2 more comments

1

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?

python sqlalchemy flask-sqlalchemy

share|improve this question

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a before_insert event).
  
  – SuperShoot
  Nov 22 '18 at 0:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  A subquery can achieve it on insert: INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;, but I don't know how to achieve that using ORM and am curious to see the answer!
  
  – SuperShoot
  Nov 22 '18 at 0:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.
  
  – Mark Meyer
  Nov 22 '18 at 5:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SuperShoot __init__ could just set unusedCapacity to a scalar subquery that uses the passed room_id and attendance, a bit like @tooTired did it, but without the fetch. For some reason a context sensitive default did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P).
  
  – Ilja Everilä
  Nov 22 '18 at 5:58
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)
  
  – SuperShoot
  Nov 22 '18 at 6:11
  

  
  
  
  

 | 
show 2 more comments

1

1

1

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?

python sqlalchemy flask-sqlalchemy

share|improve this question

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

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?

python sqlalchemy flask-sqlalchemy

python sqlalchemy flask-sqlalchemy

share|improve this question

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

share|improve this question

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

share|improve this question

share|improve this question

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

asked Nov 21 '18 at 22:11

Mark MeyerMark Meyer

38.6k33159

38.6k33159

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a before_insert event).
  
  – SuperShoot
  Nov 22 '18 at 0:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  A subquery can achieve it on insert: INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;, but I don't know how to achieve that using ORM and am curious to see the answer!
  
  – SuperShoot
  Nov 22 '18 at 0:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.
  
  – Mark Meyer
  Nov 22 '18 at 5:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SuperShoot __init__ could just set unusedCapacity to a scalar subquery that uses the passed room_id and attendance, a bit like @tooTired did it, but without the fetch. For some reason a context sensitive default did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P).
  
  – Ilja Everilä
  Nov 22 '18 at 5:58
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)
  
  – SuperShoot
  Nov 22 '18 at 6:11
  

  
  
  
  

 | 
show 2 more comments

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a before_insert event).
  
  – SuperShoot
  Nov 22 '18 at 0:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  A subquery can achieve it on insert: INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;, but I don't know how to achieve that using ORM and am curious to see the answer!
  
  – SuperShoot
  Nov 22 '18 at 0:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.
  
  – Mark Meyer
  Nov 22 '18 at 5:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SuperShoot __init__ could just set unusedCapacity to a scalar subquery that uses the passed room_id and attendance, a bit like @tooTired did it, but without the fetch. For some reason a context sensitive default did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P).
  
  – Ilja Everilä
  Nov 22 '18 at 5:58
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)
  
  – SuperShoot
  Nov 22 '18 at 6:11
  

  
  
  
  

1

1

If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a before_insert event).

– SuperShoot
Nov 22 '18 at 0:34

If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a before_insert event).

– SuperShoot
Nov 22 '18 at 0:34

A subquery can achieve it on insert: INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;, but I don't know how to achieve that using ORM and am curious to see the answer!

– SuperShoot
Nov 22 '18 at 0:36

A subquery can achieve it on insert: INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;, but I don't know how to achieve that using ORM and am curious to see the answer!

– SuperShoot
Nov 22 '18 at 0:36

Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

– Mark Meyer
Nov 22 '18 at 5:38

Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

– Mark Meyer
Nov 22 '18 at 5:38

1

1

@SuperShoot __init__ could just set unusedCapacity to a scalar subquery that uses the passed room_id and attendance, a bit like @tooTired did it, but without the fetch. For some reason a context sensitive default did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P).

– Ilja Everilä
Nov 22 '18 at 5:58

@SuperShoot __init__ could just set unusedCapacity to a scalar subquery that uses the passed room_id and attendance, a bit like @tooTired did it, but without the fetch. For some reason a context sensitive default did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P).

– Ilja Everilä
Nov 22 '18 at 5:58

Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

– SuperShoot
Nov 22 '18 at 6:11

Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

– SuperShoot
Nov 22 '18 at 6:11

 | 
show 2 more comments

2 Answers
2

active

oldest

votes

2

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.

share|improve this answer

edited Nov 22 '18 at 12:16

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.
  
  – SuperShoot
  Nov 22 '18 at 12:24
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.
  
  – Ilja Everilä
  Nov 22 '18 at 12:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D
  
  – Ilja Everilä
  Nov 22 '18 at 12:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will have a read - I appreciate you going to the trouble! Cheers.
  
  – SuperShoot
  Nov 22 '18 at 13:01
  

  
  
  
  

add a comment | 

1

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)

share|improve this answer

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is really just the same solution though, it just does the Room query in a different place.
  
  – SuperShoot
  Nov 21 '18 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.
  
  – tooTired
  Nov 22 '18 at 0:04
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421156%2finsert-column-based-on-related-column-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

2

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.

share|improve this answer

edited Nov 22 '18 at 12:16

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.
  
  – SuperShoot
  Nov 22 '18 at 12:24
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.
  
  – Ilja Everilä
  Nov 22 '18 at 12:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D
  
  – Ilja Everilä
  Nov 22 '18 at 12:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will have a read - I appreciate you going to the trouble! Cheers.
  
  – SuperShoot
  Nov 22 '18 at 13:01
  

  
  
  
  

add a comment | 

2

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.

share|improve this answer

edited Nov 22 '18 at 12:16

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.
  
  – SuperShoot
  Nov 22 '18 at 12:24
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.
  
  – Ilja Everilä
  Nov 22 '18 at 12:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D
  
  – Ilja Everilä
  Nov 22 '18 at 12:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will have a read - I appreciate you going to the trouble! Cheers.
  
  – SuperShoot
  Nov 22 '18 at 13:01
  

  
  
  
  

add a comment | 

2

2

2

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.

share|improve this answer

edited Nov 22 '18 at 12:16

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

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.

share|improve this answer

edited Nov 22 '18 at 12:16

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

share|improve this answer

share|improve this answer

edited Nov 22 '18 at 12:16

edited Nov 22 '18 at 12:16

edited Nov 22 '18 at 12:16

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

answered Nov 22 '18 at 6:13

Ilja EveriläIlja Everilä

24.5k33763

24.5k33763

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.
  
  – SuperShoot
  Nov 22 '18 at 12:24
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.
  
  – Ilja Everilä
  Nov 22 '18 at 12:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D
  
  – Ilja Everilä
  Nov 22 '18 at 12:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will have a read - I appreciate you going to the trouble! Cheers.
  
  – SuperShoot
  Nov 22 '18 at 13:01
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.
  
  – SuperShoot
  Nov 22 '18 at 12:24
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.
  
  – Ilja Everilä
  Nov 22 '18 at 12:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D
  
  – Ilja Everilä
  Nov 22 '18 at 12:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will have a read - I appreciate you going to the trouble! Cheers.
  
  – SuperShoot
  Nov 22 '18 at 13:01
  

  
  
  
  

WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.

– SuperShoot
Nov 22 '18 at 12:24

WRT the as_scalar() method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand.

– SuperShoot
Nov 22 '18 at 12:24

1

1

I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.

– Ilja Everilä
Nov 22 '18 at 12:36

I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that as_scalar() or label() is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery.

– Ilja Everilä
Nov 22 '18 at 12:36

And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D

– Ilja Everilä
Nov 22 '18 at 12:43

And here it is, or at least one of them: stackoverflow.com/a/6325582/2681632. From '11, no less :D

– Ilja Everilä
Nov 22 '18 at 12:43

Will have a read - I appreciate you going to the trouble! Cheers.

– SuperShoot
Nov 22 '18 at 13:01

Will have a read - I appreciate you going to the trouble! Cheers.

– SuperShoot
Nov 22 '18 at 13:01

add a comment | 

1

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)

share|improve this answer

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is really just the same solution though, it just does the Room query in a different place.
  
  – SuperShoot
  Nov 21 '18 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.
  
  – tooTired
  Nov 22 '18 at 0:04
  
  
  

  
  
  
  

add a comment | 

1

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)

share|improve this answer

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is really just the same solution though, it just does the Room query in a different place.
  
  – SuperShoot
  Nov 21 '18 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.
  
  – tooTired
  Nov 22 '18 at 0:04
  
  
  

  
  
  
  

add a comment | 

1

1

1

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)

share|improve this answer

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

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)

share|improve this answer

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

share|improve this answer

share|improve this answer

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

answered Nov 21 '18 at 23:45

tooTiredtooTired

1697

1697

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is really just the same solution though, it just does the Room query in a different place.
  
  – SuperShoot
  Nov 21 '18 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.
  
  – tooTired
  Nov 22 '18 at 0:04
  
  
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is really just the same solution though, it just does the Room query in a different place.
  
  – SuperShoot
  Nov 21 '18 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.
  
  – tooTired
  Nov 22 '18 at 0:04
  
  
  

  
  
  
  

This is really just the same solution though, it just does the Room query in a different place.

– SuperShoot
Nov 21 '18 at 23:54

This is really just the same solution though, it just does the Room query in a different place.

– SuperShoot
Nov 21 '18 at 23:54

@SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.

– tooTired
Nov 22 '18 at 0:04

@SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created.

– tooTired
Nov 22 '18 at 0:04

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421156%2finsert-column-based-on-related-column-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here