difference in label with first() or db.session.execute().first()

[Sandeep Srinivasa]

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 ?

[Mike Bayer]

can you please illustrate mappings succinctly as well as what the specific keys you are getting are, thanks.

[Sandeep Srinivasa]

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.

[Mike Bayer]

um, are UserCarts, Merchant, Organization Table objects or mapped classes?

[Sandeep Srinivasa]

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.

[Mike Bayer]

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).

[Mike Bayer]

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.

[Sandeep Srinivasa]

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.

[Mike Bayer]

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

[Mike Bayer]

mmmm no it works, with_labels() applies the labels to query.statement as documented.

[Mike Bayer]

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?

[Mike Bayer]

• add a note clarifying query.with_labels(), fixes #3506

→ <<cset 292f5bec1cbf>>

[Mike Bayer]

• add a note clarifying query.with_labels(), fixes #3506

(cherry picked from commit 292f5bec1cbfcaa9d16af8fe4149c7191f194f11)

→ <<cset 6416c2a3f768>>

[Mike Bayer]

• add a note clarifying query.with_labels(), fixes #3506

(cherry picked from commit 292f5bec1cbfcaa9d16af8fe4149c7191f194f11)

→ <<cset 3d4edfa51b43>>

[Mike Bayer]

I added a special note. let me know what else you'd want.