declared_attr not working with Postgres HSTORE

51 views
Skip to first unread message

Saakshaat Singh

unread,
Aug 21, 2020, 8:06:22 PM8/21/20
to sqlalchemy
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!

Mike Bayer

unread,
Aug 22, 2020, 12:19:19 PM8/22/20
to noreply-spamdigest via sqlalchemy
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()
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.

Mike Bayer

unread,
Aug 22, 2020, 12:30:11 PM8/22/20
to noreply-spamdigest via sqlalchemy
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.

Saakshaat Singh

unread,
Aug 22, 2020, 12:33:06 PM8/22/20
to sqlalchemy
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?

Saakshaat Singh

unread,
Aug 22, 2020, 12:36:13 PM8/22/20
to sqlalchemy
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.

Mike Bayer

unread,
Aug 22, 2020, 12:49:33 PM8/22/20
to noreply-spamdigest via sqlalchemy


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.





Mike Bayer

unread,
Aug 22, 2020, 12:52:33 PM8/22/20
to noreply-spamdigest via sqlalchemy


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!




Reply all
Reply to author
Forward
0 new messages