update joined table using sqlalchemy core

Question

I want to update the values in a table dependent on values in another table.

I have 2 tables:

_LOCATIONR: _id, _status, _uuid

LOCATIONR: _id, _version and some userdata

_status shall be set to 1 if _version=0, _uuid="42" and _id=1

i use python with sqlalchemy, database is sqlite

sc#table LOCATIONR
sc2#table _LOCATIONR
version = 0
uuid = "42"
id = 1
statement = sc2.update().
                where(and_(
                    sc2.c._id == id,
                    sc2.c._id == sc.c._id,
                    sc.c._version == version,
                    sc2.c._uuid == uuid))
r = statement.execute({"_status": 1, "_id": id, "_uuid": uuid})

Log:

2016-03-09 22:08:45,059 INFO sqlalchemy.engine.base.Engine UPDATE "_LOCATIONR" SET _uuid=?, _status=?, _id=? FROM "LOCATIONR" WHERE "_LOCATIONR"._id = ? AND "_LOCATIONR"._id = "LOCATIONR"._id AND "LOCATIONR"._version = ? AND "_LOCATIONR"._uuid = ?
2016-03-09 22:08:45,059 INFO sqlalchemy.engine.base.Engine ('42', 1, 1, 1, 0, '42')
2016-03-09 22:08:45,060 INFO sqlalchemy.engine.base.Engine ROLLBACK

Everytime I execute the above code sqlalchemy makes a rollback. The errors occur only if the whereclause references sc but due to those links it should be ok https://groups.google.com/forum/#!topic/sqlalchemy/HH7Rw65kypA

Update a Joined Table with SQLAlchemy Core

In sqlite this sql statement works: UPDATE _LOCATIONR SET _status=1 WHERE _uuid="42" AND _id=1 AND EXISTS (SELECT * FROM LOCATIONR WHERE _LOCATIONR._id=LOCATIONR._id AND LOCATIONR._version=0); COMMIT; — Coding
– Coding, Commented Mar 10, 2016 at 9:58

J_H · Accepted Answer · 2022-05-13 12:58:15Z

1

statement = (sc2
             .update()
             .where(and_(sc2.c._id == id,
                         sc2.c._uuid == uuid,
                         exists().where(and_(sc2.c._id == sc.c._id,
                                             sc.c._version == version))))

works.

edited May 13, 2022 at 12:58

J_H

21.2k5 gold badges29 silver badges51 bronze badges

answered Mar 10, 2016 at 10:07

Coding

1343 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

update joined table using sqlalchemy core

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related