python - Generating Boolean Expressions from Subqueries in SQLAlchemy -

i have following sqlalchemy models:

pending_state = 'pending' complete_state = 'success' error_state = 'error'  class assessment(db.model):     __tablename__ = 'assessments'      id = db.column(db.integer, primary_key=true)     state = db.column(         db.enum(pending_state, complete_state, error_state,                 name='assessment_state'),         default=pending_state,         nullable=false,         index=true)      test_results = db.relationship("testresult")   class testresult(db.model):     __tablename__ = 'test_results'      name = db.column(db.string, primary_key=true)     state = db.column(         db.enum(pending_state, complete_state, error_state,                 name='test_result_state_state'),         default=pending_state,         nullable=false,         index=true)      assessment_id = db.column(         db.integer,         db.foreignkey(             'assessments.id', onupdate='cascade', ondelete='cascade'),         primary_key=true) 

and trying implement logic update assessment error state if of test results in error state , update assessment success state if of test results in success state.

i can write raw sql this:

select 'error' assessments assessments.state = 'error' or 'error' in (     select test_results.state     test_results     test_results.assessment_id = 1); 

but don't know how translate sqlalchemy. i'd think subquery like:

(select([test_results.state]).where(test_results.assessment_id == 1)).in_('error') 

but can't find way compare query results against literals i'm doing in raw sql. swear must missing something, i'm not seeing way write queries return boolean expressions, think fundamentally i'm butting against. simple as:

select 'a' = 'b' 

seems absent documentation.

any ideas on how express state change in sqlalchemy? i'd open rethinking schemas if looks i'm going in silly way.

thanks!

query below should error check. keep in mind no rows returned in case not eror.

q = (db.session.query(literal_column("'error'"))      .select_from(assessment)      .filter(assessment.id == sid)      .filter(or_(          assessment.state == error_state,          assessment.test_results.any(testresult.state == error_state),      ))) 

if wish similar check success, find if there testresult not success , negate boolean result.