Re: [sqlalchemy] declarative one to many relationship with composite primary key
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
Re: [sqlalchemy] declarative one to many relationship with composite primary key
On Nov 17, 2010, at 9:07 AM, Adrien Saladin wrote:
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).
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']),{} )
id = Column(Integer, primary_key=True)
one_id = Column(Integer, nullable=False)
two_id = Column(Integer, nullable=False)
foo = relationship(Foo, backref = bars)
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
Re: [sqlalchemy] declarative one to many relationship with composite primary key
On Wed, Nov 17, 2010 at 4:58 PM, Michael Bayer mike...@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, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] declarative one to many relationship with composite primary key
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!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] declarative one to many relationship with composite primary key
ForeignKeyConstraint needs to go into __table_args__ when using declarative.
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#table-configuration
On Nov 16, 2010, at 6:28 PM, Adrien wrote:
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!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
