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?
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.