so u have a member, pointing to member_profile, pointing to all its attributes into separate tables? one way IMO is to map all other 50 tables into simple classes, then have member profile reference each of them, i.e. relation( .. uselist=False). Then, if u need all of them at once, request a eagerload - by default in the relation(), or keep that lazy and only specify eagerload'ing at query time. it depends on the usage patterns.
so far u'll have member.profile.gender.name or whatever. maybe u can make a property over gender.name to get that directly, i.e. member.profile.gender which is just shortcut to member.profile._gender.name (note _ in the start). i guess this can be slow - in worst case (all eagerloading) u'll be instantiating 50+objects on each member_profile. u can avoid all the object-per-attribute stuff via column_property() selectable-attributes, but i dont know if these can scale into eagerload. see if anyone has better suggestion. ciao svilen On Tuesday 03 February 2009 17:29:58 Gloria W wrote: > Hi all, thanks for this really helpful list. > > My question now involves trying to figure out the correct way to > map this legacy database to SqlAlchemy objects using the > declarative_base model. > > This database has two main tables, members, and member_profiles, > where a memberID is the foreign key from members to > member_profiles. > > It then has about thirty other tables such as this one, called > member_gender. member_gender contains a genderID, and a string > representing 'Male' or 'Female'. > genderID is a foreign key in member_profiles, pointing to > member_gender. > > So the member_profiles table contains many of these xxxID fields > which are foreign keys into many other tables which act as > descriptor tables, abstracting (to the point of overkill) just > about every bit of data in this database. > > The Left Join on member_profiles, to get all of the data I need in > one fell swoop, is over 50 lines long. > > I want to use the declarative_base class model to represent this > database in SqlAlchemy, but here are problems I face: > > 1: Multiple inheritance is not allowed, so I cannot create a class > for every table and inherit them all into one master class. > 2: If I create three classes, for example, say Members, > MemberProfiles, and Gender, and use Members as the base class for > MemberProfiles, and MemberProfiles as the base class for Gender, > the Members 'class members' are not visible via Gender. The > inheritance seems to only go two objects deep. > > I want to be able to access the gender 'descriptor' field > containing the string 'Female' from one master object, instead of > accessing 'genderID'. I want to be able to do this for all foreign > keys in this member_profiles table. > > Is there a way to properly make such a model using the > declarative_base methodology, which allows for three or four levels > of foreign key resolution in some 'master' object, containing all > of the fields I want? > > Many many thanks in advance, > Gloria > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
