TypeError: unhashable type: 'list' with Oracle Sequence sqlalchemy cx_Oracle 7.0 regression

[sqlalchemy-bot]

Migrated issue, originally created by Dileep Jayamal

I'm trying to make id column auto-increment with Sequence in Oracle 12c as shown bellow:

class APIKey(db.Model):
     __tablename__ = 'APIKey'
    apikey_id_seq = db.Sequence('apikey_id_seq', metadata=db.Model.metadata)
    id = db.Column(db.Integer, apikey_id_seq, server_default= apikey_id_seq.next_value(),primary_key=True)
    name = db.Column(db.String(100))
    username = db.Column(db.String(20), index=True, unique=True)
    key = db.Column(db.String(40))

I created the the schema and add an object like bellow:

if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    apikey = FingerAPIKey(name='dileep', username='dileep', key='1234')
    db.session.add(apikey)
    db.session.commit()
    app.run(host=host, port=port)

During the commit I get following error:

File "/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1688, in _register_newly_persistent
    if _none_set.intersection(instance_key[1]) and \
TypeError: unhashable type: 'list'

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

here's an MCVE:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr

Base = declarative_base()


class APIKey(Base):
    __tablename__ = 'APIKey'
    apikey_id_seq = Sequence('apikey_id_seq', metadata=Base.metadata)
    id = Column(Integer, apikey_id_seq, server_default= apikey_id_seq.next_value(),primary_key=True)
    name = Column(String(100))
    username = Column(String(20), index=True, unique=True)
    key = Column(String(40))


e = create_engine("oracle://scott:tiger@oracle1120/", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)
apikey = APIKey(name='dileep', username='dileep', key='1234')
s.add(apikey)
s.commit()

I get:

ORA-00984: column not allowed here [SQL: '\nCREATE TABLE "APIKey" (\n\tid INTEGER DEFAULT apikey_id_seq.nextval NOT NULL, \n\tname VARCHAR2(100 CHAR), \n\tusername VARCHAR2(20 CHAR), \n\tkey VARCHAR2(40 CHAR), \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/4xp6)

the error in the DDL seems like a separate issue. your actual trace I can get if I take out the server_default:

Traceback (most recent call last):
  File "test.py", line 25, in <module>
    s.commit()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2350, in _flush
    flush_context.finalize_flush_changes()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 413, in finalize_flush_changes
    self.session._register_newly_persistent(other)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1683, in _register_newly_persistent
    if _none_set.intersection(instance_key[1]) and \
TypeError: unhashable type: 'list'

and that is definitely cx_Oracle regressing again as I can see he just put out 7.0.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

please downgrade to cx_Oracle < 7.0 for now, thanks

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: high priority

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "TypeError: unhashable type: 'list' with Oracle Seq" to "TypeError: unhashable type: 'list' with Oracle Seq"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.2.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/870

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Use cx_Oracle dml_ret_array_val

Fixed issue for cx_Oracle 7.0 where the behavior of Oracle param.getvalue()
now returns a list, rather than a single scalar value, breaking
autoincrement logic throughout the Core and ORM. The dml_ret_array_val
compatibility flag is used for cx_Oracle 6.3 and 6.4 to establish compatible
behavior with 7.0 and forward, for cx_Oracle 6.2.1 and prior a version
number check falls back to the old logic.

Fixes: #4335
Change-Id: Ia60f5514803a505898c1ac9252355990c6203dda

→ 67a2cd9

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Use cx_Oracle dml_ret_array_val

Fixed issue for cx_Oracle 7.0 where the behavior of Oracle param.getvalue()
now returns a list, rather than a single scalar value, breaking
autoincrement logic throughout the Core and ORM. The dml_ret_array_val
compatibility flag is used for cx_Oracle 6.3 and 6.4 to establish compatible
behavior with 7.0 and forward, for cx_Oracle 6.2.1 and prior a version
number check falls back to the old logic.

Fixes: #4335
Change-Id: Ia60f5514803a505898c1ac9252355990c6203dda
(cherry picked from commit 67a2cd9)

→ 5385898