如何在SqlAlchemy中连接不相关的查询 [英] How to concatenate unrelated queries in SqlAlchemy

问题描述

如何将没有关系的sqlalchemy查询和日期之前的sort连接起来?

How can I concatenate sqlalchemy queries with no relation and sort by date?

例如 这些是模型:

Human
Car
Tree

它们都具有列created.这是查询:

And they all have the column created. Here are the queries:

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日期.预期结果将是类似的内容:

Now I want to concatenate these 3 queries and order_by date. The expected result would be something like:

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

推荐答案

根据我所提供的信息，您想进行工会( https://www.w3schools.com/sql/sql_union.asp ).

Based on the information provided I'm geussing you want to perform a union (https://www.w3schools.com/sql/sql_union.asp) on the different tables.

在下面的示例中，创建了不同的表，并结合了这些不同的表.

In the example below the different tables are created, a union of these different tables is made.

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语句中)返回:

The final query (in the print statement) returns:

[(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?

这篇关于如何在SqlAlchemy中连接不相关的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！