On Thu, Jul 11, 2019, at 2:57 AM, Dieter Menne wrote: > > `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)
hi - with the code as given, you need to use your func.coalesce() if that is the result you want: items.with_entities(User.fullname, func.coalesce(User.nickname, ...)) with_entities() does nothing special, it just replaces the things that you had placed in the columns clause of the SELECT in the first place. I guess what you are really trying to do is reduce the columns that you already have, which is not a bad idea but this is a feature that plainly available either in Core or ORM at the moment, however it will be something more clear in future releases using an attribute .selected_columns. right now it would be a little bit awkward: q = q.with_entities(q.column_descriptions[1]['expr'], q.column_descriptions[2]['expr']) that is, the "column_descriptions" attribute gives you "the thing the query is SELECTing from" right now but not in a nice namespaced way. it can be converted to a dictionary or namespace pretty easily but that's not built in to current releases right now. that is it would ideally be: q = q.with_entities(q.selected_columns.name, q.selected_columns.nickname) > > > -- > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/b3856d3b-1536-476f-b18f-a9f46165f5bc%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- 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/d7184a40-c26f-40e8-a946-fa5ac1b6a674%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
