Julien Cigar <jci...@ulb.ac.be> 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 <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 the relationship() has a simple job. It is given table A and table B, and it needs to locate where columns from A are matched up to columns from B. The relationship in that gist does not have this pattern within the primary join condition; it is injecting the “pool_invite” table as an association table, which is what the “secondary” argument is used for. This argument tells relationship() that instead of searching for direct column comparisons between A and B, it should look for comparisons between A and ATOB, and ATOB and B. >>>> 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. -- 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.
