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?

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.

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.

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)
)

Regarding @Leon comment you can rewrite code a little to avoid race condition.

async def inc_counter(id_: int):
    query = items.update(items.c.id==id_)
    return await db.execute(query, values={'counter': items.c.counter + 1})

P.S. If you a creating tables in class way you will need to access table attribute for that:

from sqlalchemy import update

async def inc_counter(id_: int):
    query = update(Items).filter(Items.id==id_)
    return await db.execute(query, values={'counter': Items.__table__.c.counter + 1})