python - Sqlalchemy 为父母过滤父子表
问题描述
我有一些我正在尝试搜索的模型,所以我希望显示返回所有父对象的结果,其中父名称为“foo”或子名称为“foo”。
我有一个查询:
parents = Session.query(Parent).\
join(Child_s3).\
filter(Parent.name.ilike("%foo%")).\
filter(Child_s3.name.ilike("%foo%")).\
order_by(asc(Product.name))
和模型:
class Parent(BaseSO):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
name = Column(Unicode(100), nullable=False, unique=True)
colours = relationship('Child_s3', secondary=Parent_images, backref='Parentc')
class Child_s3(BaseSO):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
name = Column(Unicode)
Parent_images = Table(
'Parent_images', BaseSO.metadata,
Column('parent_id', Integer, ForeignKey('parents.id')),
Column('child_id', Integer, ForeignKey('children.id'))
)
我的查询显示了名为“foo”的父对象,但没有显示任何父对象,也有名为“foo”的子对象,任何人都可以帮助构建此查询以在两个表中搜索相应的父对象吗?
解决方案
此代码显示如何使用显式连接或子查询获取结果:
import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Parent_images = sa.Table(
'Parent_images', Base.metadata,
sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
sa.Column('child_id', sa.Integer, sa.ForeignKey('children.id'))
)
class Parent(Base):
__tablename__ = 'parents'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(100), nullable=False, unique=True)
colours = orm.relationship('Child_s3', secondary=Parent_images, backref='parents')
def __repr__(self):
return 'Parent(name=%s)' % self.name
__str__ = __repr__
class Child_s3(Base):
__tablename__ = 'children'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode)
def __repr__(self):
return 'Child_s3(name=%s)' % self.name
__str__ = __repr__
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = orm.sessionmaker(bind=engine)
session = Session()
for parent, child in [('boofoo', 'spam'), ('baz', 'foobar'), ('bar', 'quux')]:
p1 = Parent(name=parent)
session.add(p1)
p1.colours.append(Child_s3(name=child))
session.commit()
print('Join')
session = Session()
q = (session.query(Parent)
.join(Child_s3, Parent.colours)
.filter(sa.or_(Parent.name.ilike('%foo%'),
Child_s3.name.ilike('%foo%'))))
for p in q.all():
print(p, p.colours)
session.commit()
print()
print('Subquery')
session = Session()
q = (session.query(Parent)
.filter(sa.or_(Parent.name.ilike('%foo%'),
Parent.colours.any(Child_s3.name.ilike('%foo%')))))
for p in q.all():
print(p, p.colours)
session.commit()
print()
连接查询
q = (session.query(Parent)
.join(Child_s3, Parent.colours)
.filter(sa.or_(Parent.name.ilike('%foo%'),
Child_s3.name.ilike('%foo%'))))
生成此 SQL
SELECT parents.id AS parents_id, parents.name AS parents_name
FROM parents JOIN "Parent_images" AS "Parent_images_1" ON parents.id = "Parent_images_1".parent_id JOIN children ON children.id = "Parent_images_1".child_id
WHERE lower(parents.name) LIKE lower(?) OR lower(children.name) LIKE lower(?)
子查询
q = (session.query(Parent)
.filter(sa.or_(Parent.name.ilike('%foo%'),
Parent.colours.any(Child_s3.name.ilike('%foo%')))))
生成此 SQL:
SELECT parents.id AS parents_id, parents.name AS parents_name
FROM parents
WHERE lower(parents.name) LIKE lower(?) OR (EXISTS (SELECT 1
FROM "Parent_images", children
WHERE parents.id = "Parent_images".parent_id AND children.id = "Parent_images".child_id AND lower(children.name) LIKE lower(?)))
该脚本从示例数据生成此输出:
Join
Parent(name=baz) [Child_s3(name=foobar)]
Parent(name=boofoo) [Child_s3(name=spam)]
Subquery
Parent(name=boofoo) [Child_s3(name=spam)]
Parent(name=baz) [Child_s3(name=foobar)]
推荐阅读
- java - 你好,我想打印我的列表,它在哪里是一个用户类对象。任何人都可以帮助我吗?
- sql-server - 如何使用 SELECT 和 LinkedServer 在 SQL INSERT INTO 查询中添加 NOT EXISTS
- java - 如何在Java中初始化一个long-s的范围数组?
- c - 两个数字之间的乘法
- jquery - 如何使用jquery列出以特定id开头的所有元素
- swift - 快速逐字显示文本
- mysql - 如何在没有 Django 中的数据库级别唯一约束的情况下确保 create_or_update 的唯一性
- android - 填写所有卡信息后Payumoney支付网关问题
- ironpython - 有人知道 FuzzyWuzzy 是否在 IronPython 中工作吗?
- sql - 我有查询,如果记录有 44,我想要记录(pids)不附加作业编码 74 的结果,到目前为止我有以下查询