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?
python sqlalchemy flask-sqlalchemy
|
show 2 more comments
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
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 abefore_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 setunusedCapacity
to a scalar subquery that uses the passedroom_id
andattendance
, 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
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
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
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 abefore_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 setunusedCapacity
to a scalar subquery that uses the passedroom_id
andattendance
, 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 abefore_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 setunusedCapacity
to a scalar subquery that uses the passedroom_id
andattendance
, 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
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.
WRT theas_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 thatas_scalar()
orlabel()
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 |
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)
This is really just the same solution though, it just does theRoom
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
});
}
});
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
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
WRT theas_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 thatas_scalar()
orlabel()
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 |
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.
WRT theas_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 thatas_scalar()
orlabel()
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 |
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.
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.
edited Nov 22 '18 at 12:16
answered Nov 22 '18 at 6:13
Ilja EveriläIlja Everilä
24.5k33763
24.5k33763
WRT theas_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 thatas_scalar()
orlabel()
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 theas_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 thatas_scalar()
orlabel()
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 |
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)
This is really just the same solution though, it just does theRoom
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 |
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)
This is really just the same solution though, it just does theRoom
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 |
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)
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)
answered Nov 21 '18 at 23:45
tooTiredtooTired
1697
1697
This is really just the same solution though, it just does theRoom
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 theRoom
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.
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
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
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
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
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
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 setunusedCapacity
to a scalar subquery that uses the passedroom_id
andattendance
, 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