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.
