I have posted this on Stackoverflow, but there was no response.
https:
//stackoverflow.com/questions/56891733/with-entities-referring-to-sqlite-column-aliased-with-label-self-contained-re
How do I use .with_entities to refer to the items? The following fails:
from sqlalchemy import Column, Integer, String, create_engine, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
from sqlalchemy import Column, Integer, String, create_engine, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Create data
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
nickname = Column(String)
engine = create_engine('sqlite://', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
session.add_all([
User(name='wendy', fullname='Wendy Williams'),
User(name='mary', fullname='Mary Contrary'),
User(name='fred', fullname='Fred Flintstone', nickname='freddy')])
session.commit()
# End of create data
items = session.query(
User.id, User.name,
func.coalesce(User.nickname, 'Nicky').label('nickname'))
# Checking that with_entities works on original data set
subset_items = session.query(
User.id, User.name)\
.with_entities(User.name, User.nickname)\
.all()
print(subset_items) # Great....
# Wanted: a subset with columns fullname and nickname only
# Result is wrong, it does not use coalesce result
special_items = items\
.with_entities(User.fullname, User.nickname) \
.all()
print(special_items)
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/b3856d3b-1536-476f-b18f-a9f46165f5bc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
