This is an edited version of the original question, with a self-contained example.
from sqlalchemy import Column, Integer, String, create_engine, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
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()
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)
# items will be used elsewhere....
# 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)
How do I use .with_entities to refer to the items? The following fails
.with_entities(items.fullname, items.nickname) \
so did variants with quotes as suggested by @Nick in reply to the original question.
