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] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Richard Damon]

On 8/22/20 12:09 PM, Vitaly Kruglikov wrote:
> Hi Richard. I wish it was that simple, but it's not. Here is an
> example of how using a builtin name breaks:
>
> ```
> In [3]: unique = object()
>    ...: class TestId:
>    ...:     id = 'something else'
>    ...:     unique_id = id(unique)
>    ...: 
>    ...: 
>
Which would be the expected problem with hiding global names, but you
could do

unique = object()

real_id = id

class TestId:

  id = 'something else'

  unique_id = real_id(unique)


The other option might be to put the column definitions into the
table_args for the table (but that loses the column object)

-- 
Richard Damon

-- 
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/9b0e37af-68b9-1569-64cd-4c0e1a185d4a%40Damon-Family.org.

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] ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Mike Bayer]

changing the "key" of a column during reflection is not a process that's ever 
been supported before.   you would be better off using a synonym here:

from sqlalchemy.orm import Session, synonym


_AutomapBase = automap.automap_base()


class Model1(_AutomapBase):
__tablename__ = "model1"

id_ = synonym("id")
tag = Column(String())




On Sat, Aug 22, 2020, at 12:14 PM, Mike Bayer wrote:
> Hi Im not able to reproduce this, though I will grant things dont seem to 
> work very well in this area, would need to know *exactly* what it is you are 
> trying to accomplish. please alter the MCVE below to reproduce your error, it 
> passes for me however does have a warning:
> 
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import Integer
> from sqlalchemy import String
> from sqlalchemy.ext import automap
> from sqlalchemy.orm import Session
> 
> 
> _AutomapBase = automap.automap_base()
> 
> 
> class Model1(_AutomapBase):
> __tablename__ = "model1"
> 
> id_ = Column(
> "id", Integer, primary_key=True, autoincrement=True, key="id_"
> )
> tag = Column(String())
> 
> 
> e = create_engine("sqlite://", echo=True)
> with e.connect() as conn:
> conn.execute("create table model1 (id integer primary key, tag varchar)")
> 
> _AutomapBase.metadata.reflect(bind=e, only=["model1"], extend_existing=True)
> _AutomapBase.prepare()
> 
> row = Model1(tag="tag1")
> 
> 
> s = Session(e)
> s.add(row)
> s.flush()
> 
> 
> 
> 
> On Sat, Aug 22, 2020, at 10:43 AM, Vitaly Kruglikov wrote:
>> Dear all,
>> 
>> I am using:
>> sqlalchemy==1.3.18
>> psycopg2==2.8.4
>> connection url schema: "postgresql+psycopg2://..."
>> postgres 10.x
>> 
>> 
>> My code looks like this:
>> 
>> ```
>> _AutomapBase = automap.automap_base()
>> 
>> class Model1(_AutomapBase):
>> **__tablename__ = 'model1"
>> 
>> id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
>>key='id_')
>> tag = sa.Column(sa.String())
>> 
>> _AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'], 
>> extend_existing=True)
>> _AutomapBase.prepare()
>> 
>> row = Model1(tag='tag1')
>> orm_session.add(attempt)
>> orm_session.flush()
>> ```
>> 
>> I get the exception `ArgumentError: Only one Column may be marked 
>> autoincrement=True, found both id and id.` when I run the following insert:
>> ```
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>>  line 2523, in flush
>> self._flush(objects)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>>  line 2664, in _flush
>> transaction.rollback(_capture_exception=True)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
>>  line 68, in __exit__
>> compat.raise_(
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
>>  line 178, in raise_
>> raise exception
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>>  line 2624, in _flush
>> flush_context.execute()
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>>  line 422, in execute
>> rec.execute(self)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>>  line 586, in execute
>> persistence.save_obj(
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>>  line 239, in save_obj
>> _emit_insert_statements(
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>>  line 1135, in _emit_insert_statements
>> result = cached_connections[connection].execute(
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
>>  line 1014, in execute
>> return meth(self, multiparams, params)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>>  line 298, in _execute_on_connection
>> return connection._execute_clauseelement(self, multiparams, params)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
>>  line 1108, in _execute_clauseelement
>> compiled_sql = elem.compile(
>>   File "", line 1, in 
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>>  line 476, in compile
>> return self._compiler(dialect, bind=bind, **kw)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>>  line 482, in _compiler
>> return dialect.statement_compiler(dialect, self, **kw)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>>  line 590, in __init__
>> Compiled.__init__(self, dialect, statement, **kwargs)
>>   File 
>> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>>  line 319, in 

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 
> 

Re: [sqlalchemy] ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Mike Bayer]

Hi Im not able to reproduce this, though I will grant things dont seem to work 
very well in this area, would need to know *exactly* what it is you are trying 
to accomplish. please alter the MCVE below to reproduce your error, it passes 
for me however does have a warning:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext import automap
from sqlalchemy.orm import Session


_AutomapBase = automap.automap_base()


class Model1(_AutomapBase):
__tablename__ = "model1"

id_ = Column(
"id", Integer, primary_key=True, autoincrement=True, key="id_"
)
tag = Column(String())


e = create_engine("sqlite://", echo=True)
with e.connect() as conn:
conn.execute("create table model1 (id integer primary key, tag varchar)")

_AutomapBase.metadata.reflect(bind=e, only=["model1"], extend_existing=True)
_AutomapBase.prepare()

row = Model1(tag="tag1")


s = Session(e)
s.add(row)
s.flush()




On Sat, Aug 22, 2020, at 10:43 AM, Vitaly Kruglikov wrote:
> Dear all,
> 
> I am using:
> sqlalchemy==1.3.18
> psycopg2==2.8.4
> connection url schema: "postgresql+psycopg2://..."
> postgres 10.x
> 
> 
> My code looks like this:
> 
> ```
> _AutomapBase = automap.automap_base()
> 
> class Model1(_AutomapBase):
> **__tablename__ = 'model1"
> 
> id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
>key='id_')
> tag = sa.Column(sa.String())
> 
> _AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'], 
> extend_existing=True)
> _AutomapBase.prepare()
> 
> row = Model1(tag='tag1')
> orm_session.add(attempt)
> orm_session.flush()
> ```
> 
> I get the exception `ArgumentError: Only one Column may be marked 
> autoincrement=True, found both id and id.` when I run the following insert:
> ```
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
> line 2523, in flush
> self._flush(objects)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
> line 2664, in _flush
> transaction.rollback(_capture_exception=True)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
>  line 68, in __exit__
> compat.raise_(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", 
> line 178, in raise_
> raise exception
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
> line 2624, in _flush
> flush_context.execute()
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>  line 422, in execute
> rec.execute(self)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>  line 586, in execute
> persistence.save_obj(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>  line 239, in save_obj
> _emit_insert_statements(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>  line 1135, in _emit_insert_statements
> result = cached_connections[connection].execute(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", 
> line 1014, in execute
> return meth(self, multiparams, params)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 298, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", 
> line 1108, in _execute_clauseelement
> compiled_sql = elem.compile(
>   File "", line 1, in 
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 476, in compile
> return self._compiler(dialect, bind=bind, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 482, in _compiler
> return dialect.statement_compiler(dialect, self, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  line 590, in __init__
> Compiled.__init__(self, dialect, statement, **kwargs)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  line 319, in __init__
> self.string = self.process(self.statement, **compile_kwargs)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  line 350, in process
> return obj._compiler_dispatch(self, **kwargs)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py",
>  line 95, in _compiler_dispatch
> return meth(self, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  line 2427, in visit_insert
> crud_params = crud._setup_crud_params(
>   File 
> 

Re: [sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Vitaly Kruglikov]

Hi Richard. I wish it was that simple, but it's not. Here is an example of 
how using a builtin name breaks:

```
In [3]: unique = object()
   ...: class TestId:
   ...: id = 'something else'
   ...: unique_id = id(unique)
   ...: 
   ...: 
---
TypeError Traceback (most recent call last)
 in 
  1 unique = object()
> 2 class TestId:
  3 id = 'something else'
  4 unique_id = id(unique)
  5 

 in TestId()
  2 class TestId:
  3 id = 'something else'
> 4 unique_id = id(unique)
  5 
  6 

TypeError: 'str' object is not callable
```

On Saturday, August 22, 2020 at 8:09:19 AM UTC-7 Richard Damon wrote:

> On 8/22/20 10:46 AM, Vitaly Kruglikov wrote:
> > I suspect this has something to do with the combination of the
> > explicit definition of the `id_` column and reflection, but don't know
> > how to fix. I really need to keep the explicit `id_` descriptor and
> > shouldn't rename it to `id` because that's a reserved python word.
> >
> I would note that 'id' is NOT a reserved word (aka key-word) in Python,
> but the name of a built-in. As such id(xx) [which uses the built in] and
> obj.id [which can reference the id member of that object] are not
> incompatible. Don't use it as a variable name, as that would cause
> issues, but in an explicit scope like a class it works.
>
> -- 
> Richard Damon
>
>

-- 
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/9fb87feb-203f-43a5-a449-77fe815262b3n%40googlegroups.com.

Re: [sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Richard Damon]

On 8/22/20 10:46 AM, Vitaly Kruglikov wrote:
> I suspect this has something to do with the combination of the
> explicit definition of the `id_` column and reflection, but don't know
> how to fix. I really need to keep the explicit `id_` descriptor and
> shouldn't rename it to `id` because that's a reserved python word.
>
I would note that 'id' is NOT a reserved word (aka key-word) in Python,
but the name of a built-in. As such id(xx) [which uses the built in] and
obj.id [which can reference the id member of that object] are not
incompatible. Don't use it as a variable name, as that would cause
issues, but in an explicit scope like a class it works.

-- 
Richard Damon

-- 
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/be9c0312-0fff-c543-1434-4550c00ed7a1%40Damon-Family.org.

[sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Vitaly Kruglikov]

Please note, that I posted, then deleted a message with the same subject in 
order to correct some relevant information, and this post replaces the one 
I deleted.

On Saturday, August 22, 2020 at 7:43:42 AM UTC-7 Vitaly Kruglikov wrote:

> Dear all,
>
> I am using:
> sqlalchemy==1.3.18
> psycopg2==2.8.4
> connection url schema: "postgresql+psycopg2://..."
> postgres 10.x
>
>
> My code looks like this:
>
> ```
> _AutomapBase = automap.automap_base()
>
> class Model1(_AutomapBase):
> __tablename__ = 'model1"
>
> id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
>key='id_')
> tag = sa.Column(sa.String())
>
> _AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'], 
> extend_existing=True)
> _AutomapBase.prepare()
>
> row = Model1(tag='tag1')
> orm_session.add(attempt)
> orm_session.flush()
> ```
>
> I get the exception `ArgumentError: Only one Column may be marked 
> autoincrement=True, found both id and id.` when I run the following insert:
> ```
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
> line 2523, in flush
> self._flush(objects)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
> line 2664, in _flush
> transaction.rollback(_capture_exception=True)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
>  
> line 68, in __exit__
> compat.raise_(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", 
> line 178, in raise_
> raise exception
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
> line 2624, in _flush
> flush_context.execute()
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>  
> line 422, in execute
> rec.execute(self)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>  
> line 586, in execute
> persistence.save_obj(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>  
> line 239, in save_obj
> _emit_insert_statements(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>  
> line 1135, in _emit_insert_statements
> result = cached_connections[connection].execute(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", 
> line 1014, in execute
> return meth(self, multiparams, params)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 298, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", 
> line 1108, in _execute_clauseelement
> compiled_sql = elem.compile(
>   File "", line 1, in 
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 476, in compile
> return self._compiler(dialect, bind=bind, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 482, in _compiler
> return dialect.statement_compiler(dialect, self, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  
> line 590, in __init__
> Compiled.__init__(self, dialect, statement, **kwargs)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  
> line 319, in __init__
> self.string = self.process(self.statement, **compile_kwargs)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  
> line 350, in process
> return obj._compiler_dispatch(self, **kwargs)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py",
>  
> line 95, in _compiler_dispatch
> return meth(self, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>  
> line 2427, in visit_insert
> crud_params = crud._setup_crud_params(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
> line 64, in _setup_crud_params
> return _get_crud_params(compiler, stmt, **kw)
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
> line 158, in _get_crud_params
> _scan_cols(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
> line 346, in _scan_cols
> _append_param_insert_pk_returning(
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
> line 457, in _append_param_insert_pk_returning
> elif c is stmt.table._autoincrement_column or c.server_default is not 
> None:
>   File 
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", 
> line 779, in _autoincrement_column
> return 

[sqlalchemy] ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert

[Vitaly Kruglikov]

Dear all,

I am using:
sqlalchemy==1.3.18
psycopg2==2.8.4
connection url schema: "postgresql+psycopg2://..."
postgres 10.x


My code looks like this:

```
_AutomapBase = automap.automap_base()

class Model1(_AutomapBase):
__tablename__ = 'model1"

id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
   key='id_')
tag = sa.Column(sa.String())

_AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'], 
extend_existing=True)
_AutomapBase.prepare()

row = Model1(tag='tag1')
orm_session.add(attempt)
orm_session.flush()
```

I get the exception `ArgumentError: Only one Column may be marked 
autoincrement=True, found both id and id.` when I run the following insert:
```
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
line 2523, in flush
self._flush(objects)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
line 2664, in _flush
transaction.rollback(_capture_exception=True)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
 
line 68, in __exit__
compat.raise_(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", 
line 178, in raise_
raise exception
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", 
line 2624, in _flush
flush_context.execute()
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
 
line 422, in execute
rec.execute(self)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
 
line 586, in execute
persistence.save_obj(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
 
line 239, in save_obj
_emit_insert_statements(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
 
line 1135, in _emit_insert_statements
result = cached_connections[connection].execute(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", 
line 1014, in execute
return meth(self, multiparams, params)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", 
line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", 
line 1108, in _execute_clauseelement
compiled_sql = elem.compile(
  File "", line 1, in 
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", 
line 476, in compile
return self._compiler(dialect, bind=bind, **kw)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", 
line 482, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", 
line 590, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", 
line 319, in __init__
self.string = self.process(self.statement, **compile_kwargs)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", 
line 350, in process
return obj._compiler_dispatch(self, **kwargs)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", 
line 95, in _compiler_dispatch
return meth(self, **kw)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", 
line 2427, in visit_insert
crud_params = crud._setup_crud_params(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
line 64, in _setup_crud_params
return _get_crud_params(compiler, stmt, **kw)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
line 158, in _get_crud_params
_scan_cols(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
line 346, in _scan_cols
_append_param_insert_pk_returning(
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py", 
line 457, in _append_param_insert_pk_returning
elif c is stmt.table._autoincrement_column or c.server_default is not 
None:
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", 
line 779, in _autoincrement_column
return self.primary_key._autoincrement_column
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
 
line 883, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
  File 
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", 
line 3706, in _autoincrement_column
raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Only one Column may be marked 
autoincrement=True, found both id and id.
```

I suspect this has something to do with the combination