That would certainly work. :) Would that offer any benefits over pyodbc, since I wouldn't have the mapping (which was taking all the time I was spending with SA)?
On 3/25/16, Mike Bayer <[email protected]> wrote: > > > On 03/25/2016 05:20 PM, Alex Hall wrote: >> 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. > > > like this: > > from sqlalchemy import create_engine > > engine = create_engine("mssql+pyodbc://dsn") > > result = engine.execute(itemsQuery) > > > > > > 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected]. >>>>> To post to this group, send email to [email protected]. >>>>> 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 [email protected]. >>>> To post to this group, send email to [email protected]. >>>> 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 [email protected]. >>> To post to this group, send email to [email protected]. >>> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
