Re: [sqlalchemy] Automatically set cascade settings based on "ON DELETE" / "ON UPDATE" when reflecting?
I wasn't advocating making this connection "in general" (though I like the autoconfigure option!), but only specifically for the case of reflection - in this case, we know the DB supports it, and it would result in a better python interface to the already existing tables. On Thu, Jul 3, 2014 at 3:20 PM, Mike Bayer wrote: > > On 7/3/14, 6:15 PM, Mike Bayer wrote: > > On 7/3/14, 5:45 PM, Paul Molodowitch wrote: > >> I noticed that sqlalchemy now properly sets the onpudate / ondelete > >> properties of foreign keys when reflecting tables: > >> > >> > https://bitbucket.org/zzzeek/sqlalchemy/issue/2183/support-on-delete-update-in-foreign-key > >> > >> However, it doesn't seem to set the cascade properties of > >> relationships to reflect these properties. ie, if the Child table > >> references the Parent table with a foreign key that has "ON DELETE > >> CASCADE", and the reference column does not allow NULL, when you > >> delete a parent table that has children, you will get an error, > >> because sqlalchemy will try to set the child's ref to NULL. > >> > >> ideally we should add "delete" in the relationship's cascade > >> properties (and probably delete-orphan as well), and then set > >> passive_updates=True. > >> > >> Or am I missing something obvious / doing something wrong / etc? > > the configuration of a Column or ForeignKey has never been directly > > linked to how relationship() gets configured. passive_updates in > > particular is a thorny one as not every database supports ON UPDATE > > CASCADE, but for that matter not every database even supports ON DELETE > > CASCADE. There's also lots of variants to ON UPDATE and ON DELETE and > > SQLAlchemy has no awareness of any of these directly. > > > > If we were to explore some automatic configuration of relationship based > > on these attributes of ForeignKey, it would take place within the > > automap extension: see > > http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html. > > > > There are also recipes such that both relationship() and ForeignKey() > > are generated at once, these are also good places for this kind of thing > > to happen. See > > > https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/a6d96575bc497ce0c952bb81db9c05d054c98bb5/atmcraft/model/meta/orm.py?at=master > > for an example of this, I still am thinking of a way recipes like this > > could also be integrated into SQLAlchemy, possibly as an enhancement to > > declarative. > > or a flag like "autoconfigure=True" on relationship(). this would also > set up innerjoin=True for joined eager loading if the FK is not null. > if the primaryjoin condition is too complex (has mulitple FKs), > autoconfigure would raise an exception. > > > -- > 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/WaVTCpBOVPk/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.
Re: [sqlalchemy] Automatically set cascade settings based on "ON DELETE" / "ON UPDATE" when reflecting?
On 7/3/14, 6:15 PM, Mike Bayer wrote: > On 7/3/14, 5:45 PM, Paul Molodowitch wrote: >> I noticed that sqlalchemy now properly sets the onpudate / ondelete >> properties of foreign keys when reflecting tables: >> >> https://bitbucket.org/zzzeek/sqlalchemy/issue/2183/support-on-delete-update-in-foreign-key >> >> However, it doesn't seem to set the cascade properties of >> relationships to reflect these properties. ie, if the Child table >> references the Parent table with a foreign key that has "ON DELETE >> CASCADE", and the reference column does not allow NULL, when you >> delete a parent table that has children, you will get an error, >> because sqlalchemy will try to set the child's ref to NULL. >> >> ideally we should add "delete" in the relationship's cascade >> properties (and probably delete-orphan as well), and then set >> passive_updates=True. >> >> Or am I missing something obvious / doing something wrong / etc? > the configuration of a Column or ForeignKey has never been directly > linked to how relationship() gets configured. passive_updates in > particular is a thorny one as not every database supports ON UPDATE > CASCADE, but for that matter not every database even supports ON DELETE > CASCADE. There's also lots of variants to ON UPDATE and ON DELETE and > SQLAlchemy has no awareness of any of these directly. > > If we were to explore some automatic configuration of relationship based > on these attributes of ForeignKey, it would take place within the > automap extension: see > http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html. > > There are also recipes such that both relationship() and ForeignKey() > are generated at once, these are also good places for this kind of thing > to happen. See > https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/a6d96575bc497ce0c952bb81db9c05d054c98bb5/atmcraft/model/meta/orm.py?at=master > for an example of this, I still am thinking of a way recipes like this > could also be integrated into SQLAlchemy, possibly as an enhancement to > declarative. or a flag like "autoconfigure=True" on relationship(). this would also set up innerjoin=True for joined eager loading if the FK is not null. if the primaryjoin condition is too complex (has mulitple FKs), autoconfigure would raise an exception. -- 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] Automatically set cascade settings based on "ON DELETE" / "ON UPDATE" when reflecting?
On 7/3/14, 5:45 PM, Paul Molodowitch wrote: > I noticed that sqlalchemy now properly sets the onpudate / ondelete > properties of foreign keys when reflecting tables: > > https://bitbucket.org/zzzeek/sqlalchemy/issue/2183/support-on-delete-update-in-foreign-key > > However, it doesn't seem to set the cascade properties of > relationships to reflect these properties. ie, if the Child table > references the Parent table with a foreign key that has "ON DELETE > CASCADE", and the reference column does not allow NULL, when you > delete a parent table that has children, you will get an error, > because sqlalchemy will try to set the child's ref to NULL. > > ideally we should add "delete" in the relationship's cascade > properties (and probably delete-orphan as well), and then set > passive_updates=True. > > Or am I missing something obvious / doing something wrong / etc? the configuration of a Column or ForeignKey has never been directly linked to how relationship() gets configured. passive_updates in particular is a thorny one as not every database supports ON UPDATE CASCADE, but for that matter not every database even supports ON DELETE CASCADE. There's also lots of variants to ON UPDATE and ON DELETE and SQLAlchemy has no awareness of any of these directly. If we were to explore some automatic configuration of relationship based on these attributes of ForeignKey, it would take place within the automap extension: see http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html. There are also recipes such that both relationship() and ForeignKey() are generated at once, these are also good places for this kind of thing to happen. See https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/a6d96575bc497ce0c952bb81db9c05d054c98bb5/atmcraft/model/meta/orm.py?at=master for an example of this, I still am thinking of a way recipes like this could also be integrated into SQLAlchemy, possibly as an enhancement to declarative. -- 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] Automatically set cascade settings based on "ON DELETE" / "ON UPDATE" when reflecting?
I noticed that sqlalchemy now properly sets the onpudate / ondelete properties of foreign keys when reflecting tables: https://bitbucket.org/zzzeek/sqlalchemy/issue/2183/support-on-delete-update-in-foreign-key However, it doesn't seem to set the cascade properties of relationships to reflect these properties. ie, if the Child table references the Parent table with a foreign key that has "ON DELETE CASCADE", and the reference column does not allow NULL, when you delete a parent table that has children, you will get an error, because sqlalchemy will try to set the child's ref to NULL. ideally we should add "delete" in the relationship's cascade properties (and probably delete-orphan as well), and then set passive_updates=True. Or am I missing something obvious / doing something wrong / etc? - Paul -- 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.
