Jonathan Ellis wrote: > Well, you could specify the primaryjoin as described here: > http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_customjoin > > but I suspect that your existing mapper will Just Work if you switch > to a composite FK, rather than 3 keys on individual columns > > t_bovines = Table( 'bovines', metadata, > Column('id', Integer, primary_key=True), > Column('entrydate', Integer), > Column('key1', Integer), > Column('key2', Integer), > Column('key3', String), > Column('var', Integer), > Column('val', Integer), > ForeignKeyConstraint(['key1', 'key2', 'key3'], > ['enterprise.key1', 'enterprise.key2', 'enterprise.key3']) > ) > > t_entreprises = Table( 'entreprises', metadata, > Column('key1', Integer), > Column('key2', Integer), > Column('key3', String), > Column('lname', Unicode(30)), > Column('fname', Unicode(30)), > Column('street', Unicode(30)), > Column('country', String(1)), > Column('zip', String(5)), > Column('locality', Unicode(30)), > PrimaryKeyConstraint('key1', 'key2', 'key3') > ) > > # similarly adjust surfaces > > On 1/9/07, exhuma.twn <[EMAIL PROTECTED]> wrote: > > > > Hi, > > > > I have to load a table from 2 different data-sources witch each having > > 3 different primary keys. This is because those 2 datasources are > > already exported from 3 databases from an archaic application. > > > > >From those 2 datasources I created - after normalising - 3 new tables. > > The main table keeps the 3 primary keys as one composite primary key. > > The two other tables have those 3 fields as foreign keys. Now, if I map > > those table definitions onto a table with relations, sqlalchemy > > complains with the following error: > > > > """ > > sqlalchemy.exceptions.ArgumentError: Error determining primary and/or > > secondary join for relationship 'bovines' between mappers > > 'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. You > > should specify the 'primaryjoin' (and 'secondaryjoin', if there is an > > association table present) keyword arguments to the relation() function > > (or for backrefs, by specifying the backref using the backref() > > function with keyword arguments) to explicitly specify the join > > conditions. Nested error is "Cant determine join between 'entreprises' > > and 'bovines'; tables have more than one foreign key constraint > > relationship between them. Please specify the 'onclause' of this join > > explicitly." > > """ > > > > Ok, so I have to specify the "onclause". But how do I do that? For > > reference, here is the (non-working) data definition: > > > > t_bovines = Table( 'bovines', metadata, > > Column('id', Integer, primary_key=True), > > Column('entrydate', Integer), > > Column('key1', Integer, ForeignKey('entreprises.key1')), > > Column('key2', Integer, ForeignKey('entreprises.key2')), > > Column('key3', String, ForeignKey('entreprises.key3')), > > Column('var', Integer), > > Column('val', Integer), > > ) > > > > t_entreprises = Table( 'entreprises', metadata, > > Column('key1', Integer, primary_key=True), > > Column('key2', Integer, primary_key=True, default=0), > > Column('key3', String, primary_key=True), > > Column('lname', Unicode(30)), > > Column('fname', Unicode(30)), > > Column('street', Unicode(30)), > > Column('country', String(1)), > > Column('zip', String(5)), > > Column('locality', Unicode(30)), > > ) > > > > t_surfaces = Table( 'surfaces', metadata, > > Column('id', Integer, primary_key=True), > > Column('entrydate', Integer), > > Column('key1', Integer, ForeignKey('entreprises.key1')), > > Column('key2', Integer, ForeignKey('entreprises.key2')), > > Column('key3', String, ForeignKey('entreprises.key3')), > > Column('var', Integer), > > Column('val', Integer), > > ) > > > > metadata.create_all() > > > > class Bovine(object): > > pass > > > > class Surface(object): > > pass > > > > class Enterprise(object): > > def __repr__(self): > > return "[Entreprise %s %s %s]" % (self.key1, self.key2, > > self.key3) > > > > usermapper = mapper(Bovine, t_bovines) > > usermapper = mapper(Surface, t_surfaces) > > usermapper = mapper(Enterprise, t_entreprises, properties={ > > 'bovines': relation(Bovine), > > 'surfaces': relation(Surface) > > }) > > > > > > > > >
Magical! This worked :) Thanks a lot --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---