python - How to increase a counter in SQLAlchemy

47
votes

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?

3
pythonsqlalchemy

3 Answers

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

39
votes

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.

0
votes

I got here following a tutorial for FastAPI, SQLAlchemy and databases (async). After various tries I finally got it working with following code in my crud.py / control layer.

async def inc_counter():
    query = counters.select(counters.c.id==1)
    current_val = await database.fetch_val(query=query, column=1)
    query = counters.update().where(counters.c.id==1).values(counter=current_val+1).returning(counters.c.counter)
    return await database.execute(query=query)

My table looks like this:

counters = Table('counters', metadata,
    Column('id', Integer, primary_key=True),
    Column('counter', Integer)
)