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.
