I'd like to achieve toggling a boolean flag with just one query.
My query looks as follows:
session.query(Foo).update({"status": SOME_NOT_OPERATOR})
Does SQLAlchemy supports PostgreSQL NOT (https://www.postgresql.org/docs/current/functions-logical.html) operator. How this can be achieved different way?
As Adrian Klaver points out in their comment, SQLAlchemy's not_ operator will toggle the values. All of these statements are equivalent:
# 1.x style
session.query(Foo).update({'status': ~Foo.status})
session.query(Foo).update({'status': not_(Foo.status)})
# 2.0 style
session.execute(update(Foo).values(status=~Foo.status))
session.execute(update(Foo).values(status=not_(Foo.status)))
Will generate this SQL:
UPDATE foo SET status=NOT foo.status
... update({"status": SOME_NOT_OPERATOR})is about setting a value not using an operator. Ifstatusis indeed abooleantype and you want to toggle it the:{"status": True}or{"status": False}.statusset to whichever value it isn't already, without having to query the value to decide whether to set it toTrueorFalse.SQLAlchemyset up, so can't test. Maybe try{"status": ~Foo.status}per Invert.