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.
