时间戳

首页 > 解决方案 > 对基类的 SQLAlchemy 查询，包括派生类

python - 对基类的 SQLAlchemy 查询，包括派生类

问题描述

我在 sqlalchemy 中使用单表继承配置来创建几个派生类。查询基类时，结果也包括所有派生类。我希望结果只包含基类。

使用了 sqlalchemy 文档中的示例代码。

from sqlalchemy import create_engine, text, func, and_, Column, Integer, String, ForeignKey, Boolean, MetaData, Table
from sqlalchemy.orm import sessionmaker, with_polymorphic, backref, relationship
from sqlalchemy.ext.declarative import declarative_base, declared_attr, has_inherited_table


POSTGRES = {
    'user': 'sqlalchemy',
    'pw': 'sqlalchemy',
    'db': 'sqlalchemy',
    'host': '127.0.0.1',
    'port': '5432',
}

Base = declarative_base()
meta = MetaData()

engine = create_engine('postgresql://%(user)s:%(pw)s@%(host)s:%(port)s/%(db)s' % POSTGRES, echo=False)
DBSession = sessionmaker(bind=engine)
s = DBSession()
d = {}


''' Create Base Classes using single table polymorphic identity column '''


class Person(Base):

    __tablename__ = 'people'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    discriminator = Column('type', String(50))

    __mapper_args__ = {'polymorphic_on': discriminator, 'polymorphic_identity': 'person'}


class Engineer(Person):

    primary_language = Column(String(50))

    __mapper_args__ = {'polymorphic_identity': 'engineer'}


''' Create Empty Schema '''

Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)

''' Create a bunch of objects '''

for id in range(1, 11):

    s.add(Person(name='person_%s' % str(id)))
    s.add(Engineer(name='engineer_%s' % str(id)))
    s.commit()
    s.flush()

print('people: %s' % s.query(Person).count())
print('engineers: %s' % s.query(Engineer).count())

''' Person query should only have 10 results but has 20. Engineer query has 10 results. '''

s.flush()
s.close()

exit()

人员查询应该有 10 个结果，但也包括工程师。

标签： pythonsqlalchemy

解决方案

似乎唯一的解决方案是在查询中添加过滤器。

people = s.query(Person).filter_by(discriminator='person').all()

推荐阅读