I've been investigating this a little further and think I found some other issues. Our data team changed the stored procedure to stop aliasing the column names, so passing the mapped columns right into .columns() is working (in other words, the rest of this post doesn't reflect my use case anymore :)).
However, labels no longer work as arguments to .columns() unless I go back to 0.9.1 logic by setting ._textual=False and .use_labels = True. Also, passing keyword arguments to .columns() only works if the names line up with the result set names, i.e. using the position as a key seems to be disabled for TextAsFrom objects. Here's a gist of the nose test suite that helped me figure out what was working and what wasn't: https://gist.github.com/garaden/8835587 On Sun, Feb 2, 2014 at 5:42 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Feb 2, 2014, at 4:31 PM, Matt Phipps <matt.the.m...@gmail.com> wrote: > > def _trackable_truckload_details(): > text = db.text("EXEC ODSQuery.SelectBridgeLoadBoard") > cols = [col for col in LoadBoard.__table__.c] > cols = map((lambda x: label('ODSQuery_tblLoadBoard_' + x.name, x)), > cols) > mobile_cols = > LoadMobileTracking.load_mobile_tracking_id.property.columns > mobile_cols = map((lambda x: label('LoadMobileTracking_' + x.name, > x)), cols) > cols.extend(mobile_cols) > taf = text.columns(*cols) > return db.session.query( > LoadBoard.load, > LoadBoard.orgn_stop, > LoadBoard.dest_stop, > > LoadMobileTracking.load_mobile_tracking_id).from_statement(taf).all() > > > Actually, I'm pretty surprised it worked at all before, without the > labeling. How did it figure out which result set columns went to which ORM > object? > > > This is because what’s actually going on is more sophisticated than just > matching up the names. When the ORM looks for columns in a row, it uses > the actual Column object to target the column. If your class is mapped to > a table “users”, for example, it would look like this: > > users = Table(‘users’, metadata, Column(‘id’, Integer), Column(‘name’, > String)) > > # … later > > for row in conn.execute(some_orm_statement): > user_id = row[users.c.id] > user_name = row[users.c.name] > > that is, we aren’t using strings at all. When the Core select() object > (or TextAsFrom in this case) is compiled for the backend, all the Column > objects it SELECTs from are put into an internal collection called the > “result_map”, which keys the result columns in several ways, including > their positional index (0, 1, 2, ..) as well as the string name the > statement knows they’ll have in the result set (e.g. the label name in this > case) to all the objects that might be used to look them up. > > So using a label(), that adds another layer onto this. The label() you > create from an existing Column still refers to that Column, and we say the > Label object “proxies” the Column. if you look in mylabel.proxy_set() > you’ll see that Column. > > So when we generate the result_map, we put as keys *all* of the things > that each label() is a “proxy” for, including the Column objects that are > in our mapping. its this large and awkward dictionary structure I’ve had > to stare at for many years as I often have to fix new issues that have > arisen (such as this one). > > The result is generated, we link the columns in the cursor.description by > string name to the string names we know are rendered in the final compiled > construct, the result set now knows that all the Column/Label objects > corresponding to “id” are linked to that column and that’s how the lookup > proceeds. > > > >> I’m committing 2932 in a moment and I’m super really hoping I can put out >> 0.9.2 today but it’s easy for me to run out of time, but 0.9.2 is >> definitely due. >> > > That would be awesome! Incidentally though, would this labeling still work > once the fix is in? > > > all the existing mechanisms are maintained and I’ve just made some of the > matching logic a bit more liberal here, so should be fine. It’s all > committed if you want to try out the git master. > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
