On Mon, Mar 02, 2015 at 12:15:51PM -0500, Michael Bayer wrote: > > > Julien Cigar <[email protected]> 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 <[email protected]> wrote: > >>>> > >>>>> On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: > >>>>> > >>>>> > >>>>> Julien Cigar <[email protected]> wrote: > >>>>> > >>>>>>> On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: > >>>>>>> Julien Cigar <[email protected]> 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.
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 ..? 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 [email protected]. > >>>>>>>>> To post to this group, send email to [email protected]. > >>>>>>>>> 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 [email protected]. > >>>>>>>> To post to this group, send email to [email protected]. > >>>>>>>> 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 [email protected]. > >>>>>>> To post to this group, send email to [email protected]. > >>>>>>> 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 [email protected]. > >>>>>> To post to this group, send email to [email protected]. > >>>>>> 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 [email protected]. > >>>>> To post to this group, send email to [email protected]. > >>>>> 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 [email protected]. > >>>> To post to this group, send email to [email protected]. > >>>> 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 [email protected]. > >>> To post to this group, send email to [email protected]. > >>> 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 [email protected]. > >> To post to this group, send email to [email protected]. > >> 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 [email protected]. > > To post to this group, send email to [email protected]. > > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
pgpRGfUapx3ad.pgp
Description: PGP signature
