SQLAlchemy 2.5 में UPDATE JOIN: MySQL-जैसा समाधान बिना Cartesian product

SQLAlchemy 2.5 में MySQL-स्टाइल UPDATE JOIN: inner/left join, IS NULL फ़िल्टर और join construct से अपडेट, बिना Cartesian product; संक्षिप्त समझ और उदाहरण कोड.

जॉइन से आने वाले डेटा के आधार पर पंक्तियों को अपडेट करना आम ज़रूरत है, लेकिन SQLAlchemy 2.5 में इसे बिना Cartesian product ट्रिगर किए साफ़-सुथरे तरीके से व्यक्त करना मुश्किल हो सकता है। यहां लक्ष्य MySQL-स्टाइल UPDATE ... JOIN है: parent तालिका पर inner join और तीसरी तालिका पर left join, ताकि तीसरी तालिका में संबंधित पंक्ति न होने पर टाइमस्टैम्प अपडेट हो।

समस्या का सेटअप और असफल प्रयास

उद्देश्य यह है कि other_child में मेल खाने वाली पंक्ति न होने पर ही parent से मान लेकर child की पंक्तियाँ अपडेट हों। सीधी-सी subquery रणनीति अंततः Cartesian product या त्रुटियों तक पहुँच जाती है।

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

class ModelBase(DeclarativeBase):
    pass

class ParentRow(ModelBase):
    __tablename__ = "parent"
    id = mapped_column(Integer, primary_key=True, nullable=False)
    updated_at = mapped_column(DateTime, nullable=True)

class ChildRow(ModelBase):
    __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 SiblingRow(ModelBase):
    __tablename__ = "other_child"
    id = mapped_column(Integer, primary_key=True, nullable=False)

db = create_engine("mysql://root:@127.0.0.1/dev?charset=utf8mb4")
ModelBase.metadata.create_all(db)
SessionFactory = sessionmaker(bind=db)

flt = (
    select(ParentRow.id, ParentRow.updated_at)
    .outerjoin(SiblingRow)
    .where(SiblingRow.id.is_(None))
).subquery()

upd_stmt = (
    update(ChildRow)
    .where(ChildRow.parent_id.in_(select(flt.c.id)))
    .values(last_status_change=func.CONVERT_TZ(flt.c.updated_at, "Europe/Paris", "UTC"))
)

with SessionFactory() as db_sess:
    db_sess.execute(upd_stmt)

यह क्यों हो रहा है

ऊपर दिया गया तरीका child पर UPDATE को एक subquery से जोड़ता है, जो उन parent को फ़िल्टर करती है जिनके पास other_child की पंक्तियाँ नहीं हैं, और फिर उसी subquery से सेट किया जाने वाला मान लिया जाता है। व्यवहार में यह इच्छित UPDATE ... JOIN नहीं बनाता और Cartesian product का कारण बन सकता है। लक्ष्य SQL MySQL के UPDATE जैसा होना चाहिए, जिसमें एक inner और एक left join हो, और left-joined तालिका पर IS NULL फ़िल्टर लगाया जाए।

कारगर समाधान: join construct पर सीधे अपडेट करें

यहाँ subquery की ज़रूरत नहीं। SQLAlchemy UPDATE में सीधे join construct को लक्ष्य बना सकता है, जिससे वांछित MySQL सिंटैक्स बनता है।

from sqlalchemy import DateTime, ForeignKey, Integer, create_engine, func, join, update
from sqlalchemy.orm import DeclarativeBase, Session, mapped_column

class ModelBase(DeclarativeBase):
    pass

class ParentRow(ModelBase):
    __tablename__ = "parent"
    id = mapped_column(Integer, primary_key=True, nullable=False)
    updated_at = mapped_column(DateTime, nullable=True)

class ChildRow(ModelBase):
    __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 SiblingRow(ModelBase):
    __tablename__ = "other_child"
    id = mapped_column(Integer, primary_key=True, nullable=False)
    parent_id = mapped_column(Integer, ForeignKey("parent.id"))

engine = create_engine("mysql+pymysql://", echo=True)
ModelBase.metadata.create_all(engine)

with Session(engine) as db_sess:
    jn = join(
        join(ChildRow, ParentRow, ParentRow.id == ChildRow.parent_id),
        SiblingRow,
        SiblingRow.parent_id == ParentRow.id,
        isouter=True,
    )

    stmt = (
        update(jn)
        .values({
            ChildRow.last_status_change: func.CONVERT_TZ(
                ParentRow.updated_at, "Europe/Paris", "UTC"
            )
        })
        .where(SiblingRow.id.is_(None))
    )

    db_sess.execute(stmt)

यह इच्छित MySQL क्वेरी जनरेट करता है।

UPDATE child
INNER JOIN parent ON parent.id = child.parent_id
LEFT OUTER JOIN other_child ON other_child.parent_id = parent.id
SET
    child.last_status_change = CONVERT_TZ(parent.updated_at, %(CONVERT_TZ_1)s, %(CONVERT_TZ_2)s)
WHERE
    other_child.id IS NULL

यह क्यों मायने रखता है

जब बिज़नेस नियम संबंधित पंक्तियों की उपस्थिति या अनुपस्थिति पर निर्भर करता है, तब UPDATE के लक्ष्य के रूप में join का उपयोग ORM कथन को उसी SQL के अनुरूप लाता है जिसकी आपको सच में ज़रूरत है। यह अनचाहे Cartesian product से बचाता है और MySQL के मूल UPDATE ... JOIN रूप का अनुसरण करता है, साथ ही कच्चे SQL पर उतरे बिना सब कुछ SQLAlchemy में रखता है। आप engine echo सक्षम करके या किसी स्टेटमेंट को कम्पाइल करके जेनरेट हुए SQL को देख भी सकते हैं, जिससे जटिल अपडेट को मान्य करना आसान हो जाता है।

मुख्य निष्कर्ष

यदि आपको कई तालिकाओं के आधार पर पंक्तियाँ अपडेट करनी हों—जिसमें left join और IS NULL फ़िल्टर शामिल हो—तो एक स्पष्ट join construct बनाकर उसे update() में दें। फ़िल्टर को left-joined तालिका पर ही रखें और मान सीधे joined तालिकाओं से असाइन करें। संदेह हो तो जेनरेट हुए SQL को देख लें कि वह आपके इच्छित MySQL UPDATE with joins से मेल खाता है।