AttributeError while querying: Neither 'InstrumentedAttribute' object nor 'Comparator' has an attribute

Question

The following code:

Base = declarative_base()
engine = create_engine(r"sqlite:///" + r"d:\foo.db",
                       listeners=[ForeignKeysListener()])
Session = sessionmaker(bind = engine)
ses = Session()

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique = True)

class Bar(Base):
    __tablename__ = "bar"
    id = Column(Integer, primary_key = True)
    foo_id = Column(Integer, ForeignKey("foo.id"))

    foo = relationship("Foo")


class FooBar(Base):
    __tablename__ = "foobar"
    id = Column(Integer, primary_key = True)
    bar_id = Column(Integer, ForeignKey("bar.id"))

    bar = relationship("Bar")



Base.metadata.create_all(engine)
ses.query(FooBar).filter(FooBar.bar.foo.name == "blah")

is giving me this error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with FooBar.bar has an attribute 'foo'

Any explanations, as to why this is happening, and guidance to how such a thing could be achieved?

ostrokach · Accepted Answer · 2018-11-20T18:11:47.390

114

This is because you are trying to access bar from the FooBar class rather than a FooBar instance. The FooBar class does not have any bar objects associated with it--bar is just an sqlalchemy InstrumentedAttribute. This is why you get the error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with FooBar.bar has an attribute 'foo'

You will get the same error by typing FooBar.bar.foo.name outside the sqlalchemy query.

The solution is to call the Foo class directly:

ses.query(FooBar).join(Bar).join(Foo).filter(Foo.name == "blah")

edited Nov 20 '18 at 18:11

answered Jul 15 '14 at 16:23

ostrokach

17,993
11
78
90

30

What if the table has multiple relations to `Foo`? – rr- May 07 '16 at 06:33
That's true, but then we need to run `.join()` in every query of the app ( i.e: user id allowed/not allowed) - in that case, how can i "save" all these "join"s ? – Ricky Levi May 29 '22 at 10:17

score 40 · Answer 2 · edited Jul 06 '17 at 14:57

40

I cannot explain technically what happens but you can work around this problem by using:

ses.query(FooBar).join(Foobar.bar).join(Bar.foo).filter(Foo.name == "blah")

edited Jul 06 '17 at 14:57

m-ric

5,621
7
38
51

answered May 17 '13 at 12:31

Bleeding Fingers

6,993
7
46
74

4

You can understand why it works in the documentation: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins – Flamínio Maranhão Nov 28 '14 at 17:19
2

This is correct, and the accepted answer is wrong because it does not join the tables on their foreign keys. You can actually remove the `.bar` and the `.foo` from this answer, and it still works. – Jim Hunziker Nov 06 '18 at 16:38

score 7 · Answer 3 · answered Jul 23 '18 at 17:55

I was getting the same error Neither 'InstrumentedAttribute' object nor 'Comparator' has an attribute, but in my case, the problem was my model contained a Column named query, which was overwriting the internal property model.query.

I decided to rename that Column to query_text and that removed the error. Alternatively, passing the name= argument to the Column method would have worked: query = db.Column(db.TEXT, name='query_text').

score 2 · Answer 4 · answered Feb 04 '16 at 11:08

A related error that can be caused by configuring your SQLAlchemy relationships incorrectly:

AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'corresponding_column'

In my case, I incorrectly defined a relationship like this:

namespace   = relationship(PgNamespace, id_namespace, backref="classes")

The id_namespace argument to relationship() should just not be there at all. SQLAlchemy is trying to interpret it as an argument of a different type, and failing with an inscrutable error.

Thank you! A column and a relationship having the same name was causing this error and I didn't spot it until reading this answer. — gatherer, Sep 23 '19 at 10:15

score 1 · Answer 5 · answered Mar 09 '22 at 20:10

1

stmt = (
   select(Bar.id).
   where(Bar.foo.has(Foo.name=="test"))
)

Maybe it will be useful... I found it here: https://docs.sqlalchemy.org/en/14/tutorial/orm_related_objects.html#exists-forms-has-any

answered Mar 09 '22 at 20:10

Evgenii

3,283
3
27
32

score 1 · Answer 6 · answered Mar 17 '23 at 12:18

This is a simple working example with multiple relations to the same table. This generally avoid having the error AttributeError while querying: Neither 'InstrumentedAttribute' object nor 'Comparator' has an attribute

def get_company(db: Session, company_id: int):
    best_contact = aliased(User, name="best_contact ")
    other_contact = aliased(User, name="other_contact")
    
    row = (
        db.query(Company, best_contact, other_contact)
        .join(best_contact, Company.best_contact_id == best_contact.id)
        .join(other_contact, Company.other_contact_id == other_contact.id)
        .filter(Company.id == company_id)
        .first()
    )
    return row

AttributeError while querying: Neither 'InstrumentedAttribute' object nor 'Comparator' has an attribute

6 Answers6

Linked