TypeError: __init__() got multiple values for argument 'cache_key'

[jasheldo]

After upgrading to sqlalchemy.__version__>=1.4.0, I've been receiving the title error.

Prior to version 1.4.0, the following would work as expected.

import os
import urllib

from sqlalchemy import create_engine

def create_connection():
    user = os.environ['USER']
    passwd = ***
    server = SERVER
    scheme = 'teradatasql'
    netloc = f'{user}:{passwd}@{server}'
    path = '/'
    query = 'logmech=LDAP&encryptdata=true'
    url = (scheme, netloc, path, '', query, '')
    cnxn_str = urllib.parse.urlunparse(url)
    engine = create_engine(cnxn_str)
    return engine

engine = create_connection()

sql = "SELECT TOP 10 * FROM SCHEMA.TABLE"
with engine.connect() as cnxn:
    data = cnxn.execute(sql).fetchall()

I'm now met with the following StackTrace:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
~/test_sqlalchemy/sqlalchemy_test.py in 
      26 sql = "SELECT TOP 10 * FROM SCHEMA.TABLE"
----> 27 with engine.connect() as cnxn:
      28     data = cnxn.execute(sql).fetchall()
      29 
      30 data

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/base.py in connect(self, close_with_result)
   3065         """
   3066 
-> 3067         return self._connection_cls(self, close_with_result=close_with_result)
   3068 
   3069     @util.deprecated(

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
     89                 connection
     90                 if connection is not None
---> 91                 else engine.raw_connection()
     92             )
     93 

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   3144 
   3145         """
-> 3146         return self._wrap_pool_connect(self.pool.connect, _connection)
   3147 
   3148 

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   3111         dialect = self.dialect
   3112         try:
-> 3113             return fn()
   3114         except dialect.dbapi.Error as e:
   3115             if connection is None:

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/impl.py in connect(self)
    381                 return rec._checkout_existing()
    382 
--> 383         return _ConnectionFairy._checkout(self, self._fairy)
    384 
    385     def _return_conn(self, record):

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/base.py in _checkout(cls, pool, threadconns, fairy)
    746     def _checkout(cls, pool, threadconns=None, fairy=None):
    747         if not fairy:
--> 748             fairy = _ConnectionRecord.checkout(pool)
    749 
    750             fairy._pool = pool

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/base.py in checkout(cls, pool)
    417     @classmethod
    418     def checkout(cls, pool):
--> 419         rec = pool._do_get()
    420         try:
    421             dbapi_connection = rec.get_connection()

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    363         except AttributeError:
    364             pass
--> 365         c = self._create_connection()
    366         self._conn.current = weakref.ref(c)
    367         if len(self._all_conns) >= self.size:

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/base.py in _create_connection(self)
    245         """Called by subclasses to create a new ConnectionRecord."""
    246 
--> 247         return _ConnectionRecord(self)
    248 
    249     def _invalidate(self, connection, exception=None, _checkin=True):

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/base.py in __init__(self, pool, connect)
    360         self.__pool = pool
    361         if connect:
--> 362             self.__connect(first_connect_check=True)
    363         self.finalize_callback = deque()
    364 

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/pool/base.py in __connect(self, first_connect_check)
    603                 ).exec_once_unless_exception(self.connection, self)
    604             if pool.dispatch.connect:
--> 605                 pool.dispatch.connect(self.connection, self)
    606 
    607 

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    318             fn(*args, **kw)
    319         for fn in self.listeners:
--> 320             fn(*args, **kw)
    321 
    322     def __len__(self):

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py in go(*arg, **kw)
   1640             once_fn = once.pop()
   1641             try:
-> 1642                 return once_fn(*arg, **kw)
   1643             except:
   1644                 if retry_on_exception:

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/create.py in first_connect(dbapi_connection, connection_record)
    668 
    669             try:
--> 670                 dialect.initialize(c)
    671             finally:
    672                 dialect.do_rollback(c.connection)

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/default.py in initialize(self, connection)
    356     def initialize(self, connection):
    357         try:
--> 358             self.server_version_info = self._get_server_version_info(
    359                 connection
    360             )

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/teradatasqlalchemy/dialect.py in _get_server_version_info(self, connection, **kw)
    959                 where(text('InfoKey=\'VERSION\''))
    960 
--> 961         res = connection.execute(stmt).scalar()
    962         return res
    963 

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   1198             )
   1199         else:
-> 1200             return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
   1201 
   1202     def _execute_function(self, func, multiparams, params, execution_options):

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params, execution_options)
    311     ):
    312         if self.supports_execution:
--> 313             return connection._execute_clauseelement(
    314                 self, multiparams, params, execution_options
    315             )

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params, execution_options)
   1379         )
   1380 
-> 1381         compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1382             dialect=dialect,
   1383             compiled_cache=compiled_cache,

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/sql/elements.py in _compile_w_cache(self, dialect, compiled_cache, column_keys, for_executemany, schema_translate_map, **kw)
    531             if compiled_sql is None:
    532                 cache_hit = dialect.CACHE_MISS
--> 533                 compiled_sql = self._compiler(
    534                     dialect,
    535                     cache_key=elem_cache_key,

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/sqlalchemy/sql/elements.py in _compiler(self, dialect, **kw)
    564         Dialect."""
    565 
--> 566         return dialect.statement_compiler(dialect, self, **kw)
    567 
    568     def __str__(self):

~/.local/share/virtualenvs/test_sqlalchemy-l7yAJfC_/lib/python3.9/site-packages/teradatasqlalchemy/dialect.py in __init__(self, dialect, statement, column_keys, inline, **kwargs)
     81 
     82     def __init__(self, dialect, statement, column_keys=None, inline=False, **kwargs):
---> 83         super(TeradataCompiler, self).__init__(dialect, statement, column_keys, inline, **kwargs)
     84 
     85     def get_select_precolumns(self, select, **kwargs):

TypeError: __init__() got multiple values for argument 'cache_key'

Here's my full environment for reference. Per @zzzeek, this is a Teradata issue.

Package            Version
------------------ ----------
appnope            0.1.2
backcall           0.2.0
decorator          4.4.2
greenlet           1.0.0
ipykernel          5.5.0
ipython            7.21.0
ipython-genutils   0.2.0
jedi               0.18.0
jupyter-client     6.1.12
jupyter-core       4.7.1
parso              0.8.1
pexpect            4.8.0
pickleshare        0.7.5
pip                21.0.1
prompt-toolkit     3.0.17
ptyprocess         0.7.0
pycryptodome       3.10.1
Pygments           2.8.1
python-dateutil    2.8.1
pyzmq              22.0.3
setuptools         53.0.0
six                1.15.0
SQLAlchemy         1.4.1
teradata           15.10.0.21
teradatasql        17.0.0.8
teradatasqlalchemy 17.0.0.0
tornado            6.1
traitlets          5.0.5
wcwidth            0.2.5
wheel              0.36.2

The issue I opened and close this AM, along with @zzzeek's suggested fix is here.

[zach-morris]

Can confirm I have the same issue. Temporary fix is to force SQLAlchemy==1.3.23 until this is resolved.

[jseabold]

I don't think this library is maintained anymore and the one that supersedes it is not open source or at least not developed in public afaict. I'm not going to bother trying to get anything out of teradata official support, but if you edit the dialect.py file in the installed location and remove the inline argument completely and change it to column_keys=column_keys in the super call, it will work again.

[zach-morris]

Thanks. Indeed it looks like the new pypi package for teradatasqlalchemy may have fixed this in version 17.0.0.2 I had to manually look through the package after downloading it to verify. How unfortunate they've removed the development from public view

[anandvamsee1]

I had the exact same issue. However, upon debugging, I noticed that my error was in the compiler.py file and not dialect.py file. Nevertheless, @jseabold 's solution worked perfectly. I opened the compiler.py file in the sqlalchemy site package, removed the inline argument completely and changed it to column_keys=column_keys in the super call.

[ckuo21]

Same here. There were a couple issues I noticed in my case:

1. Even though sqlalchemy-teradata is superseded by sqlalchemyteradata, it appears SQLAlchemy still looks for that package and I have to reinstall it. If not, I got this error message: Can't load plugin: sqlalchemy.dialects:teradata
2. Once I reinstalled the package, the error went away, but got the cache key error this time. Like @anandvamsee1 said, the error is in compiler.py and the solution from @jseabold works like a charm!

[Maroon1989]

Same here. After fixing the sqlalchemy to 1.3.23, everything runs normal.

[Nafisur21]

sqlalchemy to 1.3.23 worked for me. Thanks a lot Maroon1989