sqlalchemy与"len(query.all())"和"query.count()"的值不同 [英] sqlalchemy different value from `len(query.all())` and `query.count()`

问题描述

这是示例代码.

文档中有很多评论

PostComment扩展了注释(具有sqlalchemy多态功能)

PostComment extends Comment (with sqlalchemy polymorphic feature)

某些查询在len(query.all())和query.count()

• sqlalchemy版本:1.0.8
• mysql版本:5.6.25

请参阅下面的主要功能.

See main function below.

发生了什么事?

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:root@192.168.59.103: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()

更新

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

推荐答案

为什么得到的结果是8而不是2?因为您得到的查询是笛卡尔积(8 = 2 * 2 * 2).
反过来，发生这种情况是因为您具有dynamic与和 inheritance的关系，该关系从两个表(comment和post_comment)中创建select，而它们之间没有任何谓词.

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.

为什么第一个查询仅返回2?好吧，因为您要的是实际的映射实例，所以sqlalchemy足够聪明，可以过滤出重复项，尽管底层的SQL语句也确实返回了8行.

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.

在查询中添加join可以解决此问题:

Add a join to your query to fix this:

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

这篇关于sqlalchemy与"len(query.all())"和"query.count()"的值不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！