Hi all, Since SA was proving to be difficult to get working, and I was spending way more time just trying to get it working than I was actually running queries and outputting the results, I thought I'd give Pyodbc a shot. Within a couple days, the query was working perfectly. I'll post it below, as I'd be curious how this could be made easier using SA. I don't know that I'll use SA for this project since it's working so well in Pyodbc, but I'm curious all the same. So, no hurry on this, it's only for my own interest. Anyway, the query I finally came up with is below. It returns multiple rows for items that have attributes, but other than that it works great. A for loop with a check on the current ID takes care of making multiple rows for the same item into a single row (recall this is all going to a spreadsheet).
itemsQuery = """ select items.itm_id as itemID, items.itm_proddesc as itemTitle, items.itm_num as itemNumber, items.itm_listprice1 as msrp, items.itm_dftuom as itemUnitOfMeasure, items.itm_manufitem as itemManufacturer, items.itm_vendornum as itemVendorNumber, items.itm_weight as itemWeight, items.itm_width as itemWidth, items.itm_length as itemLength, items.itm_height as itemHeight, attachments.description as description, imagePaths.imagePath1 as imagePath1, imagePaths.imagePath2 as imagePath2, imagePaths.imagePath3 as imagePath3, attributes.attributeName as attributeName, attributes.attributeValue as attributeValue, vendor.vendorName as vendorName from ( select itm_id, itm_proddesc, itm_num, itm_vendornum, itm_listprice1, itm_length, itm_width, itm_height, itm_weight, itm_dftuom, itm_manufitem from item where itm_webflag <> 'Y' and itm_suspflag <> 'Y' ) items left outer join ( select distinct attr_desc as attributeName, attr_value as attributeValue, itm_id from attributevalueassign join attribute on attribute.attr_id = attributevalueassign.attr_id join attributevalue on attributevalue.attr_value_id = attributevalueassign.attr_value_id where attributevalueassign.itm_id = itm_id ) attributes on attributes.itm_id = items.itm_id left outer join ( select PVUS15 as vendorName, PVVNNO as vendorNumber, itm_id from VENDR join item on item.itm_id = itm_id ) vendor on vendor.vendorNumber = items.itm_vendornum and vendor.itm_id = items.itm_id left outer join ( select attach_text.att_text as description, itm_id from assignment join attachment on attachment.att_id = assignment.att_id join attach_text on attach_text.att_id = assignment.att_id where assignment.itm_id = itm_id ) attachments on attachments.itm_id = items.itm_id left outer join ( select attachment.att_path as imagePath1, attachment.att_path2 as imagePath2, attachment.att_path3 as imagePath3, itm_id from assignment join attachment on attachment.att_id = assignment.att_id ) imagePaths on imagePaths.itm_id = items.itm_id """ On 3/21/16, Simon King <si...@simonking.org.uk> wrote: > Can you extract your code into a single standalone script that demonstrates > the problem? This should be possible even with automap; the script can > start by creating just the tables that are involved in this problem > (ideally in an in-memory sqlite db), then use automap to map classes to > those tables. > > Simon > > On Mon, Mar 21, 2016 at 3:12 PM, Alex Hall <ah...@autodist.com> wrote: > >> Wow, thanks guys, especially for the sample code! I'm trying to use >> the example (and fully understand it at the same time) but am running >> into an error. This is the same error that made me look for a way >> other than this last week. >> >> sqlalchemy.exc.InvalidRequestError: when initializing mapper >> Mapper|assignmentTable|assignment, expression 'item' failed to to >> locate an item (name 'item' is not defined). If this is a class name, >> consider adding this relationship() to the >> <class.__main__.assignmentTable> class after both dependent classes >> have been defined. >> >> This all starts from the line where my query begins: >> >> items = session.query(itemTable)\ >> >> Again, I'm using automap. I put the class definitions in the same >> place I put my vendor table definition last week, where it worked >> perfectly. That's just after I set >> base = automap_base() >> but before I reflect anything. I can paste the full code if you want, >> but it's pretty long. >> >> On 3/17/16, Mike Bayer <clas...@zzzcomputing.com> wrote: >> > >> > >> > On 03/17/2016 03:11 PM, Alex Hall wrote: >> >> Hello all, >> >> It seems like I can't go a day without running into some kind of wall. >> >> This one is a conceptual one regarding foreign keys. I have to somehow >> >> get the same FK column in table A pointing to IDs in tables B and C. >> > >> > So a real foreign key constraint is not capable of this. Repurposing a >> > single column to occasionally point to one table or another is a famous >> > anti-pattern I've spoke of much (warning, this is *extremely* old, but >> > the general idea still holds): >> > >> > >> http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ >> > >> > >> > I have an updated version of all the various "polymoprhic association" >> > examples in SQLAlchemy itself at >> > >> http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#module-examples.generic_associations >> . >> > >> > This includes the "single column pointing to multiple tables" hack, >> > as >> > well as three other versions of the same business object geometry which >> > preserve relational integrity within the schema design. >> > >> >> >> >> At one person's suggestion, I'm making classes for my tables, even >> >> though I'm using automap. This is to let me stop doing a ton of joins, >> >> making querying much easier... I hope! I'm defining all the foreign >> >> keys between my tables manually. For instance: >> >> >> >> class item(base): >> >> __tablename__ = "item" >> >> itm_id = Column(Integer, primary_key=True) >> >> vendornum = Column(String, ForeignKey(VENDR.PVVNNO)) >> >> >> >> class vendorTable(base): >> >> __tablename__ = "VENDR" >> >> PVVNNO = Column(String, primary_key=True) >> >> >> >> If I've understood correctly, I'll now be able to say >> >> item.vendornum.vendor_full_name >> >> to get the vendor's full name for any item. >> >> >> >> Here's the problem. Items have attachments, and attached text, >> >> respectively held in attach and attach_text tables. Binding them to >> >> items is a table called assignment. Assignment is pretty >> >> straightforward, with an itm_id and an attachment id (att_id). The >> >> trouble is that this att_id occurs in both attach and attach_text. I >> >> can make att_id a foreign key to one table or the other, but I'm not >> >> sure how to make it go to both tables. >> > >> > the "generic_fk" example illustrates a pattern for working with this. >> > >> > Getting this all to work with automap is another layer of complexity, >> > you certainly want all of this part of it laid out before you reflect >> > the rest of the database columns. >> > >> > >> >> >> >> class assignmentTable(base): >> >> __tablename__ = "assignment" >> >> itm_id = Column(Integer, ForeignKey(item.itm_id)) >> >> #the following column has to point to attach_text.att_id AS WELL >> >> att_id = Column(Integer, ForeignKey(attachment.att_id)) >> >> seq_num = Column(Integer) >> >> asn_primary = Column(Integer, nullable=True) >> >> >> >> class attachmentTable(base): >> >> __tablename__ = "attachment" >> >> att_id = Column(Integer, primary_key=True) >> >> >> >> class attachmentTextTable(base): >> >> __tablename__ = "attach_text" >> >> att_id = Column(Integer, primary_key=True) >> >> >> > >> > -- >> > 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. >> > > -- > 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.
