declarative one to many relationship with composite primary key

[Adrien]

Hi list,

Sorry if this is trivial, I'm relatively new to sqlalchemy.
I'm trying to set a one to many relationship between class Foo and
class Bar (ie Foo should have a list of Bars). Foo has a composite
primary key.

#################################
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()

class Foo(Base):
__tablename__ = "foo"
one = Column(Integer, primary_key=True)
two = Column(Integer, primary_key=True)

class Bar(Base):
__tablename__ = "bar"
id = Column(Integer, primary_key=True)
one_id = Column(Integer, nullable=False)
two_id = Column(Integer, nullable=False)

ForeignKeyConstraint(["one_id", "two_id"], ["foo.one", "foo.two"])
foo = relationship("Foo", backref = "bars")


metadata = Base.metadata

engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker

# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
session = Session()

foo = Foo()
foo.one = 1
foo.two = 2
session.add(foo)
session.commit()
#############################

I get the following message:

sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship Bar.foo. Specify a
'primaryjoin' expression.


I tried to change the relationship line to:
foo = relationship("Foo", backref = "bars", primaryjoin=and_(one_id ==
Foo.one, two_id==Foo.two ) )

but then I get this message:

sqlalchemy.exc.ArgumentError: Could not determine relationship
direction for primaryjoin condition 'bar.one_id = foo.one AND
bar.two_id = foo.two', on relationship Bar.foo. Ensure that the
referencing Column objects have a ForeignKey present, or are otherwise
part of a ForeignKeyConstraint on their parent Table.

Thank you for your help!

[Michael Bayer]

ForeignKeyConstraint needs to go into __table_args__ when using declarative.

http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#table-configuration

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Adrien Saladin]

On Wed, Nov 17, 2010 at 1:16 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> ForeignKeyConstraint needs to go into __table_args__ when using declarative.
>
> http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#table-configuration

Thanks for the note. I have updated my test script to use
__table_args__ but the error remains the same (see script and ouput
below).

I then tried with the hybrid approach
(http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#using-a-hybrid-approach-with-table)
which works well.
Am I again doing something wrong with declarative ?

Thanks,

#################################
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
__tablename__ = "foo"
one = Column(Integer, primary_key=True)
two = Column(Integer, primary_key=True)

class Bar(Base):
__tablename__ = "bar"

__table_args__ = ( ForeignKeyConstraint(['one_id', 'two_id'],
['foo.one', 'foo.two']) )

id = Column(Integer, primary_key=True)
one_id = Column(Integer, nullable=False)
two_id = Column(Integer, nullable=False)

foo = relationship("Foo", backref = "bars")

metadata = Base.metadata

engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker

# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
session = Session()

foo = Foo()
foo.one = 1
foo.two = 2
session.add(foo)
session.commit()

#############################

2010-11-17 14:56:01,309 INFO sqlalchemy.engine.base.Engine.0x...9690
PRAGMA table_info("foo")
2010-11-17 14:56:01,309 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690
PRAGMA table_info("bar")
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690
CREATE TABLE foo (
one INTEGER NOT NULL,
two INTEGER NOT NULL,
PRIMARY KEY (one, two)
)

2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690 COMMIT
2010-11-17 14:56:01,311 INFO sqlalchemy.engine.base.Engine.0x...9690
CREATE TABLE bar (
id INTEGER NOT NULL,
one_id INTEGER NOT NULL,
two_id INTEGER NOT NULL,
PRIMARY KEY (id)
)

2010-11-17 14:56:01,311 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,311 INFO sqlalchemy.engine.base.Engine.0x...9690 COMMIT
Traceback (most recent call last):
File "compositePrimaryKey_decl.py", line 39, in <module>
foo = Foo()
File "<string>", line 4, in __init__
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/state.py",
line 93, in initialize_instance
fn(self, instance, args, kwargs)
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py",
line 2357, in _event_on_init
instrumenting_mapper.compile()
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py",
line 805, in compile
mapper._post_configure_properties()
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py",
line 834, in _post_configure_properties
prop.init()
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/interfaces.py",
line 493, in init
self.do_init()
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/properties.py",
line 840, in do_init
self._determine_joins()
File "/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/properties.py",
line 969, in _determine_joins
% self)

sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship Bar.foo. Specify a

'primaryjoin' expression. If this is a many-to-many relationship,
'secondaryjoin' is needed as well.

The script below works with the hybrid declarative approach:

#################################
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
__tablename__ = "foo"
one = Column(Integer, primary_key=True)
two = Column(Integer, primary_key=True)

bartable = Table("bar", Base.metadata,
Column("id", Integer, primary_key=True),
Column("one_id", Integer, nullable=False),
Column("two_id", Integer, nullable=False),
ForeignKeyConstraint(['one_id', 'two_id'], ['foo.one', 'foo.two']),
)

class Bar(Base):
__table__ = bartable

foo = relationship("Foo", backref = "bars")

metadata = Base.metadata

engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker

# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
session = Session()

foo = Foo()
foo.one = 1
foo.two = 2
session.add(foo)
session.commit()

bar = Bar()
bar.foo = foo
session.add(bar)

session.commit()

#############################

[Michael Bayer]

On Nov 17, 2010, at 9:07 AM, Adrien Saladin wrote:

> On Wed, Nov 17, 2010 at 1:16 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> ForeignKeyConstraint needs to go into __table_args__ when using declarative.
>>
>> http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#table-configuration
>
> Thanks for the note. I have updated my test script to use
> __table_args__ but the error remains the same (see script and ouput
> below).

OK its actually a huge SQLA bug that an error isn't raised for that, which is surprising to me, so I created and resolved #1972 in r67d8f4e2fcb9. __table_args__ is expected to be a tuple or dict, so now an error is raised if it's not. (x) isn't a tuple.

here's the correct form:

class Bar(Base):
__tablename__ = "bar"

__table_args__ = ( ForeignKeyConstraint(['one_id', 'two_id'], ['foo.one', 'foo.two']),{} )

[Adrien Saladin]

On Wed, Nov 17, 2010 at 4:58 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> OK its actually a huge SQLA bug that an error isn't raised for that, which is surprising to me, so I created and resolved #1972 in r67d8f4e2fcb9.   __table_args__ is expected to be a tuple or dict, so now an error is raised if it's not.   (x) isn't a tuple.
>

Thanks for the quick reply, the patch and the syntax correction of my code.

Regards,