with_entities referring to SQlite column aliased with <label>

[Dieter Menne]

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)

[Mike Bayer]

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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.

[Dieter Menne]

Am Donnerstag, 11. Juli 2019 16:11:42 UTC+2 schrieb Mike Bayer:

On Thu, Jul 11, 2019, at 2:57 AM, Dieter Menne wrote:

Thanks, if one thinks of delayed execution, this makes sense. It is a bit strange, though, that there is no select. I am an R-programmer by default, and in dplyr with similar feature select is the most important together with filter