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 "<string>", line 1, in <lambda>

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

In fact, when I check members of the Model, I see `extend_existing` added `id` even though `id_` was already defined to represent the actual column named `id`:

```

In [7]: dir(Model1)

Out[7]: 

[

 'classes',

 'id',

 'id_',

 'prepare',

 ...

]

```

Please help. Many thanks in advance!

[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.

[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

[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)

<ipython-input-3-e682f00753bc> in <module>

      1 unique = object()

----> 2 class TestId:

      3     id = 'something else'

      4     unique_id = id(unique)

      5 

<ipython-input-3-e682f00753bc> in TestId()

      2 class TestId:

      3     id = 'something else'

----> 4     unique_id = id(unique)

      5 

      6 

TypeError: 'str' object is not callable

```

[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()

--

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/d362d460-0013-4070-a8dd-4f8f2ef6dcc2n%40googlegroups.com.

[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())

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/712ed4dc-2654-45e1-b3d0-3b1c612dc35a%40www.fastmail.com.

[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