sqlalchemy 与 `len(query.all())` 和 `query.count()` 的值不同

2022-01-24 00:00:00 python sqlalchemy count polymorphism

问题描述

这是一个示例代码.

一个文档有很多评论

PostComment 扩展了 Comment(具有 sqlalchemy 多态特性)

PostComment extends Comment (with sqlalchemy polymorphic feature)

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

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

见下面的主要功能.

发生了什么?

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).
反过来，发生这种情况是因为您有 with inheritance 的 dynamic 关系，它从两个表(comment 和 post_comment) 之间没有任何谓词.

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)

相关文章