Re: [sqlalchemy] relationship problem
Julien Cigar wrote:
>
> Hi Mike,
>
> Sorry to bother you once with this, but I've re-read all the docs on
> the relationships and I want to be sure that I've understand correctly.
>
> Imagine I have the following "link" table in SQL:
> https://gist.github.com/silenius/77d406f8e0c0e26eb38f with the following
> mappers: https://gist.github.com/silenius/18190ef4912667e49b1a
>
> So basically you have Accounts, Pools, Roles and a link table
> AccountRole
>
> What I would like is to have on the Pool object the list of Account
> who have a Role 'facilitator' for a certain Pool.
>
> The result is at line 18-26 of the paste
> (https://gist.github.com/silenius/18190ef4912667e49b1a#file-gistfile1-py-L18-L26),
> I wondered if it was okay because I sometimes mix the primaryjoin and
> secondaryjoin clauses... If I understand well I don't need a primaryjoin
> clause
> in this case ..?
I don’t know offhand, if you turn on INFO logging for the “sqlalchemy.orm”
logger you’ll see every decision SQLAlchemy is making:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship("B")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey('a.id'))
import logging
logging.basicConfig()
logging.getLogger("sqlalchemy.orm").setLevel(logging.INFO)
configure_mappers()
output:
INFO:sqlalchemy.orm.mapper.Mapper:(B|b) _post_configure_properties() started
INFO:sqlalchemy.orm.mapper.Mapper:(B|b) initialize prop id
INFO:sqlalchemy.orm.mapper.Mapper:(B|b) initialize prop a_id
INFO:sqlalchemy.orm.mapper.Mapper:(B|b) _post_configure_properties() complete
INFO:sqlalchemy.orm.mapper.Mapper:(A|a) _post_configure_properties() started
INFO:sqlalchemy.orm.mapper.Mapper:(A|a) initialize prop bs
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs setup primary join
a.id = b.a_id
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs setup secondary
join None
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs synchronize pairs
[(a.id => b.a_id)]
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs secondary
synchronize pairs []
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs local/remote pairs
[(a.id / b.a_id)]
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs remote columns
[b.a_id]
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs local columns [a.id]
INFO:sqlalchemy.orm.relationships.RelationshipProperty:A.bs relationship
direction symbol('ONETOMANY')
INFO:sqlalchemy.orm.strategies.LazyLoader:A.bs lazy loading clause :param_1 =
b.a_id
INFO:sqlalchemy.orm.mapper.Mapper:(A|a) initialize prop id
INFO:sqlalchemy.orm.mapper.Mapper:(A|a) _post_configure_properties() complete
>
> Thanks,
> Julien
>
>> I'm using PostgreSQL, and I checked that all constraints are properly
>> created on server-side but I haven't checked with sqllite://
>>
Thanks,
Julien
>>> --
>>> Julien Cigar
>>> Belgian Biodiversity Platform (http://www.biodiversity.be)
>>> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0
>>> No trees were killed in the creation of this message.
>>> However, many electrons were terribly inconvenienced.
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it,
>>> send an email to sqlalchemy+unsubscr...@googlegroups.com.
>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>>
>> --
>> Julien Cigar
>> Belgian Biodiversity Platform (http://www.biodiversity.be)
>> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0
>> No trees were killed in the creation of this message.
>> However, many electrons were terribly inconvenienced.
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it,
>> send an email to sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
>
Re: [sqlalchemy] relationship problem
On Mon, Mar 02, 2015 at 12:15:51PM -0500, Michael Bayer wrote: > > > Julien Cigar wrote: > > > On Sun, Mar 01, 2015 at 01:53:30PM +0100, Julien Cigar wrote: > >> On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: > On Feb 26, 2015, at 5:56 AM, Julien Cigar wrote: > > > On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > > > > > > Julien Cigar wrote: > > > >>> On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > >>> Julien Cigar wrote: > >>> > > On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > > Hello, > > > > I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection > > feature of > > SQLAlchemy. > > > > I have the following tables (only relevant parts are show): > > https://gist.github.com/silenius/390bb9937490730741f2 > > > > and the "problematic" mapper is the one of my association object: > > https://gist.github.com/silenius/1559a7db65ed30a1b079 > > > > SQLAlchemy complains with the following error: > > "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > > initialize - can't proceed with initialization of other mappers. > > Original exception was: Could not locate any simple equality > > expressions > > involving locally mapped foreign key columns for primary join > > condition > > 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > > pool_invite.pool_id = pool.id' on relationship > > PoolAccountResult.pool. > > Ensure that referencing columns are associated with a ForeignKey or > > ForeignKeyConstraint, or are annotated in the join condition with > > the > > foreign() annotation. To allow comparison operators other than > > '==', the > > relationship can be marked as viewonly=True." > > > > The problem is that in the PoolAccountResult mapper I want a > > relationship to the Pool but the link is made through an > > intermediate > > table (pool_invite) .. > > > > Any idea how to handle this with SQLAlchemy ? > > > > Thanks :) > > > > Julien > > ... and I'm answering to myself: it seems to work with > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > does it sound right ? > >>> > >>> Sure. Also, you could use automap which does figure these out in > >>> simple cases: > >>> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > >> > >> always with this, any idea why SQLAlchemy inserts NULL and > >> NULL for my two relationship (line 51-79) instead of the pool_obj and > >> dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? > > > > getting a 404 on that link. > > Hi Mike, > > Thanks for your help! > > I took the time to make a complete test case, available from here > https://gist.github.com/silenius/96d6ed2544d14753853f > >>> > >>> That's a very strange use of secondary, and I'm not sure I understand the > >>> rationale for an odd schema like this. It should be pretty clear that > >>> when a table is set up as secondary, it is only used as a linkage between > >>> those immediate classes and there are no features to track the state of > >>> this table otherwise as though it were a mapped class. Using the same > >>> secondary table in two unrelated relationships is not the intended use. > >> > >> It's true that the schema is a little odd, in SQL it translates as > >> https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). > >> > >> The scenario is that an "account" can be invited to a "pool" (table > >> pool_invite at line 45-57) and can submit one (or more) "results" for > >> that invitation (I have to record the submitted date too). This scenario > >> is a good candidate for an association object, the only thing is that > >> later someone can decide to remove all those submissions, but I have to > >> remember that there was an invitation. That's why I have references to > >> the table pool_invite in pool_invite_result (line 71-73) instead of pool > >> and result :) > >> > >>> Nevertheless, if you manipulate between Pool and User, that has no impact > >>> whatsoever on PoolInviteResult... Especially since we're dealing with a > >>> "secondary" table and not a first class mapped entity.You should add > >>> event listeners as needed on attributes such that when an in-Python > >>> change occurs between Pool and User, the desired change occurs for > >>> PoolInviteResult as well. > >> > >> It has impacts, if I remove a Pool or an User all invitations > >> (pool_invite) must be removed too, as well as all submissions > >> (pool_invite_result). > >> > >
Re: [sqlalchemy] relationship problem
Julien Cigar wrote: > On Sun, Mar 01, 2015 at 01:53:30PM +0100, Julien Cigar wrote: >> On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: On Feb 26, 2015, at 5:56 AM, Julien Cigar wrote: > On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > > > Julien Cigar wrote: > >>> On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: >>> Julien Cigar wrote: >>> > On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > Hello, > > I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature > of > SQLAlchemy. > > I have the following tables (only relevant parts are show): > https://gist.github.com/silenius/390bb9937490730741f2 > > and the "problematic" mapper is the one of my association object: > https://gist.github.com/silenius/1559a7db65ed30a1b079 > > SQLAlchemy complains with the following error: > "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > initialize - can't proceed with initialization of other mappers. > Original exception was: Could not locate any simple equality > expressions > involving locally mapped foreign key columns for primary join > condition > 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. > Ensure that referencing columns are associated with a ForeignKey or > ForeignKeyConstraint, or are annotated in the join condition with the > foreign() annotation. To allow comparison operators other than '==', > the > relationship can be marked as viewonly=True." > > The problem is that in the PoolAccountResult mapper I want a > relationship to the Pool but the link is made through an intermediate > table (pool_invite) .. > > Any idea how to handle this with SQLAlchemy ? > > Thanks :) > > Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? >>> >>> Sure. Also, you could use automap which does figure these out in >>> simple cases: >>> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html >> >> always with this, any idea why SQLAlchemy inserts NULL and >> NULL for my two relationship (line 51-79) instead of the pool_obj and >> dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? > > getting a 404 on that link. Hi Mike, Thanks for your help! I took the time to make a complete test case, available from here https://gist.github.com/silenius/96d6ed2544d14753853f >>> >>> That's a very strange use of secondary, and I'm not sure I understand the >>> rationale for an odd schema like this. It should be pretty clear that >>> when a table is set up as secondary, it is only used as a linkage between >>> those immediate classes and there are no features to track the state of >>> this table otherwise as though it were a mapped class. Using the same >>> secondary table in two unrelated relationships is not the intended use. >> >> It's true that the schema is a little odd, in SQL it translates as >> https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). >> >> The scenario is that an "account" can be invited to a "pool" (table >> pool_invite at line 45-57) and can submit one (or more) "results" for >> that invitation (I have to record the submitted date too). This scenario >> is a good candidate for an association object, the only thing is that >> later someone can decide to remove all those submissions, but I have to >> remember that there was an invitation. That's why I have references to >> the table pool_invite in pool_invite_result (line 71-73) instead of pool >> and result :) >> >>> Nevertheless, if you manipulate between Pool and User, that has no impact >>> whatsoever on PoolInviteResult... Especially since we're dealing with a >>> "secondary" table and not a first class mapped entity.You should add >>> event listeners as needed on attributes such that when an in-Python change >>> occurs between Pool and User, the desired change occurs for >>> PoolInviteResult as well. >> >> It has impacts, if I remove a Pool or an User all invitations >> (pool_invite) must be removed too, as well as all submissions >> (pool_invite_result). >> >> Anyway, I'll take a look at ORM Events :) thanks! > > Another thing that is a little cloudy to me is why it doesn't work with > an alternate primaryjoin condition, such as > https://gist.github.com/silenius/300729e312dad6b9b847 the relationship() has a simple job. It is given table A and table B, and it needs t
Re: [sqlalchemy] relationship problem
On Sun, Mar 01, 2015 at 01:53:30PM +0100, Julien Cigar wrote: > On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: > > > > > > > > > On Feb 26, 2015, at 5:56 AM, Julien Cigar wrote: > > > > > >> On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > > >> > > >> > > >> Julien Cigar wrote: > > >> > > On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > > Julien Cigar wrote: > > > > >> On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > > >> Hello, > > >> > > >> I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection > > >> feature of > > >> SQLAlchemy. > > >> > > >> I have the following tables (only relevant parts are show): > > >> https://gist.github.com/silenius/390bb9937490730741f2 > > >> > > >> and the "problematic" mapper is the one of my association object: > > >> https://gist.github.com/silenius/1559a7db65ed30a1b079 > > >> > > >> SQLAlchemy complains with the following error: > > >> "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > > >> initialize - can't proceed with initialization of other mappers. > > >> Original exception was: Could not locate any simple equality > > >> expressions > > >> involving locally mapped foreign key columns for primary join > > >> condition > > >> 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > > >> pool_invite.pool_id = pool.id' on relationship > > >> PoolAccountResult.pool. > > >> Ensure that referencing columns are associated with a ForeignKey or > > >> ForeignKeyConstraint, or are annotated in the join condition with the > > >> foreign() annotation. To allow comparison operators other than '==', > > >> the > > >> relationship can be marked as viewonly=True." > > >> > > >> The problem is that in the PoolAccountResult mapper I want a > > >> relationship to the Pool but the link is made through an intermediate > > >> table (pool_invite) .. > > >> > > >> Any idea how to handle this with SQLAlchemy ? > > >> > > >> Thanks :) > > >> > > >> Julien > > > > > > ... and I'm answering to myself: it seems to work with > > > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > > > > > does it sound right ? > > > > Sure. Also, you could use automap which does figure these out in > > simple cases: > > http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > > >>> > > >>> always with this, any idea why SQLAlchemy inserts NULL and > > >>> NULL for my two relationship (line 51-79) instead of the pool_obj and > > >>> dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? > > >> > > >> getting a 404 on that link. > > > > > > Hi Mike, > > > > > > Thanks for your help! > > > > > > I took the time to make a complete test case, available from here > > > https://gist.github.com/silenius/96d6ed2544d14753853f > > > > That's a very strange use of secondary, and I'm not sure I understand the > > rationale for an odd schema like this. It should be pretty clear that > > when a table is set up as secondary, it is only used as a linkage between > > those immediate classes and there are no features to track the state of > > this table otherwise as though it were a mapped class. Using the same > > secondary table in two unrelated relationships is not the intended use. > > It's true that the schema is a little odd, in SQL it translates as > https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). > > The scenario is that an "account" can be invited to a "pool" (table > pool_invite at line 45-57) and can submit one (or more) "results" for > that invitation (I have to record the submitted date too). This scenario > is a good candidate for an association object, the only thing is that > later someone can decide to remove all those submissions, but I have to > remember that there was an invitation. That's why I have references to > the table pool_invite in pool_invite_result (line 71-73) instead of pool > and result :) > > > > > Nevertheless, if you manipulate between Pool and User, that has no impact > > whatsoever on PoolInviteResult... Especially since we're dealing with a > > "secondary" table and not a first class mapped entity.You should add > > event listeners as needed on attributes such that when an in-Python change > > occurs between Pool and User, the desired change occurs for > > PoolInviteResult as well. > > It has impacts, if I remove a Pool or an User all invitations > (pool_invite) must be removed too, as well as all submissions > (pool_invite_result). > > Anyway, I'll take a look at ORM Events :) thanks! Another thing that is a little cloudy to me is why it doesn't work with an alternate primaryjoin condition, such as https://gist.github.com/silenius/300729e312dad6b9b847 > > > > > > > > > I'm using Postgr
Re: [sqlalchemy] relationship problem
On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: > > > > > On Feb 26, 2015, at 5:56 AM, Julien Cigar wrote: > > > >> On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > >> > >> > >> Julien Cigar wrote: > >> > On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > Julien Cigar wrote: > > >> On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > >> Hello, > >> > >> I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature > >> of > >> SQLAlchemy. > >> > >> I have the following tables (only relevant parts are show): > >> https://gist.github.com/silenius/390bb9937490730741f2 > >> > >> and the "problematic" mapper is the one of my association object: > >> https://gist.github.com/silenius/1559a7db65ed30a1b079 > >> > >> SQLAlchemy complains with the following error: > >> "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > >> initialize - can't proceed with initialization of other mappers. > >> Original exception was: Could not locate any simple equality > >> expressions > >> involving locally mapped foreign key columns for primary join condition > >> 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > >> pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. > >> Ensure that referencing columns are associated with a ForeignKey or > >> ForeignKeyConstraint, or are annotated in the join condition with the > >> foreign() annotation. To allow comparison operators other than '==', > >> the > >> relationship can be marked as viewonly=True." > >> > >> The problem is that in the PoolAccountResult mapper I want a > >> relationship to the Pool but the link is made through an intermediate > >> table (pool_invite) .. > >> > >> Any idea how to handle this with SQLAlchemy ? > >> > >> Thanks :) > >> > >> Julien > > > > ... and I'm answering to myself: it seems to work with > > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > > > does it sound right ? > > Sure. Also, you could use automap which does figure these out in simple > cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > >>> > >>> always with this, any idea why SQLAlchemy inserts NULL and > >>> NULL for my two relationship (line 51-79) instead of the pool_obj and > >>> dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? > >> > >> getting a 404 on that link. > > > > Hi Mike, > > > > Thanks for your help! > > > > I took the time to make a complete test case, available from here > > https://gist.github.com/silenius/96d6ed2544d14753853f > > That's a very strange use of secondary, and I'm not sure I understand the > rationale for an odd schema like this. It should be pretty clear that when > a table is set up as secondary, it is only used as a linkage between those > immediate classes and there are no features to track the state of this table > otherwise as though it were a mapped class. Using the same secondary table > in two unrelated relationships is not the intended use. It's true that the schema is a little odd, in SQL it translates as https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). The scenario is that an "account" can be invited to a "pool" (table pool_invite at line 45-57) and can submit one (or more) "results" for that invitation (I have to record the submitted date too). This scenario is a good candidate for an association object, the only thing is that later someone can decide to remove all those submissions, but I have to remember that there was an invitation. That's why I have references to the table pool_invite in pool_invite_result (line 71-73) instead of pool and result :) > > Nevertheless, if you manipulate between Pool and User, that has no impact > whatsoever on PoolInviteResult... Especially since we're dealing with a > "secondary" table and not a first class mapped entity.You should add > event listeners as needed on attributes such that when an in-Python change > occurs between Pool and User, the desired change occurs for PoolInviteResult > as well. It has impacts, if I remove a Pool or an User all invitations (pool_invite) must be removed too, as well as all submissions (pool_invite_result). Anyway, I'll take a look at ORM Events :) thanks! > > > > > I'm using PostgreSQL, and I checked that all constraints are properly > > created on server-side but I haven't checked with sqllite:// > > > >> > >> > >> > >> > >> > >>> > >>> Thanks, > >>> Julien > >>> > >>> > >> -- > >> Julien Cigar > >> Belgian Biodiversity Platform (http://www.biodiversity.be) > >> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > >> No trees were killed in the creation of this message. > >> However, many electrons were
Re: [sqlalchemy] relationship problem
> On Feb 26, 2015, at 5:56 AM, Julien Cigar wrote: > >> On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: >> >> >> Julien Cigar wrote: >> On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar wrote: >> On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: >> Hello, >> >> I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of >> SQLAlchemy. >> >> I have the following tables (only relevant parts are show): >> https://gist.github.com/silenius/390bb9937490730741f2 >> >> and the "problematic" mapper is the one of my association object: >> https://gist.github.com/silenius/1559a7db65ed30a1b079 >> >> SQLAlchemy complains with the following error: >> "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to >> initialize - can't proceed with initialization of other mappers. >> Original exception was: Could not locate any simple equality expressions >> involving locally mapped foreign key columns for primary join condition >> 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND >> pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. >> Ensure that referencing columns are associated with a ForeignKey or >> ForeignKeyConstraint, or are annotated in the join condition with the >> foreign() annotation. To allow comparison operators other than '==', the >> relationship can be marked as viewonly=True." >> >> The problem is that in the PoolAccountResult mapper I want a >> relationship to the Pool but the link is made through an intermediate >> table (pool_invite) .. >> >> Any idea how to handle this with SQLAlchemy ? >> >> Thanks :) >> >> Julien > > ... and I'm answering to myself: it seems to work with > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html >>> >>> always with this, any idea why SQLAlchemy inserts NULL and >>> NULL for my two relationship (line 51-79) instead of the pool_obj and >>> dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? >> >> getting a 404 on that link. > > Hi Mike, > > Thanks for your help! > > I took the time to make a complete test case, available from here > https://gist.github.com/silenius/96d6ed2544d14753853f That's a very strange use of secondary, and I'm not sure I understand the rationale for an odd schema like this. It should be pretty clear that when a table is set up as secondary, it is only used as a linkage between those immediate classes and there are no features to track the state of this table otherwise as though it were a mapped class. Using the same secondary table in two unrelated relationships is not the intended use. Nevertheless, if you manipulate between Pool and User, that has no impact whatsoever on PoolInviteResult... Especially since we're dealing with a "secondary" table and not a first class mapped entity.You should add event listeners as needed on attributes such that when an in-Python change occurs between Pool and User, the desired change occurs for PoolInviteResult as well. > > I'm using PostgreSQL, and I checked that all constraints are properly > created on server-side but I haven't checked with sqllite:// > >> >> >> >> >> >>> >>> Thanks, >>> Julien >>> >>> >> -- >> Julien Cigar >> Belgian Biodiversity Platform (http://www.biodiversity.be) >> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 >> No trees were killed in the creation of this message. >> However, many electrons were terribly inconvenienced. >> >> -- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > > > -- > Julien Cigar > Belgian Biodiversity Platform (http://www.biodiversity.be) > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. >>>
Re: [sqlalchemy] relationship problem
On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > > > Julien Cigar wrote: > > > On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > >> Julien Cigar wrote: > >> > >>> On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > Hello, > > I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of > SQLAlchemy. > > I have the following tables (only relevant parts are show): > https://gist.github.com/silenius/390bb9937490730741f2 > > and the "problematic" mapper is the one of my association object: > https://gist.github.com/silenius/1559a7db65ed30a1b079 > > SQLAlchemy complains with the following error: > "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > initialize - can't proceed with initialization of other mappers. > Original exception was: Could not locate any simple equality expressions > involving locally mapped foreign key columns for primary join condition > 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. > Ensure that referencing columns are associated with a ForeignKey or > ForeignKeyConstraint, or are annotated in the join condition with the > foreign() annotation. To allow comparison operators other than '==', the > relationship can be marked as viewonly=True." > > The problem is that in the PoolAccountResult mapper I want a > relationship to the Pool but the link is made through an intermediate > table (pool_invite) .. > > Any idea how to handle this with SQLAlchemy ? > > Thanks :) > > Julien > >>> > >>> ... and I'm answering to myself: it seems to work with > >>> https://gist.github.com/silenius/e7e59c96a7277fb5879f > >>> > >>> does it sound right ? > >> > >> Sure. Also, you could use automap which does figure these out in simple > >> cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > > > > always with this, any idea why SQLAlchemy inserts NULL and > > NULL for my two relationship (line 51-79) instead of the pool_obj and > > dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? > > getting a 404 on that link. Hi Mike, Thanks for your help! I took the time to make a complete test case, available from here https://gist.github.com/silenius/96d6ed2544d14753853f I'm using PostgreSQL, and I checked that all constraints are properly created on server-side but I haven't checked with sqllite:// > > > > > > > > > Thanks, > > Julien > > > > > -- > Julien Cigar > Belgian Biodiversity Platform (http://www.biodiversity.be) > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > >>> > >>> > >>> > >>> -- > >>> Julien Cigar > >>> Belgian Biodiversity Platform (http://www.biodiversity.be) > >>> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > >>> No trees were killed in the creation of this message. > >>> However, many electrons were terribly inconvenienced. > >>> > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "sqlalchemy" group. > >>> To unsubscribe from this group and stop receiving emails from it, send an > >>> email to sqlalchemy+unsubscr...@googlegroups.com. > >>> To post to this group, send email to sqlalchemy@googlegroups.com. > >>> Visit this group at http://groups.google.com/group/sqlalchemy. > >>> For more options, visit https://groups.google.com/d/optout. > >> > >> -- > >> You received this message because you are subscribed to the Google Groups > >> "sqlalchemy" group. > >> To unsubscribe from this group and stop receiving emails from it, send an > >> email to sqlalchemy+unsubscr...@googlegroups.com. > >> To post to this group, send email to sqlalchemy@googlegroups.com. > >> Visit this group at http://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > > > > -- > > Julien Cigar > > Belgian Biodiversity Platform (http://www.biodiversity.be) > > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > > No trees were killed in the creation of this message. > > However, many electrons were terribly inconvenienced. > > > > -- > > You received this message because you
Re: [sqlalchemy] relationship problem
Julien Cigar wrote: > On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: >> Julien Cigar wrote: >> >>> On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the "problematic" mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True." The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien >>> >>> ... and I'm answering to myself: it seems to work with >>> https://gist.github.com/silenius/e7e59c96a7277fb5879f >>> >>> does it sound right ? >> >> Sure. Also, you could use automap which does figure these out in simple >> cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > > always with this, any idea why SQLAlchemy inserts NULL and > NULL for my two relationship (line 51-79) instead of the pool_obj and > dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? getting a 404 on that link. > > Thanks, > Julien > > -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. >>> >>> >>> >>> -- >>> Julien Cigar >>> Belgian Biodiversity Platform (http://www.biodiversity.be) >>> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 >>> No trees were killed in the creation of this message. >>> However, many electrons were terribly inconvenienced. >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send an >>> email to sqlalchemy+unsubscr...@googlegroups.com. >>> To post to this group, send email to sqlalchemy@googlegroups.com. >>> Visit this group at http://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > -- > Julien Cigar > Belgian Biodiversity Platform (http://www.biodiversity.be) > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsu
Re: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > > > Julien Cigar wrote: > > > On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > >> Hello, > >> > >> I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of > >> SQLAlchemy. > >> > >> I have the following tables (only relevant parts are show): > >> https://gist.github.com/silenius/390bb9937490730741f2 > >> > >> and the "problematic" mapper is the one of my association object: > >> https://gist.github.com/silenius/1559a7db65ed30a1b079 > >> > >> SQLAlchemy complains with the following error: > >> "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > >> initialize - can't proceed with initialization of other mappers. > >> Original exception was: Could not locate any simple equality expressions > >> involving locally mapped foreign key columns for primary join condition > >> 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > >> pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. > >> Ensure that referencing columns are associated with a ForeignKey or > >> ForeignKeyConstraint, or are annotated in the join condition with the > >> foreign() annotation. To allow comparison operators other than '==', the > >> relationship can be marked as viewonly=True." > >> > >> The problem is that in the PoolAccountResult mapper I want a > >> relationship to the Pool but the link is made through an intermediate > >> table (pool_invite) .. > >> > >> Any idea how to handle this with SQLAlchemy ? > >> > >> Thanks :) > >> > >> Julien > > > > ... and I'm answering to myself: it seems to work with > > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > > > does it sound right ? > > Sure. Also, you could use automap which does figure these out in simple > cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? Thanks, Julien > > > > > >> -- > >> Julien Cigar > >> Belgian Biodiversity Platform (http://www.biodiversity.be) > >> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > >> No trees were killed in the creation of this message. > >> However, many electrons were terribly inconvenienced. > >> > >> -- > >> You received this message because you are subscribed to the Google Groups > >> "sqlalchemy" group. > >> To unsubscribe from this group and stop receiving emails from it, send an > >> email to sqlalchemy+unsubscr...@googlegroups.com. > >> To post to this group, send email to sqlalchemy@googlegroups.com. > >> Visit this group at http://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > > > > > > > > -- > > Julien Cigar > > Belgian Biodiversity Platform (http://www.biodiversity.be) > > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > > No trees were killed in the creation of this message. > > However, many electrons were terribly inconvenienced. > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. pgp9ParhCgn47.pgp Description: PGP signature
Re: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > > > Julien Cigar wrote: > > > On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > >> Hello, > >> > >> I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of > >> SQLAlchemy. > >> > >> I have the following tables (only relevant parts are show): > >> https://gist.github.com/silenius/390bb9937490730741f2 > >> > >> and the "problematic" mapper is the one of my association object: > >> https://gist.github.com/silenius/1559a7db65ed30a1b079 > >> > >> SQLAlchemy complains with the following error: > >> "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > >> initialize - can't proceed with initialization of other mappers. > >> Original exception was: Could not locate any simple equality expressions > >> involving locally mapped foreign key columns for primary join condition > >> 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > >> pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. > >> Ensure that referencing columns are associated with a ForeignKey or > >> ForeignKeyConstraint, or are annotated in the join condition with the > >> foreign() annotation. To allow comparison operators other than '==', the > >> relationship can be marked as viewonly=True." > >> > >> The problem is that in the PoolAccountResult mapper I want a > >> relationship to the Pool but the link is made through an intermediate > >> table (pool_invite) .. > >> > >> Any idea how to handle this with SQLAlchemy ? > >> > >> Thanks :) > >> > >> Julien > > > > ... and I'm answering to myself: it seems to work with > > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > > > does it sound right ? > > Sure. Also, you could use automap which does figure these out in simple > cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html Thanks, looks like I missed this extension... I'll definitively take a look at it! > > > > > > >> -- > >> Julien Cigar > >> Belgian Biodiversity Platform (http://www.biodiversity.be) > >> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > >> No trees were killed in the creation of this message. > >> However, many electrons were terribly inconvenienced. > >> > >> -- > >> You received this message because you are subscribed to the Google Groups > >> "sqlalchemy" group. > >> To unsubscribe from this group and stop receiving emails from it, send an > >> email to sqlalchemy+unsubscr...@googlegroups.com. > >> To post to this group, send email to sqlalchemy@googlegroups.com. > >> Visit this group at http://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > > > > > > > > -- > > Julien Cigar > > Belgian Biodiversity Platform (http://www.biodiversity.be) > > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > > No trees were killed in the creation of this message. > > However, many electrons were terribly inconvenienced. > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. pgpLyl504xqIF.pgp Description: PGP signature
Re: [sqlalchemy] relationship problem
Julien Cigar wrote: > On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: >> Hello, >> >> I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of >> SQLAlchemy. >> >> I have the following tables (only relevant parts are show): >> https://gist.github.com/silenius/390bb9937490730741f2 >> >> and the "problematic" mapper is the one of my association object: >> https://gist.github.com/silenius/1559a7db65ed30a1b079 >> >> SQLAlchemy complains with the following error: >> "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to >> initialize - can't proceed with initialization of other mappers. >> Original exception was: Could not locate any simple equality expressions >> involving locally mapped foreign key columns for primary join condition >> 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND >> pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. >> Ensure that referencing columns are associated with a ForeignKey or >> ForeignKeyConstraint, or are annotated in the join condition with the >> foreign() annotation. To allow comparison operators other than '==', the >> relationship can be marked as viewonly=True." >> >> The problem is that in the PoolAccountResult mapper I want a >> relationship to the Pool but the link is made through an intermediate >> table (pool_invite) .. >> >> Any idea how to handle this with SQLAlchemy ? >> >> Thanks :) >> >> Julien > > ... and I'm answering to myself: it seems to work with > https://gist.github.com/silenius/e7e59c96a7277fb5879f > > does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html > >> -- >> Julien Cigar >> Belgian Biodiversity Platform (http://www.biodiversity.be) >> PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 >> No trees were killed in the creation of this message. >> However, many electrons were terribly inconvenienced. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > > > -- > Julien Cigar > Belgian Biodiversity Platform (http://www.biodiversity.be) > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: > Hello, > > I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of > SQLAlchemy. > > I have the following tables (only relevant parts are show): > https://gist.github.com/silenius/390bb9937490730741f2 > > and the "problematic" mapper is the one of my association object: > https://gist.github.com/silenius/1559a7db65ed30a1b079 > > SQLAlchemy complains with the following error: > "sqlalchemy.exc.InvalidRequestError: One or more mappers failed to > initialize - can't proceed with initialization of other mappers. > Original exception was: Could not locate any simple equality expressions > involving locally mapped foreign key columns for primary join condition > 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND > pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. > Ensure that referencing columns are associated with a ForeignKey or > ForeignKeyConstraint, or are annotated in the join condition with the > foreign() annotation. To allow comparison operators other than '==', the > relationship can be marked as viewonly=True." > > The problem is that in the PoolAccountResult mapper I want a > relationship to the Pool but the link is made through an intermediate > table (pool_invite) .. > > Any idea how to handle this with SQLAlchemy ? > > Thanks :) > > Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? > > -- > Julien Cigar > Belgian Biodiversity Platform (http://www.biodiversity.be) > PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. pgpDJ4I7_2j3x.pgp Description: PGP signature
Re: [sqlalchemy] relationship problem
Michael,
On 09/11/2012 23:40, Michael Bayer wrote:
...
Authuser.cellar = sao.relationship('Cellar', primaryjoin=
('Authuser.fk_cellar_id==Cellar.id'))
With this query I don't get the authuser relation on the second record even so
the fk_authuser_id is set to 1, which is the same as on the first record.
above, fk_authuser_id is used by the Authuser.cellar relationship, not the
Cellar.authuser relationship. You'd need fk_cellar_id to be meaningful in that
case.
I looked at this for a long time and just didn't see this stupid mistake.
Thanks a lot for putting me right.
Werner
--
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.
Re: [sqlalchemy] relationship problem
On Nov 9, 2012, at 3:45 AM, Werner wrote:
> Hi,
>
> I don't understand why on one record I don't get the authuser relation.
>
> My definition is:
> class Cellar(DeclarativeBase, mix.StandardColumnMixin):
>__tablename__ = u'cellar'
>
>name = sa.Column(sa.Unicode(length=50), nullable=False)
>fk_authuser_id = sautils.reference_col('authuser')
>
> Cellar.authuser = sao.relationship('Authuser', backref='cellars', primaryjoin=
>('Cellar.id==Authuser.fk_cellar_id'), uselist=False)
>
> In my authuser class I have:
> class Authuser(DeclarativeBase, mix.StandardColumnMixin):
>__tablename__ = u'authuser'
>
>name = sa.Column(sa.Unicode(30), nullable=False, index=True)
>
>
># not using reference_col due to the use of Authuser in cellar
>fk_cellar_id = sa.Column(sa.BigInteger(), sa.ForeignKey(u'cellar.id',
>name='fk_cellar_id', use_alter=True))
>
> Authuser.cellar = sao.relationship('Cellar', primaryjoin=
>('Authuser.fk_cellar_id==Cellar.id'))
>
> With this query I don't get the authuser relation on the second record even
> so the fk_authuser_id is set to 1, which is the same as on the first record.
above, fk_authuser_id is used by the Authuser.cellar relationship, not the
Cellar.authuser relationship. You'd need fk_cellar_id to be meaningful in that
case.
--
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.
RE: [sqlalchemy] relationship problem
mik wrote:
> Hello,
>
> I am trying to use sqlalchemy with oracle, here is my code:
>
> from sqlalchemy import *
> from sqlalchemy.orm import sessionmaker, mapper, relationship
> class Activite(object):
> pass
> class Famprod(object):
> pass
> engine = create_engine('oracle://login/paswd@db', echo=True)
> metadata = MetaData(engine)
> tActivite = Table('ACTIVITE', metadata,
> autoload=True)
> mapper(Activite, tActivite)
> tFamprod = Table('FAMPROD', metadata,
> autoload=True)
> mapper(Famprod, tFamprod)
> Famprod.activite = relationship(Activite)
> Session = sessionmaker(bind=engine)
> session = Session()
> famprod = session.query(Famprod).get(("ED", "15"))
> print famprod.activite
>
>
> and i get this error:
> AttributeError: 'RelationshipProperty' object has no attribute
> 'parent'
>
> The table famprod has a composite key, one of the key columns is the
> key of activite.
> Is there something wrong with my code ?
> I have tried to manually define the tFamprod's keys and foreign key
> without succes.
>
> Thank you.
>
I think your problem is here:
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
You can't add relationship properties to mapped classes, unless they
were set up with the declarative extension
(http://www.sqlalchemy.org/docs/orm/extensions/declarative.html).
Without declarative, the code should look something like this:
mapper(Famprod, tFamprod, properties={
'activite': relationship(Activite),
})
(http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-many)
Hope that helps,
Simon
--
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.
