Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
390 views
in Technique[技术] by (71.8m points)

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

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.".

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...