SQLAlchemy AttributeError: 'Query' object has no attribute '_sa_instance_state' when retrieving from database

Question

The problem is trying to retrieve an object with relationships from the database using SQLAlchemy on Pyramid. What I want basically is to create the objects I need to retrieve from the database to complete the data needed for a web page.

When I try to access the url /poll/{id} (using a valid poll id, for example: /poll/1) to get the page I get this error: AttributeError: 'Query' object has no attribute '_sa_instance_state'. What's the mistake?

This is the relevant part of the model:

class Question(Base):
    __tablename__ = 'question'
    id = Column(Integer, primary_key=True)
    text = Column(String(250))
    type_id = Column(Integer, ForeignKey('type.id'))
    type = relationship(Type)
    poll_id = Column(Integer, ForeignKey('poll.id'))
    poll = relationship(Poll)

    def __init__(self, text, type, poll):
        self.text = text
        self.type = type
        self.poll = poll


class Option(Base):
    __tablename__ = 'option'
    id = Column(Integer, primary_key=True)
    text = Column(String(250))
    question_id =  Column(Integer, ForeignKey('question.id'))
    question = relationship(Question)

    def __init__(self, text, question):
        self.text = text
        self.question = question

This one is the part of the code that gives me trouble. The debugger points at the second to last line (the Option object).

if request.matchdict['id'] != None:
            pinst = session.query(Poll).get(request.matchdict['id'])
            typeq = session.query(Type).first()
            qinst = session.query(Question).filter_by(poll=pinst)
            lopt = session.query(Option).filter_by(question=qinst)
            return {'question':qinst, 'arroptions':lopt, 'type':typeq}

Thanks in advance!

Answer 1

qinst is a Query, not a Question. You probably want:

qinst = session.query(Question).filter_by(poll=pinst).one()

or

qinst = session.query(Question).filter_by(poll=pinst).first()

You could also add a backref on Question so you can go from Poll to Question:

class Question(Base):
    ...
    poll = relationship(Poll, backref="question")

qinst = pinst.question