Im sorry, I misread that ItemC links to ItemB only since I missed the
a_id coluimn on B. The issue has to do with MySQLdb's autoincrement
not being reported to lastrowid properly (or the autoincrement not
firing at all, I dont have time to check right now, but you can
experiment by saying "result = table_b.insert().values(...)" and then
see what result.last_inserted_ids() says.), which is probably due to
the composite primary key. the example works if you set the "id"
fields explicitly. There is no issue withing the ORM regarding the
foreign key values being translated over, its likely a DBAPI quirk (or
maybe something with last_inserted_ids() on MySQL, less likely).
working example:
import logging
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
table_a = Table('table_a', metadata,
Column('id', Integer(), primary_key=True),
Column('name', String(20), nullable=True),
)
table_b = Table('table_b', metadata,
Column('a_id', Integer(), nullable=False, primary_key=True),
Column('id', Integer(), nullable=False, primary_key=True),
Column('name', String(20), nullable=True),
ForeignKeyConstraint(['a_id'], ['table_a.id'],ondelete='CASCADE'),
)
table_c = Table('table_c', metadata,
Column('a_id', Integer(), nullable=False, primary_key=True),
Column('b_id', Integer(), nullable=False, primary_key=True),
Column('id', Integer(), primary_key=True),
Column('name', String(20), nullable=True),
ForeignKeyConstraint(['a_id', 'b_id'],
['table_b.a_id','table_b.id'], ondelete='CASCADE'),
)
# Models
class ItemA(object):
def __init__(self, name):
self.name = name
class ItemB(object):
def __init__(self, name, id):
self.name = name
self.id = id
class ItemC(object):
def __init__(self, name, id):
self.name = name
self.id = id
# Mapping
mapper(ItemA, table_a, properties={'b': relation(ItemB,
backref="a",cascade="all, delete-orphan")})
mapper(ItemB, table_b, properties={'c': relation(ItemC,
backref="b",cascade="all, delete-orphan")})
mapper(ItemC, table_c)
# Init engine
engine = create_engine('mysql://scott:ti...@localhost/test', echo=True)
metadata.drop_all(engine)
metadata.create_all(engine)
session_factory = scoped_session(sessionmaker(bind=engine))
session = session_factory()
itemA1 = ItemA(name = 'ItemA1')
itemB1 = ItemB(name = 'ItemB1', id=1)
c1 = ItemC(name = 'ItemC1', id=1)
itemB1.c.append(c1)
itemA1.b.append(itemB1)
session.add(itemA1)
session.commit()
On Jul 30, 2009, at 5:50 PM, Christian Schwanke wrote:
>
> Thanks for your quick answer. However, I still don't understand why
> another relation is necessary.
>
>> relation ItemC.b takes care of the assignment of table_c.b_id, but
>> there
>> is nothing here for SQLA to know about table_c.a_id.
>
> table_c.a_id is *not* the problem - table_c.b_id is not assigned and
> that value has nothing to do with itemA at all.
> In my opinion the ForeignKeyConstraint on Table C contains all
> information SQLA should need in order to populate the primary key of
> C. The relation from B->A works essentially the same as C->B with the
> only difference that B has one autoincremented PK column and one
> assigned from A, while C has one autoinc column and two values
> assigned from B.
> In both cases, a FK constraint is present, specifying the relation
> between the tables. Looking at the FK constraints, SQLA should be able
> to determine the order in which the models are to be persisted (A->B-
>> C) and then assign all autoincremented values correctly.
>
>> You need another relation() on ItemC which expresses this, and you
>> need to assign itemA1 to
>> ItemC explicitly, independently of the ItemB stuff.
>
> This might work, but this doesn't make sense to me, because ItemC
> shouldn't really have to know about itemA because the PrimaryKey of C
> only depends on the primarykey of B. The fact, that the PK on B is in
> turn dependent on A should not be a factor.
>
> In fact, I modified the testcase and removed a_id from table_c
> altogether - The autoincremented id of itemB is still not set on the
> C-
> Model, so I'm still thinking this is a bug with nested relations - the
> fact that the primary key is composed of multiple columns does not
> seem to contribute to the problem.
>
>
>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
