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))* > > * __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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/0421937b-08f0-470d-8067-43430bfc1ca8n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/0421937b-08f0-470d-8067-43430bfc1ca8n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > -- > 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+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/51688c7d-cca2-433a-8690-f0996ce5f8a7%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/51688c7d-cca2-433a-8690-f0996ce5f8a7%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > > > -- 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/be12a2bb-81bc-439b-b178-f7fc4300ce67n%40googlegroups.com.