How to concatenate unrelated queries in SqlAlchemy

Question

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

e.g. These are the models:

Human
Car
Tree

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(...)

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>
...

How would you write the SQL? – univerio Jul 04 '17 at 21:18 — univerio, Jul 04 '17 at 21:18

score 1 · Answer 1 · answered Jul 05 '17 at 04:00

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())

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

This answer is based on: How to union across multiple tables in SQLAlchemy?

Close. But this is only returning one column whereas I need the whole column of each type (car, tree, human) with different number of columns. — DKo, Jul 05 '17 at 11:24
You could combine the answer provided with the further details from this question: https://stackoverflow.com/questions/7971798/sqlalchemy-union-with-different-number-of-columns. Does that provide an answer to your question? — mtndoe, Jul 05 '17 at 12:04

How to concatenate unrelated queries in SqlAlchemy

1 Answers1

Linked