[Python] How to make a left join in an update with sqlalchemy and mysql?

I am trying to craft a query with SQLAlchemy 2.5. My main goal is to do an update while :

• doing a join on a "parent" table
• and doing a left join between the "parent" table and a third table

I can't seem to make this work : I am always having either errors or Cartesian products.

I have tried a few options. Currently I am trying with a subquery but getting a Cartesian product :

from sqlalchemy import (
DateTime,
ForeignKey,
Integer,
create_engine,
func,
select,
update,
)
from sqlalchemy.orm import DeclarativeBase, mapped_column, sessionmaker


###########
# MODELS #
###########
class Base(DeclarativeBase):
pass


class Parent(Base):
__tablename__ = "parent"

id = mapped_column(Integer, primary_key=True, nullable=False)
updated_at = mapped_column(DateTime, nullable=True)


class Child(Base):
__tablename__ = "child"

id = mapped_column(Integer, primary_key=True, nullable=False)
parent_id = mapped_column(Integer, ForeignKey("parent.id"))
last_status_change = mapped_column(DateTime, nullable=True)


class OtherChild(Base):
__tablename__ = "other_child"

id = mapped_column(Integer, primary_key=True, nullable=False)
parent_id = mapped_column(Integer, ForeignKey("parent.id"))


###########
# DB INIT #
###########

engine = create_engine("mysql://root127.0.0.1/dev?charset=utf8mb4")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

###########
# EXAMPLE #
###########

subq = (
select(Parent.id, Parent.updated_at)
.outerjoin(OtherChild)
.where(OtherChild.id.is_(None))
).subquery()

stmt = (
update(Child)
.where(Child.parent_id.in_(select(subq.c.id)))
.values(
last_status_change=func.CONVERT_TZ(subq.c.updated_at, "Europe/Paris", "UTC")
)
)

compiled = stmt.compile(dialect=engine.dialect, compile_kwargs={"literal_binds": True})
# print(compiled)
with Session() as session:
session.execute(stmt)



In an nutshell, the SQL that I am trying to generate is like (I am aware of the possibility to use text() and raw SQL, but I'd like to make this work with the ORM) :

UPDATE
child
JOIN parent ON
parent.id = child.parent_id
LEFT JOIN other_child ON
other_child.parent_id = parent.id
SET
last_status_change = CONVERT_TZ(parent.updated_at, 'Europe/Paris', 'UTC')
WHERE
other_child.id IS NULL


What am I missing ?

Continue reading...