If I define all the relationships as suggested, I could do result.itm_id or result.attribute_value and it would all work? Would I still need to specify, in my initial query, things like .filter(itemTable.itm_id = attachmentAssignmentTable.itm_id\ .filter(attachmentTable.att_id = attachmentAssignmentTable.att_id)
to get all attachments assigned to a given item? I'll read more about this and play with it, but I wanted to ask here as well in case someone sees that the design of this database will cause problems with relationships. On 3/16/16, Simon King <si...@simonking.org.uk> wrote: > 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. > -- 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.
