Re: [sqlalchemy] polymorphic inheritance and unique constraints
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_attach
table.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 | Polluxrich...@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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
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_attach
table.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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
oops, i forgot to comment out the fk to the parent table and now it
doesn't work: sqlalchemy.exc.NoForeignKeysError: Can't find any foreign
key relationships between 'container_instance' and 'container_aggregation'.
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/RxS8Lzft
i'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:
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,
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.
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_on
then 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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
well, i'm almost given up ... i'm using concrete now, but it seems that something isn't right. the error: sqlalchemy.exc.ArgumentError: When configuring property 'updated_by' on Mapper|ContainerInstance|pjoin, column 'container_instance.fk_updated_by' is not represented in the mapper's table. Use the `column_property()` function to force this column to be mapped as a read-only attribute. now, what makes me a little hopeless: 1. i have a base object (a simple object), that have some attributes that i want in ALL of my tables (created_at, updated_at, created_by, upated_by), which all of them are @declared_attr; 2. my base object is a declarative_base which uses the object above described as the cls parameter; 3. then, i inherit AbstractConcreteBase and my declarative object to the parent class, having all FKs in it as @declared_attr too; 4. from bla import *, exception. ps: using ConcreteBase, the error is: AttributeError: type object 'ContainerInstance' has no attribute '__mapper__' On 04/15/2015 03:13 PM, Richard Gerd Kuesters wrote: oh, right, concrete! abstract concrete can also do the trick? On 04/15/2015 03:10 PM, Mike Bayer wrote: On 4/15/15 1:59 PM, Richard Gerd Kuesters wrote: oops, i forgot to comment out the fk to the parent table and now it doesn't work: sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'container_instance' and 'container_aggregation'. well, it doesn't need it if it's inherited (both db and software level), right? correct, you'd use a concrete setup here from a SQLA perspective. -- 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.
Re: [sqlalchemy] polymorphic inheritance and unique constraints
oh, right, concrete! abstract concrete can also do the trick? On 04/15/2015 03:10 PM, Mike Bayer wrote: On 4/15/15 1:59 PM, Richard Gerd Kuesters wrote: oops, i forgot to comment out the fk to the parent table and now it doesn't work: sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'container_instance' and 'container_aggregation'. well, it doesn't need it if it's inherited (both db and software level), right? correct, you'd use a concrete setup here from a SQLA perspective. -- 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] polymorphic inheritance and unique constraints
On 4/15/15 1:59 PM, Richard Gerd Kuesters wrote:
oops, i forgot to comment out the fk to the parent table and now it
doesn't work: sqlalchemy.exc.NoForeignKeysError: Can't find any
foreign key relationships between 'container_instance' and
'container_aggregation'.
well, it doesn't need it if it's inherited (both db and software
level), right?
correct, you'd use a concrete setup here from a SQLA perspective.
On 04/15/2015 02:55 PM, Richard Gerd Kuesters wrote:
the table definitions are listed here: http://pastebin.com/RxS8Lzft
i'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:
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,
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.
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_on
then 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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
On 4/15/15 1:07 PM, Richard Gerd Kuesters wrote:
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,
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.
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_on
then 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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
the table definitions are listed here: http://pastebin.com/RxS8Lzft
i'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:
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,
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.
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_on
then 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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
yup, i know (this pattern) it is not ideal; i was just testing the new features of sa 1.0 regarding postgres (since i'm actually hands-on). i should rewrite a whole part of my model (and listeners and extensions and so on), which are already working with polymorphism. i will change this pattern, but for now i had to know where i can go with those features to reach any other gain in postgres. i listed my steps in 1,2,3,4 so it's easier to spot where my mistake was (because i was sure it was mine). :) when i got the time this kind of implementation deserves, then i'll hook up on rewriting the model. thanks for the support and sorry if this wasted your time! cheers, richard. On 04/15/2015 04:09 PM, Mike Bayer wrote: The pattern you're doing is not what Posgresql INHERITS is really intended for. PG's feature is intended for transparent sharding of data to different tablespaces, not to simulate OR-mapped class hierarchies. The keyword is mis-named in this regard. Concrete inh is in all cases a tough road to travel because it's difficult to relate things to a whole set of tables which each act as the table for a class. -- 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] polymorphic inheritance and unique constraints
On 4/15/15 2:44 PM, Richard Gerd Kuesters wrote: well, i'm almost given up ... i'm using concrete now, but it seems that something isn't right. the error: sqlalchemy.exc.ArgumentError: When configuring property 'updated_by' on Mapper|ContainerInstance|pjoin, column 'container_instance.fk_updated_by' is not represented in the mapper's table. Use the `column_property()` function to force this column to be mapped as a read-only attribute. now, what makes me a little hopeless: 1. i have a base object (a simple object), that have some attributes that i want in ALL of my tables (created_at, updated_at, created_by, upated_by), which all of them are @declared_attr; 2. my base object is a declarative_base which uses the object above described as the cls parameter; 3. then, i inherit AbstractConcreteBase and my declarative object to the parent class, having all FKs in it as @declared_attr too; 4. from bla import *, exception. ps: using ConcreteBase, the error is: AttributeError: type object 'ContainerInstance' has no attribute '__mapper__' The pattern you're doing is not what Posgresql INHERITS is really intended for. PG's feature is intended for transparent sharding of data to different tablespaces, not to simulate OR-mapped class hierarchies. The keyword is mis-named in this regard. Concrete inh is in all cases a tough road to travel because it's difficult to relate things to a whole set of tables which each act as the table for a class. On 04/15/2015 03:13 PM, Richard Gerd Kuesters wrote: oh, right, concrete! abstract concrete can also do the trick? On 04/15/2015 03:10 PM, Mike Bayer wrote: On 4/15/15 1:59 PM, Richard Gerd Kuesters wrote: oops, i forgot to comment out the fk to the parent table and now it doesn't work: sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'container_instance' and 'container_aggregation'. well, it doesn't need it if it's inherited (both db and software level), right? correct, you'd use a concrete setup here from a SQLA perspective. -- 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 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. 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] polymorphic inheritance and unique constraints
sorry, i mean i couldn't test it earlier, when i first asked the
question :) it was not another co-worker, lol.
cheers,
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_attach
table.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 | Polluxrich...@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),
Re: [sqlalchemy] polymorphic inheritance and unique constraints
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_attach
table.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 | Polluxrich...@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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
well, this didn't work with upstream 1.0 - sorry, I was in another
project and couldn't test it myself.
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_attach
table.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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
hell yeah! that's exactly what i was looking for :)
is it in the 1.0.0b3 or upstream?
best regards,
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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
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
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] polymorphic inheritance and unique constraints
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.
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.
Re: [sqlalchemy] polymorphic inheritance and unique constraints
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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
thanks again, Mike!
just a question: to make the constraint in the parent, shouldn't i move
other columns that composes the constraint to the parent too?
cheers,
richard.
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
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.
