python - sqlalchemy different value from `len(query.all())` and `query.count()`

Question

This is an example code.

A Document has many Comment(s)

PostComment extends Comment (with sqlalchemy polymorphic feature)

Some query returns different result between len(query.all()) and query.count()

• sqlalchemy version : 1.0.8
• mysql version : 5.6.25

See main function below.

What happened?

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, Float, Boolean, ForeignKey, String, Unicode, DateTime, Date, UniqueConstraint
from sqlalchemy.orm import relationship, backref

engine = create_engine('mysql://root:[email protected]:3306/document')

DBSession = scoped_session(sessionmaker(bind=engine))

Base = declarative_base()
Base.metadata.bind = engine

class Document(Base):
    __tablename__ = 'document'

    id = Column(Integer, primary_key=True)


class Comment(Base):
    __tablename__ = 'comment'

    id = Column(Integer, primary_key=True)
    type = Column(String(50))
    document_id = Column(Integer, ForeignKey('document.id'), primary_key=True)
    document = relationship('Document', backref=backref('comments', lazy='dynamic'))

    __mapper_args__= {
        'polymorphic_identity' : 'comment',
        'polymorphic_on' : type,
    }


class PostComment(Comment):
    __tablename__ = 'post_comment'

    id = Column(Integer, ForeignKey('comment.id'), primary_key=True)
    ready = Column(Boolean)

    __mapper_args__= {
        'polymorphic_identity' : 'post_comment',
    }



def main():
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    d1 = Document()
    DBSession.add(d1)

    d2 = Document()
    DBSession.add(d2)

    c1 = PostComment(document=d1, ready=True)
    DBSession.add(c1)

    c2 = PostComment(document=d1, ready=True)
    DBSession.add(c2)

    c3 = PostComment(document=d2, ready=True)
    DBSession.add(c3)

    c4 = PostComment(document=d2, ready=True)
    DBSession.add(c4)

    DBSession.commit()

    query = d1.comments.filter(PostComment.ready==True)

    print len(query.all())      # returns 2
    print query.count()         # returns 8


if __name__ == '__main__':
    main()

Updates

http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.count

It says "Return a count of rows this Query would return.".

Answer 1

Why do you get 8 as result instead of 2? Because you get a query which is a cartesian product (8 = 2 * 2 * 2).
In turn, this happens because you have dynamic relationship with inheritance, which creates select from both tables (comment and post_comment) without any predicate between them.

Why the first query returns just 2? Well, because you are asking for actual mapped instances, sqlalchemy is smart enough to filter out the duplicates, although the underlying SQL statement does return 8 rows as well.

Add a join to your query to fix this:

query = d1.comments.join(PostComment).filter(PostComment.ready == True)