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