Thanks Michael, I'm going to take a closer look at this and rework my code.
Cheers Raymond On Nov 27 2009, 12:56 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Nov 26, 2009, at 2:05 AM, Kalium wrote: > > > Hi, I'm coming to grief trying to create aMapperon the fly. > > > At first I tried this > > > classDynamic(object): > > pass > > >Dynamic.mapper=mapper(Dynamic, self.j); > > > self.j is a join statement. This now maps all the columns from the > > tables in the join statement. Which is fine. Except that in the SQL > > query it looks something like "SELECT table1.id AS id, table2.id AS > > id etc etc". So that won't work as the columns don't have alias'. > > the join() by itself is automatically disambiguating so you shouldn't have > such an issue: > > from sqlalchemy import * > from sqlalchemy.orm import * > > m = MetaData(e) > > t1 = Table('t1', m, Column('id', Integer, primary_key=True)) > t2 = Table('t2', m, Column('id', Integer, primary_key=True)) > > j = t1.join(t2, t1.c.id==t2.c.id) > > # j now has j.c.t1_id, j.c.t2_id > print j.c.t1_id, j.c.t2_id > > returns: > > t1.id t2.id > > now map: > > class C(object): > pass > > mapper(C, j) > > print create_session().query(C) > > returns: > > SELECT t1.id AS t1_id, t2.id AS t2_id > FROM t1 JOIN t2 ON t1.id = t2.id > > the map of C will have a single attribute "id" on it, which is based on that > name. in this case, that's fine since the two "id" columns are equated, and > you'd want them to be under one attribute. If you didn't, you can set up the > mapping explicitly with the names you prefer: > > m = MetaData(create_engine('sqlite://')) > > t1 = Table('t1', m, Column('id', Integer, primary_key=True), Column('data', > String)) > t2 = Table('t2', m, Column('id', Integer, primary_key=True), Column('data', > String)) > m.create_all() > > j = t1.join(t2, t1.c.id==t2.c.id) > > class C(object): > pass > > mapper(C, j, properties={ > 't1data':t1.c.data, > 't2data':t2.c.data > > }) > > s = sessionmaker()() > c1 = C() > c1.t1data='foo' > c1.t2data='bar' > s.add(c1) > s.commit() > print s.execute("select * from t1 join t2 on t1.id=t2.id",mapper=C).fetchall() > > returns: > > [(1, u'foo', 1, u'bar')] > > above, themapperfor "C" has a single attribute "id" representing t1.c.id and > t2.c.id, but separate "t1data" and "t2data" attributes representing t1.c.data > and t2.c.data, respectively. > > > > > Next I tried something like this > > >Dynamic.mapper=mapper(Dynamic,select(fields_to_select,from_obj= > > [self.j]),primary_key=[table1.c.id,table2.c.id,table3.c.id]) > > here, you've mapped to a select() construct - that is your selectable. The > mistake here is that you used columns for "primary_key" which are from your > tables - which are represented within the internal FROM clause of your > select() but are not the columns it publically exposes in its columns clause. > Any columns you specify to primary_key must be in terms of the mapped > selectable's "exported" columns clause, i.e. myselect.c.table1_id, > myselect.c.table2_id, myselect.c.table3_id. Specifying use_labels on your > select() construct will allow the column names it exports in its "c" > collection to be prepended with the table name, the same way that the join() > does automatically. > > But also, if your select() already has all the PK fields from the three > tables represented, the primary key columns will automatically be pulled out > in the mapping so there is probably no need for the primary_key argument.
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.