On Wed, Mar 16, 2016 at 1:07 PM, Alex Hall <ah...@autodist.com> wrote:
> Hello all, > I'm running a different query than yesterday. Before, I had something like: > > items = session.query(itemTable, attachmentTable, attachmentTextTable, > assignmentTable, attributeTable, attributeValueTable, > attributeValueAssignmentTable, vendorTable)\ > .filter(attachmentTable.itm_id == itemTable.itm_id)\ > #and so on, a bunch of .filter calls > > Then, in the loop iterating over the results, I could do this: > > for result in queryResults: > itemID = result.item.itm_id > Because you wrote "session.query(itemTable, attachmentTable, attachmentTextTable)", the results that you get back from the query are like a tuple with 3 items corresponding to the 3 tables that you queries. result[0] would be the data from itemTable, result[1] is from attachmentTable, and result[2] is from attachmentTextTable. It also supports name-based access, which is why you were able to refer to "result.item" and "result.attach_text". > > Now that I'm using a bunch of outer left joins, that code is suddenly > not working. I get an error when I say > result.item.itm_id > AttributeError: 'item' object has no attribute 'item' > > The problem is that my query starts out with only one table passed to > session.query(), not all of them. Thus my result is of type 'item', > which is the table passed in. That would be okay, except that I need > to access values of other tables in the result, so even if I change > id = result.item.itm_id > to > id = result.itm_id > When I then say > description = result.attach_text.att_value > AttributeError: 'item' object has no attribute 'attach_text' > > First, it helps to be precise about your terminology. SQLAlchemy distinguishes between the object representing a table, and a class that you are mapping to that table. You've talked about using automapper in the past, so I assume you are passing a mapped class, not a table, to session.query(). When you pass a single mapped class, the results you get back are instances of that class. > I know why it doesn't. What I don't know is how to get my query > results to hold all the information from all the tables, or how to > access it if they do already, but in a different way than before. My > new query is this: > > items = session.query(itemTable)\ > .outerjoin(vendorTable, vendorTable.PVVNNO == itemTable.itm_vendornum)\ > .outerjoin(assignmentTable, assignmentTable.itm_id == itemTable.itm_id)\ > .filter(assignmentTable.att_id == attachmentTable.att_id)\ > .outerjoin(attachmentTextTable, assignmentTable.att_id == > attachmentTextTable.att_id)\ > .outerjoin(attributeValueAssignmentTable, > attributeValueAssignmentTable.itm_id == itemTable.itm_id)\ > .outerjoin(attributeTable, attributeTable.attr_id == > attributeValueAssignmentTable.attr_id)\ > .filter(attributeValueTable.attr_value_id == > attributeValueAssignmentTable.attr_value_id)\ > .yield_per(1000) > > I've also tried the same query, but with the first line changed to: > items = session.query(itemTable, attachmentTable, attachmentTextTable, > assignmentTable, attributeTable, attributeValueTable, > attributeValueAssignmentTable, vendorTable)\ > > The problem here is that, while result.item.* works as expected, other > tables don't. For instance, result.attach_text.att_value yields an > AttributeError, 'None' type object has no attribute att_value. > Clearly, the other tables are in the result, but they're all None. I > expected something like that, and only added them back in to see if it > might help, but since I call query().outerjoin() I didn't think it > would work. > > I should note that I renamed most of the tables by assigning variables > to base.classes.tableName, which is why I'm using "itemTable" here, > but in getting attributes of results I use just "item". The 'item' > table is called 'item', but I assigned it to a variable called > 'itemTable', just for clarity in the script. > > Is there a way to access the values of a query like this? At the very > least, is there a way I can print out all the objects the result > object has, so I can work out what to do? Thanks for any help! > > Normally you would do this by setting up relationships between your mapped classes, so that when you access "item.attach_text", SQLAlchemy will go and fetch the related object: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#building-a-relationship Once you've got the relationship in place, you can set options on the query to control whether to load the related objects immediately, or to wait until they are accessed: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#eager-loading Automap may have already set up relationships between your classes. The way it does this is described here: http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#relationship-detection Hope that helps, Simon -- 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. For more options, visit https://groups.google.com/d/optout.
