difference in label with first() or db.session.execute().first()
we have a fairly complex query with inner and outer joins and aliases.
we normally use the following syntax
q = db.session.query(UserCarts,Merchant,Organization).join(Merchant,Merchant.c.id == UserCarts.c.merchant_id).outerjoin(Organization,Organization.c.id == Merchant.c.organization_id).filter(db.and_(UserCarts.c.cart_id == '0f05b5e25ce946af9e0a3b37f0843870'))
if we use
q.first().keys()
, we get repeated keys "name" between Merchant and Organization. I tried this with "with_labels()" at the end as well.
however if I do this:
p = db.session.execute(q)
s = p.first()
in that case, I get labeled keys. Why is there a difference ?
Comments (14)
-
repo owner -
reporter this is the result of : q.first().keys() vs db.session.execute(q).first().keys() http://pastebin.com/jxrE6cPN
P.S. the query I'm running is larger than the example I just pasted here.
-
repo owner um, are UserCarts, Merchant, Organization Table objects or mapped classes?
-
reporter this is used from within flask
metadata = db.MetaData() metadata.reflect(bind = db.get_engine(app)) User = metadata.tables['users'] MerchantIdentity = metadata.tables['merchant_identities'] UserCarts = metadata.tables['user_carts'] Merchant = metadata.tables['merchants'] Relationship = metadata.tables['relationships'] TransactionLog = metadata.tables['transaction_logs'] PhoneIdentity = metadata.tables['phone_identities'] org_user_alias = db.aliased(User, name = "org_user") merchant_user_alias = db.aliased(User, name = "merchant_user")
I'm using reflection to work with an existing database.
-
repo owner those are Table objects. The ResultProxy you get back from session.execute() is going to have much richer behavior here; if you want to build up using Query, you might want to say session.execute(query.with_labels().statement).
-
repo owner hmm even query.with_labels() won't affect those column labels. Just use session.execute() with Core, that is, use the select() object. It's much better when dealing with only straight tables.
-
reporter no problem - in fact with_labels() has no impact on the result. The same two different results come up with or without with_labels().
it would be nice if you could qualify that in the documentation. I was not aware that I should be expecting a difference in richness even though I use with_labels.
-
repo owner or, this might be more appropriate, use the automap extension so that you do get classes back: http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html
-
repo owner mmmm no it works, with_labels() applies the labels to query.statement as documented.
-
repo owner here's the docs for "with_labels()":
"""Apply column labels to the return value of Query.statement. Indicates that this Query's `statement` accessor should return a SELECT statement that applies labels to all columns in the form <tablename>_<columnname>; this is commonly used to disambiguate columns from multiple tables which have the same name. When the `Query` actually issues SQL to load rows, it always uses column labeling.
it's pretty clear this applies to only the ".statement" accessor, no?
-
repo owner - changed status to resolved
- add a note clarifying query.with_labels(), fixes
#3506
→ <<cset 292f5bec1cbf>>
-
repo owner - add a note clarifying query.with_labels(), fixes
#3506
(cherry picked from commit 292f5bec1cbfcaa9d16af8fe4149c7191f194f11)
→ <<cset 6416c2a3f768>>
- add a note clarifying query.with_labels(), fixes
-
repo owner - add a note clarifying query.with_labels(), fixes
#3506
(cherry picked from commit 292f5bec1cbfcaa9d16af8fe4149c7191f194f11)
→ <<cset 3d4edfa51b43>>
- add a note clarifying query.with_labels(), fixes
-
repo owner I added a special note. let me know what else you'd want.
- Log in to comment
can you please illustrate mappings succinctly as well as what the specific keys you are getting are, thanks.