Hi,
I've ran into a problem when using a composite primary key with
auto_incremented values and nested relations. My scenario is as
follows:
I have three models A, B and C, where A has a one-to-many relation to
B and B has a one-to-many relation to C.
A has a standard primarykey consisting of one integer column that is
autoincremented.
B has a combined primary key using the foreign key to A.id as the
first part and another autoincremented id column as the second part of
the key.
Finally, C has a combined primary key consisting of the A.id and B.id
and another autoincremented id column.
I create an instance of A, add a new instance of B to the "b"-
attribute of A and then call add the A-instance to the session and
call commit().
SQLAlchemy correctly sets the autogenerated ID value of A on the B-
instance and saves everything to the database, so if the A instance
gets an ID of 1, the a_id-column in the B-table is populated
correctly.
So far, nothing unusual.
Now I repeat the first use case but I also append a brand new instance
of C to the "c"-attribute of the B instance (my object graph looks
something like this: A.b[B.c[C]]). When I add the A instance to the
session and call commit(), the A and B models are correctly saved like
before.
However, the primary key on the C model is broken: SQLAlchemy does set
the a_id-column value of the combined key but does not set the
autoincremented value of the B.id column on the b_id-column
(Ironically, SQLAlchemy logs a warning message, that the column b_id
does not have a default value).
Is this a bug in the SQLAlchemy mapper or is there something I can do
about it ?
I've created a small test case to demonstrate the behaviour (using
Mysql):
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):
self.name = name
class ItemC(object):
def __init__(self, name):
self.name = name
# 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://localhost/sqltest', connect_args=
{'user':'root', 'passwd':''}, echo=True)
engine_logger = sqlalchemy.log.instance_logger(engine)
engine_logger.setLevel(logging.DEBUG)
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')
itemB1.c.append(ItemC(name = 'ItemC1'))
itemA1.b.append(itemB1)
session.add(itemA1)
session.commit()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
