[sqlalchemy] Re: Problems with composite primary key and nested relations
Hi Michael, thanks for your effort, glad to hear that the issue is resolved! On 1 Aug., 02:37, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 31, 2009, at 7:20 PM, Michael Bayer wrote: I will investigate a way such that the dialect more intelligently selects the primary key column which recieves AUTOINCREMENT behavior. since lots of work has been going on with last_inserted_ids() in 0.6, which is soon going to trunk, this issue is fixed in 0.6. Your original test program works. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with composite primary key and nested relations
the issue is that the mysql dialect assumes cursor.lastrowid applies
to the first primary key column in the table. Build your tables like
this and it works:
table_b = Table('table_b', metadata,
Column('id', Integer(), nullable=False, primary_key=True),
Column('a_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('id', Integer(), primary_key=True),
Column('a_id', Integer(), nullable=False, primary_key=True),
Column('b_id', Integer(), nullable=False, primary_key=True),
Column('name', String(20), nullable=True),
ForeignKeyConstraint(['a_id', 'b_id'],
['table_b.a_id','table_b.id'], ondelete='CASCADE'),
)
I will investigate a way such that the dialect more intelligently
selects the primary key column which recieves AUTOINCREMENT behavior.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with composite primary key and nested relations
On Jul 31, 2009, at 7:20 PM, Michael Bayer wrote: I will investigate a way such that the dialect more intelligently selects the primary key column which recieves AUTOINCREMENT behavior. since lots of work has been going on with last_inserted_ids() in 0.6, which is soon going to trunk, this issue is fixed in 0.6. Your original test program works. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with composite primary key and nested relations
Christian Schwanke wrote:
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'),
)
# 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)
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. You need another
relation() on ItemC which expresses this, and you need to assign itemA1 to
ItemC explicitly, independently of the ItemB stuff.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with composite primary key and nested relations
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
-~--~~~~--~~--~--~---
