Using NOT EXISTS clause in sqlalchemy ORM query

Question

I want to convert the following raw sql query into a sqlalchemy ORM query :

SELECT * FROM kwviolations AS kwviol WHERE kwviol.proj_id=1 AND NOT EXISTS (SELECT * FROM
kwmethodmetrics AS kwmetrics WHERE kwmetrics.kw_id=kwviol.kw_id AND kwmetrics.checkpoint_id=5);

I tried the following ORM query but didn't succeed:

self.session.query(KWViolations).filter(KWViolations.proj_id==project.id).\
filter(and_(~KWViolations.kw_id.any(KWMethodMetrics.kw_id==KWViolations.kw_id),KWMethodMetrics.checkpoint_id==checkpoint.id))

Can anyone please help? Thanks in advance

zzzeek · Accepted Answer · 2013-01-31 00:21:48Z

33

kw_id here seems to be a scalar column value, so you can't call any() on that - any() is only available from a relationship() bound attribute. Since I don't see one of those here you can call the exists() directly:

from sqlalchemy import exists

session.query(KWViolations).filter(KWViolations.proj_id == project.id).\
    filter(
        ~exists().where(
                and_(
                    KWMethodMetrics.kw_id == KWViolations.kw_id,
                    KWMethodMetrics.checkpoint_id == checkpoint.id
                )
        )
    )

answered Jan 31, 2013 at 0:21

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

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

Collectives™ on Stack Overflow

Using NOT EXISTS clause in sqlalchemy ORM query

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