here, with "echo=True":
[...]
2015-04-15 14:26:32,859 INFO sqlalchemy.engine.base.Engine {'name':
u'user_setting'}
2015-04-15 14:26:32,860 INFO sqlalchemy.engine.base.Engine select
relname from pg_class c join pg_namespace n on n.oid=c.relnamespace
where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-04-15 14:26:32,860 INFO sqlalchemy.engine.base.Engine {'name':
u'adapter'}
2015-04-15 14:26:32,860 INFO sqlalchemy.engine.base.Engine select
relname from pg_class c join pg_namespace n on n.oid=c.relnamespace
where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-04-15 14:26:32,860 INFO sqlalchemy.engine.base.Engine {'name':
u'permission_override_history'}
2015-04-15 14:26:32,864 INFO sqlalchemy.engine.base.Engine
CREATE TABLE system_unit_setting_history (
dt_created_on TIMESTAMP WITH TIME ZONE NOT NULL,
dt_updated_on TIMESTAMP WITH TIME ZONE NOT NULL,
b_active BOOLEAN NOT NULL,
b_deleted BOOLEAN NOT NULL,
pk_system_unit_setting_id BIGSERIAL NOT NULL,
fk_system_unit_id BIGINT NOT NULL,
e_key SMALLINT NOT NULL,
u_value VARCHAR(255) NOT NULL,
fk_updated_by BIGINT,
fk_created_by BIGINT,
pk_version INTEGER NOT NULL,
PRIMARY KEY (pk_system_unit_setting_id, pk_version)
)
2015-04-15 14:26:32,864 INFO sqlalchemy.engine.base.Engine {}
2015-04-15 14:26:32,877 INFO sqlalchemy.engine.base.Engine COMMIT
2015-04-15 14:26:32,882 INFO sqlalchemy.engine.base.Engine
CREATE TABLE organization_address_history (
dt_created_on TIMESTAMP WITH TIME ZONE NOT NULL,
dt_updated_on TIMESTAMP WITH TIME ZONE NOT NULL,
b_active BOOLEAN NOT NULL,
b_deleted BOOLEAN NOT NULL,
pk_organization_address_id BIGSERIAL NOT NULL,
fk_organization_id BIGINT NOT NULL,
fk_address_id BIGINT NOT NULL,
fk_updated_by BIGINT,
fk_created_by BIGINT,
pk_version INTEGER NOT NULL,
PRIMARY KEY (pk_organization_address_id, pk_version)
)
2015-04-15 14:26:32,882 INFO sqlalchemy.engine.base.Engine {}
2015-04-15 14:26:32,894 INFO sqlalchemy.engine.base.Engine COMMIT
2015-04-15 14:26:32,903 INFO sqlalchemy.engine.base.Engine
CREATE TABLE container_aggregation (
pk_fk_container_instance_id BIGINT NOT NULL,
fk_container_descriptor_id BIGINT NOT NULL,
u_container_ean VARCHAR(20) NOT NULL,
b_generated_container_ean BOOLEAN NOT NULL,
fk_adapter_id BIGINT NOT NULL,
e_container_aggregation_status SMALLINT NOT NULL,
PRIMARY KEY (pk_fk_container_instance_id)
)
* INHERITS ( container_instance )*
2015-04-15 14:26:32,903 INFO sqlalchemy.engine.base.Engine {}
2015-04-15 14:26:32,904 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
File "database_test.py", line 425, in <module>
run()
File "database_test.py", line 114, in run
create_all()
File "database_test.py", line 95, in create_all
get_base('CORE_VPAK').metadata.create_all()
File
"/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
line 3614, in create_all
tables=tables)
[...]
On 04/15/2015 02: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, 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
"/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<[email protected]> 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
<[email protected]>
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
[email protected]
.
To post to this group, send email to
[email protected]
.
Visit this group at
http://groups.google.com/group/sqlalchemy
.
For more options, visit
https://groups.google.com/d/optout
.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
[email protected].
To post to this group, send email [email protected].
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
