well, it doesn't need it if it's inherited (both db and software level), right?
On 04/15/2015 02:55 PM, Richard Gerd Kuesters wrote:
the table definitions are listed here: http://pastebin.com/RxS8Lzfti'm using polymorphic associations, but with inheritance (INHERITS) there's no need to do it (imho), so the fk column to the parent table (which is also the pk) can be overriden.using "add_is_dependent_on" did the trick. i didn't know of such a feature ... thanks for bring it on :) although, is there a way to use it in declarative, intead of: MyModel.__table__.add_is_dependent_on(MyParentModel.__table__) ?cheers, richard. On 04/15/2015 02:44 PM, Mike Bayer wrote:On 4/15/15 1:07 PM, Richard Gerd Kuesters wrote:what do your table defs look like? The tables are created in order of FK dependency, and up until 1.0 there was no ordering beyond that. in 1.0 they will be by table name if there are no FK dependencies.ok, now i have an issue. i don't know why, but sqlalchemy seems to issue the create table command of inherited postgresql tables before the base one in "metadata.create_all()". commenting the inherited table, issuing create all,if you're using this with INHERITS types of setups then you should establish which table is dependent on which using add_is_dependent_on():http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=add_is_dependent_on#sqlalchemy.schema.Table.add_is_dependent_onthen uncomment the table and issuing create all again seems to work, but ... it's a heck of a workaround (if i think in terms of code).i even tried to use serializable isolation level, but no result. importing models in the desired order doesn't affect the behavior either. well, i'm out of options :)a little help? best regards, richard. On 04/15/2015 11:48 AM, Richard Gerd Kuesters wrote:nevermind. i'm again victim of rtfm :) http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#postgresql-table-options great work on this, btw. it'll simplify my life *A LOT* :) best regards, richard. On 04/15/2015 10:10 AM, Richard Gerd Kuesters wrote:hello Mike!so ... ok, based on this link <http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y> (yeah yeah, well, rtfm for me), I was able to make it work like this:<code> class ContainerInstance(CoreMixin, TimestampMixin): container_instance_id = CoreMixin.column_id() parent_id = CoreMixin.column_fk(container_instance_id, nullable=False) batch_id = CoreMixin.column_fk(Batch.id_, nullable=False) container_instance_type = Column(EnumDictForInt(ContainerInstanceEnum), nullable=False, index=True) __mapper_args__ = { "polymorphic_on": container_instance_type, "polymorphic_identity": ContainerInstanceEnum.NONE } class ContainerAggregation(ContainerInstance): container_instance_id = CoreMixin.column_fk(ContainerInstance.id_, primary_key=True) container_descriptor_id = CoreMixin.column_fk(ContainerDescriptor.id_, nullable=False) # "tada!" batch_id = column_property(Column(BigInteger), ContainerInstance.batch_id) __mapper_args__ = { "polymorphic_identity": ContainerInstanceEnum.AGGREGATION } UniqueConstraint(ContainerAggregation.container_descriptor_id, ContainerAggregation.batch_id) </code>which brings me the question: I'm targeting *only* postgresql, so I have no need to pursue an agnostic approach in terms of inheritance. i do know that postgres inheritance system was discussed a lot in here, but, in my case - where i do want to have a constraint between parent and children - isn't it better to use postgres inheritance instead of duplicating the value to another table?well, i did notice the sqlalchemy example of postgres inheritance <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PostgreSQLInheritance>, which uses written ddl and triggers. the problem is that i have extra fields in the inheritance table, which I think it is not a very good approach to postgres inheritance, but, either way, from your experience, what would be your tip?ps: i found this link <http://postgresql.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html> interesting and may be another solution to this, since i already have a table descriptor (the polymorphic_on expression). of course, the approach does require an extra table, but with events I can easily make it work in sqlalchemy.cheers, richard.On 04/14/2015 08:40 AM, Richard Gerd Kuesters | Pollux Automation wrote:here, a better illustration with my actual code: http://pastebin.com/RxS8Lzft best regards, richard. On 04/13/2015 06:30 PM, Mike Bayer wrote:On 4/13/15 4:59 PM, Richard Gerd Kuesters | Pollux Automation wrote:well, this didn't work with upstream 1.0 - sorry, I was in another project and couldn't test it myself.you're not doing the same thing this user was doing in any case...Traceback (most recent call last): File "database_test.py", line 46, in <module> from plx.db.core import * File "../src/plx/db/core.py", line 901, in <module>UniqueConstraint(ContainerInstance.batch_id, ContainerAggregation.container_descriptor_id,) File "/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 2464, in __init__ ColumnCollectionMixin.__init__(self, *columns, _autoattach=_autoattach) File "/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 2393, in __init__self._check_attach()File "/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 2429, in _check_attachtable.description)sqlalchemy.exc.ArgumentError: Column(s) 'container_aggregation.fk_container_descriptor_id' are not part of table 'container_instance'.I got sqlalchemy from git, today. >>> sqlalchemy.__version__ '1.0.0'container_aggretation is a subclass of container_instance. I'm not using concrete inheritance here, may this be the problem?anything else, it's Python 2.7.9 + Linux + PostgreSQL 9.4.1. cheers, richard. On 03/24/2015 08:49 PM, Michael Bayer wrote:are these two separate constraints? I just looked and it seems like they are distinct. I just added a fix to 1.0 because someone was hacking around something similar to this. The easiest way to get these for the moment is just to create the UniqueConstraint outside of the class definition. class Foo(Base): # … class Bar(Foo): # … UniqueConstraint(Bar.x, Foo.y) that way all the columns are set up, should just work. Richard Gerd Kuesters | Pollux<rich...@pollux.com.br> wrote:well, understanding better the docs for column conflicts, can i use a declared_attr in a unique constraint? if yes, my problem is solved :) On 03/24/2015 10:33 AM, Michael Bayer wrote:Richard Gerd Kuesters | Pollux <rich...@pollux.com.br> wrote:hi all! i'm dealing with a little problem here. i have a parent table and its two inheritances. there is a value that both children have and must be unique along either types. is there a way to move this column to the parent and use a constraint in the child? my implementation is postgres 9.4+ with psycopg2 only.if this is single table inheritance then the constraint would most ideally be placed on the parent class. if you’re trying to make this “magic” such that you can semantically keep the unique constraints on the child classes, you’d need to build out a conditional approach within @declared_attr. IMO I think this is an idealized edge case that in the real world doesn’t matter much - just do what works (put the col / constraint on the base). the approach is described at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts . You’d need to make this work for both the column and the constraint.as a simple example (i'm just creating this example to simplify things), this works: ____________ class MyParent(Base): foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True) foo_name = Column(Unicode(64), nullable=False) foo_type = Column(Integer, nullable=False) __mapper_args__ = { "polymorphic_on": foo_type, "polymorphic_identity": 0 } class MyChild1(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) child1_specific_name = Column(Unicode(5), nullable=False) child1_baz_stuff = Column(Boolean, default=False) __mapper_args__ = { "polymorphic_identity": 1 } __table_args__ = ( UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is in MyChild1 ) class MyChild2(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) child2_specific_code = Column(UUID, nullable=False) child2_baz_stuff = Column(Float, nullable=False)__mapper_args__ = {"polymorphic_identity": 2 } __table_args__ = ( UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is in MyChild2 ) ____________ but i would like to do this, if possible: ____________ class MyParent(Base): foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True) foo_name = Column(Unicode(64), nullable=False) foo_type = Column(Integer, nullable=False) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) # since both child uses bar_id, why not having it on the parent? __mapper_args__ = { "polymorphic_on": foo_type, "polymorphic_identity": 0 } class MyChild1(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) child1_specific_name = Column(Unicode(5), nullable=False) child1_baz_stuff = Column(Boolean, default=False) __mapper_args__ = { "polymorphic_identity": 1 } __table_args__ = ( UniqueConstraint(MyParent.bar_id, child1_specific_name,), # will it work? ) class MyChild2(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) child2_specific_code = Column(UUID, nullable=False) child2_baz_stuff = Column(Float, nullable=False)__mapper_args__ = {"polymorphic_identity": 2 } __table_args__ = ( UniqueConstraint(MyParent.bar_id, child2_specific_code,), # will it work? ) ____________ well, will it work without being a concrete inheritance? :) best regards, richard. -- 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 tosqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email tosqlalch...@googlegroups.com. Visit this group athttp://groups.google.com/group/sqlalchemy. For more options, visithttps://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 <mailto: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 <mailto: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 <mailto: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 <mailto: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 <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto: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 <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto: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 <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto: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 <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>.Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
-- default-signature Atenciosamente, *Richard Gerd Kuesters* *Pollux Automation* Tel.: (47) 3025-9019 rich...@pollux.com.br | www.pollux.com.br <http://www.pollux.com.br/> • Linhas de Montagem • Inspeção e Testes • Robótica • Identificação e Rastreabilidade • Software para Manufatura -- 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.
