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 <jci...@ulb.ac.be> wrote: > > > > > >> On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > > >> > > >> > > >> Julien Cigar <jci...@ulb.ac.be> wrote: > > >> > > >>>> On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > > >>>> Julien Cigar <jci...@ulb.ac.be> 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 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 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+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. -- 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.
pgpzK6Ygky0Qp.pgp
Description: PGP signature
