Re: [sqlalchemy] declared_attr not working with Postgres HSTORE

[Mike Bayer]

On Sat, Aug 22, 2020, at 12:36 PM, Saakshaat Singh wrote:
> 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 
>  
> 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.

unfortunately not.   Your example illustrates only a single table named 
"parent".   I have no information on the actual database schema you are 
attempting to map towards. The MCVE ( see link at the bottom of this email) 
is a means of communicating exactly the code that fails to run and how the 
error is produced.  Within the SQLAlchemy team it's not a good use of our 
time to try to guess what it is someone is trying to do so we ask that users 
illustrate everything up front to reproduce the error.  thanks!




> 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*

Re: [sqlalchemy] declared_attr not working with Postgres HSTORE

[Mike Bayer]

On Sat, Aug 22, 2020, at 12:33 PM, Saakshaat Singh wrote:
> 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?

Most likely but I was not able to make the mappings run, and once I saw what 
you were doing with the columns I don't see a way to make those mappings work.





> 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))*
>>> **
>>> *__mapper_args__ = {*
>>> *'polymorphic_identity': ChildType.sub_2*
>>> *}*
>>> **
>>> *class 

Re: [sqlalchemy] declared_attr not working with Postgres HSTORE

[Saakshaat Singh]

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 
 
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))*
> 
> *__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, 

Re: [sqlalchemy] declared_attr not working with Postgres HSTORE

[Saakshaat Singh]

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))*
> 
> *__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 

Re: [sqlalchemy] declared_attr not working with Postgres HSTORE

[Mike Bayer]

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))*
>> **
>> *__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 

Re: [sqlalchemy] declared_attr not working with Postgres HSTORE

[Mike Bayer]

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))*
> **
> *__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 
>