Hi Michael, I am trying to figure out the two suggestions you did, and not getting very far. Some basic questions:
- if A, B, C are mapped classes, can you do A.join(B, A.id == B.id).join(C, B.id == C.id).join( .... ? - Would using join in such a way make access to mapped attributes in one of the joined tables excessively slow? - What is the difference between using association_proxy and relationship(... secondary = .., ..., secondaryjoin = ...)? - in the example in poly_assoc_generic.py, is there a way to define an attribute on address that returns a list with both "orders" and "users" with that address (and be able to append that list) ? Please help, Lars On Apr 11, 8:45 pm, Lars <[email protected]> wrote: > Hi again, > > On Apr 10, 11:17 pm, Michael Bayer <[email protected]> wrote:> On Apr > 10, 2011, at 1:27 PM, Lars wrote: > > > > Hi Michael, > > > > Thank you for the suggestions. > > > > I noticed that all the foreign keys to superclasses are identical > > > (same id number). What would be the disadvantage of using only primary > > > keys and using those in setting up relationships (compared to using > > > foreign keys)? > > > When two columns in a relational database, one being a primary key, the > > other being possibly a primary key, are logically constrained to have the > > same value, that's known as a foreign key. It's then best practice to > > actually place a real foreign key constraint in the database to enforce > > this at the data level. SQLite and MySQL by default don't enforce it, > > though. SQLAlchemy doesn't particularly care if an actual constraint is > > defined in the database, it can be told to join on any combination of > > columns. The presence of a foreign key within table metadata just makes > > this automatic. > > Does that mean that if you don't set the foreign key constraint and > you want to be able to set an attribute, you also have to write code > to copy the primary key of the parent to the child? > > > > If I use a root base class (similar to "object" in python) and add a > > > type column/attribute to refer to the actual class of the object/ > > > record, is there an elegant way to get/set/del the polymorphic > > > attribute object of the correct class using this type? > > > Which table is this "type" column present in, base1 or base2 ? > > Neither, I was thinking of using a class that is the root base class > of all classes (not present in code above) to store the type and the > "primary key" of all objects. This "type" should be accessible in all > classes, since they would all be direct or indirect subclasses of this > root class. Say that I store all classes in a dictionary called > registry, then registry[type] would give the actual class of the > object identified with the primary key in the root class table. This > key would also identify the correct record in all superclasses of this > class, e.g. to be used in a join over these classes. > > (hope this is somewhat clear ...) > > > > > > Would it be possible to redefine query() using this type to first > > > resolve the correct table and use the query method SA provides on that > > > (without this resulting in otherwise changing the API) ? > > > but what's the "correct" table, base1, base2, claz ? with multiple > > inheritance the path is not a straight line. If you have a mostly straight > > inheritance model with an occasional offshoot, say its base1 -> subbase1 -> > > (subbase1 , base2) -> claz, I'd use traditional inheritance and have just > > "base2" via relationship(). > > The correct table would be the table corresponding to the value in the > type column. I am working on a framework where others will design the > data structure and would like to use a uniform metadata model to avoid > extra choices for the designer. > > > > Cheers again, Lars > > > > > > > > > > > > On Apr 7, 8:42 pm, Michael Bayer <[email protected]> wrote: > > >> On Apr 7, 2011, at 2:30 PM, Lars wrote: > > > >>> Hello, > > > >>> I am pretty determined to find a way to get (a simplified version of) > > >>> multiple inheritance working with SA. The simplification lies in that > > >>> no overriding of attributes will be possible (but I don't know whether > > >>> that is significant). I was thinking of a schema as follows: > > > >>> --------------------------------------------------------------------------- > > >>> ----------- > > > >>> metadata = MetaData() > > > >>> base1_table = Table("base1_table", metadata, > > >>> Column('id', Integer, primary_key=True), > > >>> Column('str', String) > > >>> ) > > > >>> base2_table = Table("base2_table", metadata, > > >>> Column('id', Integer, primary_key=True), > > >>> Column('int', Integer) > > >>> ) > > > >>> claz1_table = Table("claz1_table", metadata, > > >>> Column('id', Integer, primary_key=True), > > >>> Column('base1_id', None, ForeignKey('base1_table.id')), > > >>> Column('base2_id', None, ForeignKey('base2_table.id')), > > >>> Column('assoc_id', None, ForeignKey('assoc_table.id')) > > >>> ) > > > >>> assoc_table = Table("assoc_table", metadata, > > >>> Column('id', Integer, primary_key=True), > > >>> Column('name', String(50), nullable=False), > > >>> Column('type', String(50), nullable=False) > > >>> ) > > > >>> base3_table = Table("base3_table", metadata, > > >>> Column('id', Integer, primary_key=True), > > >>> Column('assoc_id', None, ForeignKey('assoc_table.id')), > > >>> Column('bool', Boolean) > > >>> ) > > > >>> claz2_table = Table("claz2_table", metadata, > > >>> Column('id', Integer, primary_key=True), > > >>> Column('base3_id', None, ForeignKey('base3_table.id')), > > >>> Column('date', Date) > > >>> ) > > > >>> class base1(object): > > >>> pass > > >>> class base2(object): > > >>> pass > > >>> class base3(object): > > >>> pass > > >>> class claz1(base1, base2): > > >>> pass > > >>> class claz2(base3): > > >>> pass > > > >>> # do mappings, relationships and e.g. be able to > > > >>> c1 = claz1(str = "hello", int = 17) > > >>> setattr(c1, name, claz2(bool = True, date = Date(2010,9,10))) > > > >> You can just forego the "inherits" flag and map each class to the > > >> appropriate join or base table. claz1 would be mapped to a join of the > > >> three tables involved. The difficult part of course is the query > > >> side, if you're looking to query "base1" or "base2" and get back "claz1" > > >> objects. > > > >> Alternatively, each class can be mapped to one table only, and > > >> relationship() used to link to other tables. Again if you don't use > > >> the "inherits" flag, you can maintain the class hierarchy on the Python > > >> side and use association proxies to provide local access to attributes > > >> that are normally on the related class. This would still not give you > > >> polymorphic loading but would grant a little more flexibility in which > > >> tables are queried to start. > > > >>> --------------------------------------------------------------------------- > > >>> -------------------------------------- > > > >>> I am still pretty new to SA. Can anyone give me any hints, tips, > > >>> issues with this scheme (e.g. about how to do the mappings, > > >>> descriptors, etc)? > > > >>> The step after will be to write factory functions/metaclasses to > > >>> generate these dynamically. > > > >>> Multiple inheritance is very important for my use case. > > > >>> Cheers, Lars > > > >>> -- > > >>> You received this message because you are subscribed to the Google > > >>> Groups "sqlalchemy" group. > > >>> To post to this group, send email to [email protected]. > > >>> To unsubscribe from this group, send email to > > >>> [email protected]. > > >>> For more options, visit this group > > >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "sqlalchemy" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group > > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
