Re: [sqlalchemy] Advice on multiple ORM classes in multiple files and imports
Thanks for your help Simon, I've found the issue to be related to mixed source imports and duplicate paths set in eclipse, which both lead to a double import situation; again thanks for the useful links. -- Marcin 2015-03-02 13:19 GMT+01:00 Simon King si...@simonking.org.uk: Hi Martin, Normally, Python caches modules after they are imported, so when you import a module for the second time it doesn't actually execute the code again. If you have 3 modules, one, two and common, and one and two both import common, common should only be loaded once. However, there are a few situations where this can break. One is when the initial script for your application (the one that you run from the command line) is also imported by other modules. Typically the script will be loaded twice, once with the name __main__, and once with its real name. Other possibilities might be import loops, certain uses of from module import something, manipulation of PYTHONPATH/sys.path etc. It's difficult to know what the cause is without seeing the code. See also http://python-notes.curiousefficiency.org/en/latest/python_concepts/import_traps.html#the-double-import-trap . By looking at the value of __name__ and the stack traces when you get exceptions, you might be able to figure it out. http://stackoverflow.com/a/4897479/395053 suggests inserting import pdb; pdb.set_trace() above the class definition and using bt to display backtraces. In general, the solution is to ensure that your imports are structured as simply as possible and avoid circular dependencies. Hope that helps, Simon On Mon, Mar 2, 2015 at 11:57 AM, Martino Io martino8...@gmail.com wrote: Hi Simon, definitely multiple imports is the issue here, however I don't know how to get rid of it, let's say I have a module which i named ormstuff inside it I have a class named mybase, I do have 2 other modules which are using this class, and to do so I import it in both places. Now this might just be me skipping the basics, however if I don't import the mybase in both modules I cannot use it, since it should throw an undefined variable exception. 2015-03-02 12:47 GMT+01:00 Simon King si...@simonking.org.uk: On Mon, Mar 2, 2015 at 11:29 AM, Martino Io martino8...@gmail.com wrote: Hello, I've been busy writing an application which now has grown considerably, it spans across several packages/modules totalling 200K lines of python code. Recently I made some changes to the structure and decided to split several classes into separate modules for greater flexibility; the problem I'm facing now is quite strange, before I was using mainly 1 file for all the ORM definitions, but since I split that into several files, I've started to get exceptions due to multiple Base objects being defined and no shared mapper to store the metadata. I've changed approach and started to import the base from a single module, however given the high number of modules and cross imports I get exceptions like: sqlalchemy.exc.InvalidRequestError: Table 'fabrics' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object. I thought that by adding use_existing would fix the issue (the mapper will ignore further imports which redefine the objects) but instead got: InvalidRequestError: Multiple classes found for path Group in the registry of this declarative base. Please use a fully module-qualified path. So going back to the question, if I have a cross dependant package (which imports classes which are ORM classes), how do I avoid the mapper redefining a class? Also any advice on best way to structure packages/modules in an ORM use case would be appreciated. extend_existing is almost certainly not what you want here. Each mapped class should only be defined in a single place. If you've done that, and are still getting this error, it suggests that perhaps your Python modules are being imported more than once. Try putting print 'importing module %s' % __name__ before your class definition. If you see the output more than once, then the module is being imported multiple times under different names. Hope that helps, Simon -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gExzW1RNF7A/unsubscribe. To unsubscribe from this group and all its topics, 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
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 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
Re: [sqlalchemy] Advice on multiple ORM classes in multiple files and imports
Hi Martin, Normally, Python caches modules after they are imported, so when you import a module for the second time it doesn't actually execute the code again. If you have 3 modules, one, two and common, and one and two both import common, common should only be loaded once. However, there are a few situations where this can break. One is when the initial script for your application (the one that you run from the command line) is also imported by other modules. Typically the script will be loaded twice, once with the name __main__, and once with its real name. Other possibilities might be import loops, certain uses of from module import something, manipulation of PYTHONPATH/sys.path etc. It's difficult to know what the cause is without seeing the code. See also http://python-notes.curiousefficiency.org/en/latest/python_concepts/import_traps.html#the-double-import-trap. By looking at the value of __name__ and the stack traces when you get exceptions, you might be able to figure it out. http://stackoverflow.com/a/4897479/395053 suggests inserting import pdb; pdb.set_trace() above the class definition and using bt to display backtraces. In general, the solution is to ensure that your imports are structured as simply as possible and avoid circular dependencies. Hope that helps, Simon On Mon, Mar 2, 2015 at 11:57 AM, Martino Io martino8...@gmail.com wrote: Hi Simon, definitely multiple imports is the issue here, however I don't know how to get rid of it, let's say I have a module which i named ormstuff inside it I have a class named mybase, I do have 2 other modules which are using this class, and to do so I import it in both places. Now this might just be me skipping the basics, however if I don't import the mybase in both modules I cannot use it, since it should throw an undefined variable exception. 2015-03-02 12:47 GMT+01:00 Simon King si...@simonking.org.uk: On Mon, Mar 2, 2015 at 11:29 AM, Martino Io martino8...@gmail.com wrote: Hello, I've been busy writing an application which now has grown considerably, it spans across several packages/modules totalling 200K lines of python code. Recently I made some changes to the structure and decided to split several classes into separate modules for greater flexibility; the problem I'm facing now is quite strange, before I was using mainly 1 file for all the ORM definitions, but since I split that into several files, I've started to get exceptions due to multiple Base objects being defined and no shared mapper to store the metadata. I've changed approach and started to import the base from a single module, however given the high number of modules and cross imports I get exceptions like: sqlalchemy.exc.InvalidRequestError: Table 'fabrics' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object. I thought that by adding use_existing would fix the issue (the mapper will ignore further imports which redefine the objects) but instead got: InvalidRequestError: Multiple classes found for path Group in the registry of this declarative base. Please use a fully module-qualified path. So going back to the question, if I have a cross dependant package (which imports classes which are ORM classes), how do I avoid the mapper redefining a class? Also any advice on best way to structure packages/modules in an ORM use case would be appreciated. extend_existing is almost certainly not what you want here. Each mapped class should only be defined in a single place. If you've done that, and are still getting this error, it suggests that perhaps your Python modules are being imported more than once. Try putting print 'importing module %s' % __name__ before your class definition. If you see the output more than once, then the module is being imported multiple times under different names. Hope that helps, Simon -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gExzW1RNF7A/unsubscribe. To unsubscribe from this group and all its topics, 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. -- You received this message because you are subscribed to
Re: [sqlalchemy] Advice on multiple ORM classes in multiple files and imports
Hi Simon, definitely multiple imports is the issue here, however I don't know how to get rid of it, let's say I have a module which i named ormstuff inside it I have a class named mybase, I do have 2 other modules which are using this class, and to do so I import it in both places. Now this might just be me skipping the basics, however if I don't import the mybase in both modules I cannot use it, since it should throw an undefined variable exception. 2015-03-02 12:47 GMT+01:00 Simon King si...@simonking.org.uk: On Mon, Mar 2, 2015 at 11:29 AM, Martino Io martino8...@gmail.com wrote: Hello, I've been busy writing an application which now has grown considerably, it spans across several packages/modules totalling 200K lines of python code. Recently I made some changes to the structure and decided to split several classes into separate modules for greater flexibility; the problem I'm facing now is quite strange, before I was using mainly 1 file for all the ORM definitions, but since I split that into several files, I've started to get exceptions due to multiple Base objects being defined and no shared mapper to store the metadata. I've changed approach and started to import the base from a single module, however given the high number of modules and cross imports I get exceptions like: sqlalchemy.exc.InvalidRequestError: Table 'fabrics' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object. I thought that by adding use_existing would fix the issue (the mapper will ignore further imports which redefine the objects) but instead got: InvalidRequestError: Multiple classes found for path Group in the registry of this declarative base. Please use a fully module-qualified path. So going back to the question, if I have a cross dependant package (which imports classes which are ORM classes), how do I avoid the mapper redefining a class? Also any advice on best way to structure packages/modules in an ORM use case would be appreciated. extend_existing is almost certainly not what you want here. Each mapped class should only be defined in a single place. If you've done that, and are still getting this error, it suggests that perhaps your Python modules are being imported more than once. Try putting print 'importing module %s' % __name__ before your class definition. If you see the output more than once, then the module is being imported multiple times under different names. Hope that helps, Simon -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gExzW1RNF7A/unsubscribe. To unsubscribe from this group and all its topics, 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.
[sqlalchemy] Advice on multiple ORM classes in multiple files and imports
Hello, I've been busy writing an application which now has grown considerably, it spans across several packages/modules totalling 200K lines of python code. Recently I made some changes to the structure and decided to split several classes into separate modules for greater flexibility; the problem I'm facing now is quite strange, before I was using mainly 1 file for all the ORM definitions, but since I split that into several files, I've started to get exceptions due to multiple Base objects being defined and no shared mapper to store the metadata. I've changed approach and started to import the base from a single module, however given the high number of modules and cross imports I get exceptions like: sqlalchemy.exc.InvalidRequestError: Table 'fabrics' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object. I thought that by adding use_existing would fix the issue (the mapper will ignore further imports which redefine the objects) but instead got: InvalidRequestError: Multiple classes found for path Group in the registry of this declarative base. Please use a fully module-qualified path. So going back to the question, if I have a cross dependant package (which imports classes which are ORM classes), how do I avoid the mapper redefining a class? Also any advice on best way to structure packages/modules in an ORM use case would be appreciated. -- Marcin -- 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] Advice on multiple ORM classes in multiple files and imports
On Mon, Mar 2, 2015 at 11:29 AM, Martino Io martino8...@gmail.com wrote: Hello, I've been busy writing an application which now has grown considerably, it spans across several packages/modules totalling 200K lines of python code. Recently I made some changes to the structure and decided to split several classes into separate modules for greater flexibility; the problem I'm facing now is quite strange, before I was using mainly 1 file for all the ORM definitions, but since I split that into several files, I've started to get exceptions due to multiple Base objects being defined and no shared mapper to store the metadata. I've changed approach and started to import the base from a single module, however given the high number of modules and cross imports I get exceptions like: sqlalchemy.exc.InvalidRequestError: Table 'fabrics' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object. I thought that by adding use_existing would fix the issue (the mapper will ignore further imports which redefine the objects) but instead got: InvalidRequestError: Multiple classes found for path Group in the registry of this declarative base. Please use a fully module-qualified path. So going back to the question, if I have a cross dependant package (which imports classes which are ORM classes), how do I avoid the mapper redefining a class? Also any advice on best way to structure packages/modules in an ORM use case would be appreciated. extend_existing is almost certainly not what you want here. Each mapped class should only be defined in a single place. If you've done that, and are still getting this error, it suggests that perhaps your Python modules are being imported more than once. Try putting print 'importing module %s' % __name__ before your class definition. If you see the output more than once, then the module is being imported multiple times under different names. Hope that helps, Simon -- 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] session.transaction / session.begin and always rolling back
Jonathon Nelson jdnel...@dyn.com wrote:
For a variety of reasons, sometimes it's nice to be able to do something like
this:
with dbsess.begin(rollback=True):
do_stuff
Where the expected behavior is that everything in the context runs in a
transaction, but upon exiting the context we issue a rollback. Sometimes this
is useful to *guarantee* that actions that take place within the context are
transactionally read-only.
I had authored and am using this:
def make_dbsess_contextual(sess_factory):
@contextmanager
def dbsess(on_success='commit'):
sess = sess_factory()
# we could use 'with sess.begin() here but it's
# easier to have parity
sess.begin()
try:
yield sess
except:
sess.rollback()
sess.close()
raise
else:
if on_success == 'commit':
sess.commit()
else:
sess.rollback()
sess.close()
return dbsess
I might suggest that you just do the sess.close() without the commit(), but
not the sess.rollback() part; only because the rollback() is a relatively
expensive operation in that the Session is internally reverting things to
try to match a new transaction. If you do sess.close(), the connections are
discarded and the underlying database transactions are rolled back by the
connection pool.
For SQLAlchemy API I don’t really favor creating sessions with “autocommit”
which means that begin() doesn’t really have much place; a SQLAlchemy today
wouldn’t have this method.
the idiom we have without a begin() is:
with session.transaction:
# etc
which is actually what happens when you say “with session.begin()” in any
case.
I don’t have a strong sense of what additional context managers would be
broadly useful and obvious, and I don’t want to discourage individual
projects from working out exactly the idioms that are appropriate to their
own codebases which is what happens when more frameworky things like extra
context managers are added.
and it's used like this:
factory =
contextual_factory = make_dbsess_contextual(factory)
with contextual_factory('rollback') as dbsess:
do_stuff(dbsess)
however it would be nice if the functionality could be integrated directly.
I also strongly suspect that the way I've approached this is non-optimal.
I looked at the code for how this might work and I have some ideas but I
thought I'd check here first to see if I'm completely off-base.
--
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.
[sqlalchemy] session.transaction / session.begin and always rolling back
For a variety of reasons, sometimes it's nice to be able to do something
like this:
with dbsess.begin(rollback=True):
do_stuff
Where the expected behavior is that everything in the context runs in a
transaction, but upon exiting the context we issue a rollback. Sometimes
this is useful to *guarantee* that actions that take place within the
context are transactionally read-only.
I had authored and am using this:
def make_dbsess_contextual(sess_factory):
@contextmanager
def dbsess(on_success='commit'):
sess = sess_factory()
# we could use 'with sess.begin() here but it's
# easier to have parity
sess.begin()
try:
yield sess
except:
sess.rollback()
sess.close()
raise
else:
if on_success == 'commit':
sess.commit()
else:
sess.rollback()
sess.close()
return dbsess
and it's used like this:
factory =
contextual_factory = make_dbsess_contextual(factory)
with contextual_factory('rollback') as dbsess:
do_stuff(dbsess)
however it would be nice if the functionality could be integrated directly.
I also strongly suspect that the way I've approached this is non-optimal.
I looked at the code for how this might work and I have some ideas but I
thought I'd check here first to see if I'm completely off-base.
--
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
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
