Dynamic mapper issue

[Kalium]

Hi, I'm coming to grief trying to create a Mapper on the fly.

At first I tried this

class Dynamic(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'.

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])

fields_to_select are the fields I actually need, and they look
something like [table1.c.id,table2.c.id,table3.c.id]. If I want to I
imagine I could individually attach a label() to these and that would
overcome any conflicts with the SQL query. That's not the problem
though, as it gives me this error

mapper Mapper|Dynamic|{ANON 158092684 anon} could not assemble any
primary key columns for mapped table '{ANON 158092684 anon}'

I've looked around and read the docs, been through the newgroup but
can't seem to figure out where I've gone wrong. Any ideas ?

Cheers

[Michael Bayer]

On Nov 26, 2009, at 2:05 AM, Kalium wrote:

> Hi, I'm coming to grief trying to create a Mapper on the fly.
>
> At first I tried this
>
> class Dynamic(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, the mapper for "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.

[Kalium]

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);

> 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.