如何在 SqlAlchemy 中连接不相关的查询

Question

如何连接没有关系的sqlalchemy查询，并按日期sort？

例如 这些是模型：

Human
Car
Tree

他们都有列created.以下是查询：

q1 = session.query(Human.created.label('created'), Human).filter(...)
q2 = session.query(Car.created.label('created'), Car).filter(...)
q3 = session.query(Tree.created.label('created'), Tree).filter(...)

现在我想连接这 3 个查询并order_by日期。预期结果如下：

date      | instance
----------------------------
<created> | Human<instance>
<created> | Car<instance>
<created> | Car<instance>
<created> | Tree<instance>
<created> | Human<instance>
...

Answer 1

根据提供的信息，我想在不同的表上执行联合(https://www.w3schools.com/sql/sql_union.asp(。

在下面的示例中，创建了不同的表，并集了这些不同的表。

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from datetime import datetime
Base = declarative_base()

class Human(Base):
__tablename__ = 'human'
id = Column('id', Integer, primary_key=True)
created = Column('created', DateTime)
name = Column('name', String(250))
def __init__(self, created, name):
self.created = created
self.name = name
def __repr__(self):
return '<{created} - {name}>'.format(created=self.created, name=self.name)

class Car(Base):
__tablename__ = 'car'
id = Column('id', Integer, primary_key=True)
created = Column('created', DateTime)
brand = Column(String(250))
def __init__(self, created, brand):
self.created = created
self.brand = brand
def __repr__(self):
return '<{created} - {brand}>'.format(created=self.created, brand=self.brand)

class Tree(Base):
__tablename__ = 'tree'
id = Column('id', Integer, primary_key=True)
created = Column('created', DateTime)
type = Column(String(250))
def __init__(self, created, type):
self.created = created
self.type = type
def __repr__(self):
return '<{created} - {type}>'.format(created=self.created, type=self.type)

engine = create_engine('sqlite:///')
session = sessionmaker()
session.configure(bind=engine)
ex_ses = session()
Base.metadata.create_all(engine)
human = Human(datetime.now(), 'Human a')
human2 = Human(datetime.now(), 'Human b')
car = Car(datetime.now(), 'Car a')
car2 = Car(datetime.now(), 'Car b')
tree = Tree(datetime.now(), 'Tree a')
tree2 = Tree(datetime.now(), 'Tree b')
ex_ses.add(human)
ex_ses.add(human2)
ex_ses.add(car)
ex_ses.add(car2)
ex_ses.add(tree)
ex_ses.add(tree2)
ex_ses.commit()
# Query
h = ex_ses.query(Human.created, 'name')
c = ex_ses.query(Car.created, 'brand')
t = ex_ses.query(Tree.created, 'type')
print(h.union_all(c).union_all(t).all())

最后一个查询(在 print 语句中(返回：

[(datetime.datetime(2017, 7, 5, 5, 55, 54, 633863), 'Human a'),
(datetime.datetime(2017, 7, 5, 5, 55, 54, 633863), 'Human b'),
(datetime.datetime(2017, 7, 5, 5, 55, 54, 633863), 'Car a'),
(datetime.datetime(2017, 7, 5, 5, 55, 54, 633863), 'Car b'),
(datetime.datetime(2017, 7, 5, 5, 55, 54, 633863), 'Tree a'),
(datetime.datetime(2017, 7, 5, 5, 55, 54, 633863), 'Tree b')]

这个答案是基于：如何在SQLAlchemy中跨多个表联合？