Hi, Mike.
I run into strange behavior, see example below. In short, ORM does not
select column from nested union when a label is assigned to the column.
This results in mixed up attributes of a mapped object.
from datetime import datetime
from sqlalchemy import Column, DateTime, Integer, create_engine, select,
union
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True)
created_at = Column(DateTime)
modified_at = Column(DateTime)
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
dt1, dt2 = datetime(2011, 1, 1), datetime(2012, 2, 2)
dt3, dt4 = datetime(2013, 3, 3), datetime(2014, 4, 4)
article1 = Article(created_at=dt1, modified_at=dt2)
article2 = Article(created_at=dt3, modified_at=dt4)
session.add_all((article1, article2))
session.commit()
session.expunge_all()
query = (
session.query(Article)
.select_entity_from(
union(
select((Article, Article.modified_at.label('order_by'))),
select((Article, Article.created_at.label('order_by'))),
)
)
.order_by(Article.id)
)
article1, article2 = query.all()
print('article1')
print('\tcreated_at', article1.created_at, '\t\texpected', dt1.isoformat())
print('\tmodified_at', article1.modified_at, '\texpected', dt2.isoformat())
print('article2')
print('\tcreated_at', article2.created_at, '\t\texpected', dt3.isoformat())
print('\tmodified_at', article2.modified_at, '\texpected', dt4.isoformat())
# article1
# created_at 2011-01-01 00:00:00 expected
2011-01-01T00:00:00
# modified_at 2011-01-01 00:00:00 expected
2012-02-02T00:00:00
# article2
# created_at 2013-03-03 00:00:00 expected
2013-03-03T00:00:00
# modified_at 2013-03-03 00:00:00 expected
2014-04-04T00:00:00
Rendered SQL query:
SELECT anon_1.id AS anon_1_id, anon_1.order_by AS anon_1_order_by,
anon_1.modified_at
AS anon_1_modified_at
FROM (SELECT article.id AS id,
article.created_at AS created_at, -- this field is not
selected by outer SELECT
article.modified_at AS modified_at,
article.created_at AS order_by
FROM article
UNION
SELECT article.id AS id,
article.created_at AS created_at, -- this field is not
selected by outer SELECT
article.modified_at AS modified_at,
article.modified_at AS order_by
FROM article) AS anon_1
ORDER BY anon_1.id
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/68ac9acc-9c06-4c6d-981d-07df7a7a62c6%40googlegroups.com.
