How to increase a counter in SQLAlchemy
Suppose I have table tags which has a field count that indicates how many items have been tagged with the given tag.
How do I increase this counter in SQLAlchemy after I add a new item with an existing tag?
With plain SQL I would do the following:
INSERT INTO `items` VALUES (...)
UPDATE `tags` SET count=count+1 WHERE tag_id=5
But how do I express count=count+1 in SQLAlchemy?
Thanks, Boda Cydo.
python sqlalchemy
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
add a comment |
Suppose I have table tags which has a field count that indicates how many items have been tagged with the given tag.
How do I increase this counter in SQLAlchemy after I add a new item with an existing tag?
With plain SQL I would do the following:
INSERT INTO `items` VALUES (...)
UPDATE `tags` SET count=count+1 WHERE tag_id=5
But how do I express count=count+1 in SQLAlchemy?
Thanks, Boda Cydo.
python sqlalchemy
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
add a comment |
Suppose I have table tags which has a field count that indicates how many items have been tagged with the given tag.
How do I increase this counter in SQLAlchemy after I add a new item with an existing tag?
With plain SQL I would do the following:
INSERT INTO `items` VALUES (...)
UPDATE `tags` SET count=count+1 WHERE tag_id=5
But how do I express count=count+1 in SQLAlchemy?
Thanks, Boda Cydo.
python sqlalchemy
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
Suppose I have table tags which has a field count that indicates how many items have been tagged with the given tag.
How do I increase this counter in SQLAlchemy after I add a new item with an existing tag?
With plain SQL I would do the following:
INSERT INTO `items` VALUES (...)
UPDATE `tags` SET count=count+1 WHERE tag_id=5
But how do I express count=count+1 in SQLAlchemy?
Thanks, Boda Cydo.
python sqlalchemy
python sqlalchemy
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
edited Nov 25 '18 at 15:00
SuperShoot
1,855721
1,855721
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
asked Feb 25 '10 at 14:48
bodacydobodacydo
23.2k68171276
23.2k68171276
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you have something like:
mytable = Table('mytable', db.metadata,
Column('id', db.Integer, primary_key=True),
Column('counter', db.Integer)
)
You can increment fields like this:
m = mytable.query.first()
m.counter = mytable.c.counter + 1
Or, if you have some mapped Models, you can write alternatively:
m = Model.query.first()
m.counter = Model.counter + 1
Both versions will return the sql statement you have asked for. But if you don't include the column and just write m.counter += 1, then the new value would be calculated in Python (and race conditions are likely to happen). So always include a column as shown in the two examples above in such counter queries.
Regards,
Christoph
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
2
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
1
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with+=would result inSET counter=4instead ofSET counter=counter+1. So you shouldnt use the third+=` version.
– tux21b
Feb 25 '10 at 15:54
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
1
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
3
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
|
show 3 more comments
If you are using the SQL layer, then you can use arbitrary SQL expressions in the update statement:
conn.execute(tags.update(tags.c.tag_id == 5).values(count=tags.c.count + 1))
The ORM Query object also has an update method:
session.query(Tag).filter_by(tag_id=5).update({'count': Tag.count + 1})
The ORM version is smart enough to also update the count attribute on the object itself if it's in the session.
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
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%2f2334824%2fhow-to-increase-a-counter-in-sqlalchemy%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
If you have something like:
mytable = Table('mytable', db.metadata,
Column('id', db.Integer, primary_key=True),
Column('counter', db.Integer)
)
You can increment fields like this:
m = mytable.query.first()
m.counter = mytable.c.counter + 1
Or, if you have some mapped Models, you can write alternatively:
m = Model.query.first()
m.counter = Model.counter + 1
Both versions will return the sql statement you have asked for. But if you don't include the column and just write m.counter += 1, then the new value would be calculated in Python (and race conditions are likely to happen). So always include a column as shown in the two examples above in such counter queries.
Regards,
Christoph
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
2
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
1
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with+=would result inSET counter=4instead ofSET counter=counter+1. So you shouldnt use the third+=` version.
– tux21b
Feb 25 '10 at 15:54
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
1
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
3
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
|
show 3 more comments
If you have something like:
mytable = Table('mytable', db.metadata,
Column('id', db.Integer, primary_key=True),
Column('counter', db.Integer)
)
You can increment fields like this:
m = mytable.query.first()
m.counter = mytable.c.counter + 1
Or, if you have some mapped Models, you can write alternatively:
m = Model.query.first()
m.counter = Model.counter + 1
Both versions will return the sql statement you have asked for. But if you don't include the column and just write m.counter += 1, then the new value would be calculated in Python (and race conditions are likely to happen). So always include a column as shown in the two examples above in such counter queries.
Regards,
Christoph
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
2
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
1
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with+=would result inSET counter=4instead ofSET counter=counter+1. So you shouldnt use the third+=` version.
– tux21b
Feb 25 '10 at 15:54
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
1
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
3
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
|
show 3 more comments
If you have something like:
mytable = Table('mytable', db.metadata,
Column('id', db.Integer, primary_key=True),
Column('counter', db.Integer)
)
You can increment fields like this:
m = mytable.query.first()
m.counter = mytable.c.counter + 1
Or, if you have some mapped Models, you can write alternatively:
m = Model.query.first()
m.counter = Model.counter + 1
Both versions will return the sql statement you have asked for. But if you don't include the column and just write m.counter += 1, then the new value would be calculated in Python (and race conditions are likely to happen). So always include a column as shown in the two examples above in such counter queries.
Regards,
Christoph
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
If you have something like:
mytable = Table('mytable', db.metadata,
Column('id', db.Integer, primary_key=True),
Column('counter', db.Integer)
)
You can increment fields like this:
m = mytable.query.first()
m.counter = mytable.c.counter + 1
Or, if you have some mapped Models, you can write alternatively:
m = Model.query.first()
m.counter = Model.counter + 1
Both versions will return the sql statement you have asked for. But if you don't include the column and just write m.counter += 1, then the new value would be calculated in Python (and race conditions are likely to happen). So always include a column as shown in the two examples above in such counter queries.
Regards,
Christoph
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
edited Feb 25 '10 at 15:58
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
answered Feb 25 '10 at 14:59
tux21btux21b
53.9k99694
53.9k99694
2
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
1
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with+=would result inSET counter=4instead ofSET counter=counter+1. So you shouldnt use the third+=` version.
– tux21b
Feb 25 '10 at 15:54
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
1
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
3
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
|
show 3 more comments
2
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
1
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with+=would result inSET counter=4instead ofSET counter=counter+1. So you shouldnt use the third+=` version.
– tux21b
Feb 25 '10 at 15:54
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
1
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
3
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
2
2
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
Thanks. But can you explain about race condition more? Did I understand you correctly that the first version would be safer than the second?
– bodacydo
Feb 25 '10 at 15:10
1
1
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with
+= would result in SET counter=4 instead of SET counter=counter+1. So you shouldnt use the third +=` version.– tux21b
Feb 25 '10 at 15:54
No. Both versions I've showed you are exactly the same (one uses mapped objects and the other tables). But the third statement with
+= would result in SET counter=4 instead of SET counter=counter+1. So you shouldnt use the third +=` version.– tux21b
Feb 25 '10 at 15:54
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
Understood. Thanks for helping!
– bodacydo
Feb 25 '10 at 16:22
1
1
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
Can you explain where mytable.c.counter comes from? That's a syntax I'm not familiar with, and it seems like it refers to the table definition instead of the selected row 'm'... so I'm not clear on how the math works.
– mpounsett
Dec 10 '13 at 3:41
3
3
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
if you increment the actual value (e.g. 3 of type int) in Python, you will get a new integer (e.g. 4). If more clients do this concurrently, you might loose some updates. SqlAlchemy's column type has an overloaded add operator, so writing "column + 1" would result in an object that tells the database (and not Python!) to increment the column by one.
– tux21b
Dec 11 '13 at 21:40
|
show 3 more comments
If you are using the SQL layer, then you can use arbitrary SQL expressions in the update statement:
conn.execute(tags.update(tags.c.tag_id == 5).values(count=tags.c.count + 1))
The ORM Query object also has an update method:
session.query(Tag).filter_by(tag_id=5).update({'count': Tag.count + 1})
The ORM version is smart enough to also update the count attribute on the object itself if it's in the session.
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
add a comment |
If you are using the SQL layer, then you can use arbitrary SQL expressions in the update statement:
conn.execute(tags.update(tags.c.tag_id == 5).values(count=tags.c.count + 1))
The ORM Query object also has an update method:
session.query(Tag).filter_by(tag_id=5).update({'count': Tag.count + 1})
The ORM version is smart enough to also update the count attribute on the object itself if it's in the session.
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
add a comment |
If you are using the SQL layer, then you can use arbitrary SQL expressions in the update statement:
conn.execute(tags.update(tags.c.tag_id == 5).values(count=tags.c.count + 1))
The ORM Query object also has an update method:
session.query(Tag).filter_by(tag_id=5).update({'count': Tag.count + 1})
The ORM version is smart enough to also update the count attribute on the object itself if it's in the session.
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
If you are using the SQL layer, then you can use arbitrary SQL expressions in the update statement:
conn.execute(tags.update(tags.c.tag_id == 5).values(count=tags.c.count + 1))
The ORM Query object also has an update method:
session.query(Tag).filter_by(tag_id=5).update({'count': Tag.count + 1})
The ORM version is smart enough to also update the count attribute on the object itself if it's in the session.
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
answered Feb 25 '10 at 21:58
Ants AasmaAnts Aasma
39.8k67484
39.8k67484
add a comment |
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%2f2334824%2fhow-to-increase-a-counter-in-sqlalchemy%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
