如何在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

基于提供我geussing要在不同的表进行联合（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()) 

最后的查询（在打印语句）返回：

[(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')] 

这个答案是基于：How to union across multiple tables in SQLAlchemy?