14554

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 <strong>something</strong> like:

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

Answer1:

Based on the information provided I'm geussing you want to perform a union (<a href="https://www.w3schools.com/sql/sql_union.asp" rel="nofollow">https://www.w3schools.com/sql/sql_union.asp</a>) 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: <a href="https://stackoverflow.com/questions/10610960/how-to-union-across-multiple-tables-in-sqlalchemy" rel="nofollow">How to union across multiple tables in SQLAlchemy?</a>

Recommend

  • PHP $_POST doesn't display data - Example code shown
  • why the html5 geolocation fails to get results?
  • When do I have to close mysqli (Database) connection?
  • How to implement xsl
  • Linq Full Outer Join on Two Objects
  • distinct values from multiple fields within one table ORACLE SQL
  • Access user's phone number on iOS 7
  • Servlet stops working on Tomcat server after some hits or time
  • Scipy Leastsq Optional Output Variable (Mesg)
  • Can someone please explain to me in the most layman terms how to use EventArgs?
  • Eliminate partial duplicate rows from result set
  • Unable to get column index with table.getColumn method using custom table Model
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Keep this build forever option - Jenkins
  • Transactional Create with Validation in ServiceStack Redis Client
  • Hardware Accelerated Image Scaling in windows using C++
  • iOS: Detect app start via notification press
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Scrapy recursive link crawler
  • does jqgrid support a multiple checkbox list for editing
  • Lost migrations and Azure database is now out of sync
  • Optimizing database types to compact database (SQLite)
  • RectangularRangeIndicator format like triangular using dojo
  • How would I use PHP exceptions to define a redirect?
  • Cross-Platform Protobuf Serialization
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Web-crawler for facebook in python
  • Do I've to free mysql result after storing it?
  • Calling of Constructors in a Java
  • PHP: When would you need the self:: keyword?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Revoking OAuth Access Token Results in 404 Not Found
  • Suggestions to manage Login/Logout transitions
  • Turn off referential integrity in Derby? is it possible?
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal