On Wed, Nov 17, 2010 at 1:16 AM, Michael Bayer mike...@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