Re: [sqlalchemy] declared_attr not working with Postgres HSTORE
I agree that it's not practical for saving columns with the same name but
in our case, we had to give the child subclasses the same column names.
SQLAlchemy has declared_attr
<https://www.kite.com/python/docs/sqlalchemy.ext.declarative.declared_attr>
which
converts the columns to scalar objects allowing the database to hold
different columns with the same name (making it possible only for
polymorphic models).
I hope that helps! Let me know if you have any more questions.
On Saturday, August 22, 2020 at 9:30:11 AM UTC-7 Mike Bayer wrote:
> If your model is based all on one table called "parent", it can only have
> one column called "child_value" and it can only be of a single database
> type, since that's your CREATE TABLE. I'm not able to follow what your
> example intends to do as you seem to be creating many Column objects with
> the same name and different types, all against a single table. That's not
> possible in relational databases.
>
>
> On Sat, Aug 22, 2020, at 12:18 PM, Mike Bayer wrote:
>
> Hi, I have no idea what the problem is and would need a fully runnable
> MCVE. Below is part of your test which I've tried to get running but it
> still errors out on identifiers missing and such, additionally I need a
> working example of exactly the session operations you are trying to
> achieve. It might be easier to post the working code example as a github
> question: https://github.com/sqlalchemy/sqlalchemy/issues
>
> code so far below:
>
> import enum
>
> import sqlalchemy as sa
> from sqlalchemy.dialects.postgresql import ARRAY
> from sqlalchemy.dialects.postgresql import HSTORE
> from sqlalchemy.ext.mutable import MutableDict
> from sqlalchemy.ext.declarative import declarative_base
>
> class ChildType(enum.Enum):
> sub_1 = "sub_1"
> sub_2 = "sub_2"
> sub_3 = "sub_3"
>
>
> class ParentModel(declarative_base()):
> __table__ = "parent"
> general_field = sa.Column(sa.String)
> resource_type = sa.Column(sa.Enum(ChildType))
>
> __mapper_args__ = {
> "polymorphic_identity": "parent",
> "polymorphic_on": resource_type,
> }
>
>
> class Sub1(ParentModel):
> @sa.declared_attr
> def child_value(cls):
> return ParentModel.__table__.c.get(
> "child_value", sa.Column(sa.Integer, nullable=True)
> )
>
> __mapper_args__ = {"polymorphic_identity": ChildType.sub_1}
>
>
> class Sub2(ParentModel):
> @sa.declared_attr
> def child_value(cls):
> return ParentModel.__table__.c.get(
> "child_value", sa.Column(sa.Boolean, nullable=True)
> )
>
> __mapper_args__ = {"polymorphic_identity": ChildType.sub_2}
>
>
> class Sub3(ParentModel):
> @sa.declared_attr
> def child_value(cls):
> return ParentModel.__table__.c.get(
> "child_value", sa.Column(ARRAY(MutableDict.as_mutable(HSTORE)))
> )
>
> __mapper_args__ = {"polymorphic_identity": ChildType.sub_3}
>
>
>
> e = create_engine("postgresql://scott:tiger@pg12/test", echo=True)
> ParentModel.drop_all(e)
> ParentModel.create_all(e)
> s = Session(e)
>
> s.add(Sub3(child_value=[{"foo": "bar"}]))
> s.commit()
>
>
>
>
> On Fri, Aug 21, 2020, at 8:06 PM, Saakshaat Singh wrote:
>
> Hi,
>
> I'm working with SQLAlchemy and Postgres and I have a polymorphic model
> whose subclasses have a field with the same name. To allow this field to
> co-exist with the others and not cause any name conflicts, I'm using the
> `declare_attr` decorator from SQLAlchemy.
>
> This solution works well for fields consisting of primary data types,
> however when I try to use Postgres's HSTORE to store dictionary values,
> SQLAlchemy complains with:
>
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt
> type 'dict'
>
>
> My guess is that this happens because `declared_attr` has constrains on
> which data types its fields have.
>
> Here's an example of how my models look:
>
> *import enum *
>
> *import sqlalchemy as sa*
> *from sqlalchemy.dialects.postgres import ARRAY, HSTORE*
> *from sqlalchemy.ext.mutables import MutableDict*
>
> *class ChildType(enum.Enum):*
> *sub_1 = "sub_1"*
> *sub_2 = "sub_2"*
> *sub_3 = "sub_3"*
>
> *class ParentModel(sa.declarative_base()):*
> *__table__ = 'parent'*
> *general_field = sa.Column(sa.String)*
> *r_type = sa.Column(sa.Enum(ChildType))*
>
> *
Re: [sqlalchemy] declared_attr not working with Postgres HSTORE
Thank you for looking into it Mike. I'll post an example today.
But looking at your SQLAlchemy execution, I noticed that you're only
passing a value for the `child_value` field while the Parent class is
polymorphic on the `ChildType` enum. So maybe it holds value to also pass
values for the `resource_type` and `general_field` fields?
On Saturday, August 22, 2020 at 9:19:19 AM UTC-7 Mike Bayer wrote:
> Hi, I have no idea what the problem is and would need a fully runnable
> MCVE. Below is part of your test which I've tried to get running but it
> still errors out on identifiers missing and such, additionally I need a
> working example of exactly the session operations you are trying to
> achieve. It might be easier to post the working code example as a github
> question: https://github.com/sqlalchemy/sqlalchemy/issues
>
> code so far below:
>
> import enum
>
> import sqlalchemy as sa
> from sqlalchemy.dialects.postgresql import ARRAY
> from sqlalchemy.dialects.postgresql import HSTORE
> from sqlalchemy.ext.mutable import MutableDict
> from sqlalchemy.ext.declarative import declarative_base
>
> class ChildType(enum.Enum):
> sub_1 = "sub_1"
> sub_2 = "sub_2"
> sub_3 = "sub_3"
>
>
> class ParentModel(declarative_base()):
> __table__ = "parent"
> general_field = sa.Column(sa.String)
> resource_type = sa.Column(sa.Enum(ChildType))
>
> __mapper_args__ = {
> "polymorphic_identity": "parent",
> "polymorphic_on": resource_type,
> }
>
>
> class Sub1(ParentModel):
> @sa.declared_attr
> def child_value(cls):
> return ParentModel.__table__.c.get(
> "child_value", sa.Column(sa.Integer, nullable=True)
> )
>
> __mapper_args__ = {"polymorphic_identity": ChildType.sub_1}
>
>
> class Sub2(ParentModel):
> @sa.declared_attr
> def child_value(cls):
> return ParentModel.__table__.c.get(
> "child_value", sa.Column(sa.Boolean, nullable=True)
> )
>
> __mapper_args__ = {"polymorphic_identity": ChildType.sub_2}
>
>
> class Sub3(ParentModel):
> @sa.declared_attr
> def child_value(cls):
> return ParentModel.__table__.c.get(
> "child_value", sa.Column(ARRAY(MutableDict.as_mutable(HSTORE)))
> )
>
> __mapper_args__ = {"polymorphic_identity": ChildType.sub_3}
>
>
>
> e = create_engine("postgresql://scott:tiger@pg12/test", echo=True)
> ParentModel.drop_all(e)
> ParentModel.create_all(e)
> s = Session(e)
>
> s.add(Sub3(child_value=[{"foo": "bar"}]))
> s.commit()
>
>
>
>
> On Fri, Aug 21, 2020, at 8:06 PM, Saakshaat Singh wrote:
>
> Hi,
>
> I'm working with SQLAlchemy and Postgres and I have a polymorphic model
> whose subclasses have a field with the same name. To allow this field to
> co-exist with the others and not cause any name conflicts, I'm using the
> `declare_attr` decorator from SQLAlchemy.
>
> This solution works well for fields consisting of primary data types,
> however when I try to use Postgres's HSTORE to store dictionary values,
> SQLAlchemy complains with:
>
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt
> type 'dict'
>
>
> My guess is that this happens because `declared_attr` has constrains on
> which data types its fields have.
>
> Here's an example of how my models look:
>
> *import enum *
>
> *import sqlalchemy as sa*
> *from sqlalchemy.dialects.postgres import ARRAY, HSTORE*
> *from sqlalchemy.ext.mutables import MutableDict*
>
> *class ChildType(enum.Enum):*
> *sub_1 = "sub_1"*
> *sub_2 = "sub_2"*
> *sub_3 = "sub_3"*
>
> *class ParentModel(sa.declarative_base()):*
> *__table__ = 'parent'*
> *general_field = sa.Column(sa.String)*
> *r_type = sa.Column(sa.Enum(ChildType))*
>
> *__mapper_args__ = {*
> *'polymorphic_identity': 'parent',*
> *'polymorphic_on': resource_type*
> *}*
>
>
> *class Sub1(ParentModel):*
> *@sa.declared_attr*
> *def child_value(cls):*
> *return ParentModel.__table__.c.get('child_value',
> sa.Column(sa.Integer, nullable=True))*
>
> *__mapper_args__ = {*
> *'polymorphic_identity': ChildType.sub_1*
> *}*
>
> *class Sub2(ParentModel):*
> *@sa.declared_attr*
> *def child_value(cls):*
> *return ParentModel.__table__.c.get('child_value',
> sa.Column(sa.Boolean, nullable=True))*
>
>
[sqlalchemy] declared_attr not working with Postgres HSTORE
Hi,
I'm working with SQLAlchemy and Postgres and I have a polymorphic model
whose subclasses have a field with the same name. To allow this field to
co-exist with the others and not cause any name conflicts, I'm using the
`declare_attr` decorator from SQLAlchemy.
This solution works well for fields consisting of primary data types,
however when I try to use Postgres's HSTORE to store dictionary values,
SQLAlchemy complains with:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt
type 'dict'
My guess is that this happens because `declared_attr` has constrains on
which data types its fields have.
Here's an example of how my models look:
*import enum *
*import sqlalchemy as sa*
*from sqlalchemy.dialects.postgres import ARRAY, HSTORE*
*from sqlalchemy.ext.mutables import MutableDict*
*class ChildType(enum.Enum):*
*sub_1 = "sub_1"*
*sub_2 = "sub_2"*
*sub_3 = "sub_3"*
*class ParentModel(sa.declarative_base()):*
*__table__ = 'parent'*
*general_field = sa.Column(sa.String)*
*r_type = sa.Column(sa.Enum(ChildType))*
*__mapper_args__ = {*
*'polymorphic_identity': 'parent',*
*'polymorphic_on': resource_type*
*}*
*class Sub1(ParentModel):*
*@sa.declared_attr*
*def child_value(cls):*
*return ParentModel.__table__.c.get('child_value',
sa.Column(sa.Integer, nullable=True))*
*__mapper_args__ = {*
*'polymorphic_identity': ChildType.sub_1*
*}*
*class Sub2(ParentModel):*
*@sa.declared_attr*
*def child_value(cls):*
*return ParentModel.__table__.c.get('child_value',
sa.Column(sa.Boolean, nullable=True))*
*__mapper_args__ = {*
*'polymorphic_identity': ChildType.sub_2*
*}*
*class Sub3(ParentModel):*
*@sa.declared_attr*
*def child_value(cls):*
*return ParentModel.__table__.c.get('child_value',
sa.Column(ARRAY(MutableDict.as_mutable(HSTORE*
*__mapper_args__ = {*
*'polymorphic_identity': ChildType.sub_3*
*}*
Can anyone help me out with a potential workaround/solution?
Thanks!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/0421937b-08f0-470d-8067-43430bfc1ca8n%40googlegroups.com.
