Problem with SQLAlchemy 1.4.0

[kromash]

Hello,

New version of SQLAlchemy (1.4.0) was released 15.03.2020 and since then I got following error:

 File "/layers/google.python.pip/pip/lib/python3.8/site-packages/promise/promise.py", line 489, in _resolve_from_executor
    executor(resolve, reject)
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/promise/promise.py", line 756, in executor
    return resolve(f(*args, **kwargs))
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/graphql/execution/middleware.py", line 75, in make_it_promise
    return next(*args, **kwargs)
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/graphene_sqlalchemy/fields.py", line 78, in connection_resolver
    return on_resolve(resolved)
  File "src/adtrender_api/connections.py", line 52, in resolve_connection
    return super().resolve_connection(connection_type, model, info, args, resolved)
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/graphene_sqlalchemy/fields.py", line 53, in resolve_connection
    _len = resolved.count()
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3009, in count
    return self._from_self(col).scalar()
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2753, in scalar
    ret = self.one()
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2730, in one
    return self._iter().one()
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2768, in _iter
    result = self.session.execute(
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1653, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 533, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 566, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/layers/google.python.pip/pip/lib/python3.8/site-packages/pybigquery/sqlalchemy_bigquery.py", line 137, in __init__
    super(BigQueryCompiler, self).__init__(dialect, statement, column_keys, inline, **kwargs)
TypeError: __init__() got multiple values for argument 'cache_key'" 

I rolled back to SQLAlchemy==1.3.20 and it works fine.

[choprashweta]

Facing the same problem.

[alonme]

Can you please share a code snippet which generates this error?
And also as much details as possible regarding your environment (python version, package versions, etc...)

[kromash]

Here is snippet. Just use table that you have access to

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class ExampleTable(Base):
    __tablename__ = 'dataset.example_table'
    id = Column(Integer, primary_key=True)
    name = Column(String)


def get_data():
    engine = create_engine(f'bigquery://project/dataset')
    session_maker = sessionmaker(bind=engine)
    session = session_maker()
    return session.query(ExampleTable).count()


if __name__ == '__main__':
    print(get_data())

here is requirements.txt for this example

pybigquery==0.5.0

pip freeze:

cachetools==4.2.1
certifi==2020.12.5
cffi==1.14.5
chardet==4.0.0
future==0.18.2
google-api-core==1.26.1
google-auth==1.28.0
google-cloud-bigquery==2.12.0
google-cloud-core==1.6.0
google-crc32c==1.1.2
google-resumable-media==1.2.0
googleapis-common-protos==1.53.0
greenlet==1.0.0
grpcio==1.36.1
idna==2.10
packaging==20.9
proto-plus==1.18.0
protobuf==3.15.6
pyasn1==0.4.8
pyasn1-modules==0.2.8
pybigquery==0.5.0
pycparser==2.20
pyparsing==2.4.7
pytz==2021.1
requests==2.25.1
rsa==4.7.2
six==1.15.0
SQLAlchemy==1.4.0
urllib3==1.26.4

python --version:

Python 3.8.5

[kromash]

when you change requirements.txt to:

pybigquery==0.5.0
SQLAlchemy==1.3.20

example works just fine

[alonme]

it seems like SQLAlchemy 1.4.0, also breaks a lot of other features (23 tests fail)
still not sure how big are the changes to fix those

[tswast]

I updated the maximum sqlalchemy version in 37e584e#diff-60f61ab7a8d1910d86d9fda2261620314edcae5894d5aaa236b821c7256badd7R67 to limit to 1.3.x and below.

As such, I'm converting this issue to a feature request (to support 1.4.x).

From https://docs.sqlalchemy.org/en/14/changelog/migration_14.html is sounds like there have been conceptual changes, so this may require significant work to get right.

[victorthevong]

Any update on the timeline for support for sqlalchemy 1.4?

[jimfulton]

I'm working on it now. :)

Hopefully, this week.

[alonme]

@jimfulton Started a PR, couldn't find the time to finish it until now,

Did you see it?
Willing to help finish if needed

[jimfulton]

@jimfulton Started a PR, couldn't find the time to finish it until now,

Did you see it?

I didn't until you pointed it out.

Willing to help finish if needed

I'm nearly finished with my work, which builds on:

• Adding use of the sqlalchemy dialect-compliance test suite and
• 100% unit test coverage.

My work is here: https://github.com/googleapis/python-bigquery-sqlalchemy/compare/riversnake-sqla-14

ATM, I'm trying to figure out why some code paths I added while debugging the compliance tests initially are no-longer being hit. :/

[alonme]

Lots of code there!

i did some work to rebase my branch, but i think that you implemented all the fixes i did already.

so i have a branch with those applied and where all current tests work.

does your branch also work on older versions of SQLA?

[jimfulton]

Lots of code there!

There was lots of code in the tests work. With that in place, the 1.4 port isn't too bad. :)

i did some work to rebase my branch, but i think that you implemented all the fixes i did already.

Cool.

does your branch also work on older versions of SQLA?

It should work with the latest 1.2, 1.3, and 1.4 versions.

I dropped support for 1.1 (on master) when I added comment support.

[jimfulton]

The PR: #177

More eyes would be welcome. Feel free to ask questions or to request more comments.

[jimfulton]

@alonme any interest in taking a look? Or did you already?

[jimfulton]

Release 0.8.0 supports SQLAlchemy 1.4.

[Abe410]

I am currently getting the same error.

SQLAlchemy==1.4.39
google-cloud-bigquery==3.11.0
pybigquery==0.5.0
langchain==0.0.177

Any clues as to what might be the case now?

File ~/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1306, in Connection.execute(self, statement, *multiparams, **params)
   1302     util.raise_(
   1303         exc.ObjectNotExecutableError(statement), replace_context=err
   1304     )
   1305 else:
-> 1306     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File ~/.local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py:332, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    328 def _execute_on_connection(
    329     self, connection, multiparams, params, execution_options, _force=False
    330 ):
    331     if _force or self.supports_execution:
--> 332         return connection._execute_clauseelement(
    333             self, multiparams, params, execution_options
    334         )
    335     else:
    336         raise exc.ObjectNotExecutableError(self)

File ~/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1490, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1482 schema_translate_map = execution_options.get(
   1483     "schema_translate_map", None
   1484 )
   1486 compiled_cache = execution_options.get(
   1487     "compiled_cache", self.engine._compiled_cache
   1488 )
-> 1490 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1491     dialect=dialect,
   1492     compiled_cache=compiled_cache,
   1493     column_keys=keys,
   1494     for_executemany=for_executemany,
   1495     schema_translate_map=schema_translate_map,
   1496     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1497 )
   1498 ret = self._execute_context(
   1499     dialect,
   1500     dialect.execution_ctx_cls._init_compiled,
   (...)
   1508     cache_hit=cache_hit,
   1509 )
   1510 if has_events:

File ~/.local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py:543, in ClauseElement._compile_w_cache(self, dialect, compiled_cache, column_keys, for_executemany, schema_translate_map, **kw)
    541 else:
    542     extracted_params = None
--> 543     compiled_sql = self._compiler(
    544         dialect,
    545         cache_key=elem_cache_key,
    546         column_keys=column_keys,
    547         for_executemany=for_executemany,
    548         schema_translate_map=schema_translate_map,
    549         **kw
    550     )
    552     if not dialect._supports_statement_cache:
    553         cache_hit = dialect.NO_DIALECT_SUPPORT

File ~/.local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py:565, in ClauseElement._compiler(self, dialect, **kw)
    561 def _compiler(self, dialect, **kw):
    562     """Return a compiler appropriate for this ClauseElement, given a
    563     Dialect."""
--> 565     return dialect.statement_compiler(dialect, self, **kw)

File /opt/conda/lib/python3.10/site-packages/pybigquery/sqlalchemy_bigquery.py:137, in BigQueryCompiler.__init__(self, dialect, statement, column_keys, inline, **kwargs)
    135 if isinstance(statement, Column):
    136     kwargs['compile_kwargs'] = util.immutabledict({'include_table': False})
--> 137 super(BigQueryCompiler, self).__init__(dialect, statement, column_keys, inline, **kwargs)

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

[AmineDjeghri]

i'm having the same problem

SQLCompiler.__init__() got multiple values for argument 'cache_key'

 /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ch │
│ ains/base.py:507 in run                                                      │
│                                                                              │
│   504 │   │   if args and not kwargs:                                        │
│   505 │   │   │   if len(args) != 1:                                         │
│   506 │   │   │   │   raise ValueError("`run` supports only one positional a │
│ ❱ 507 │   │   │   return self(args[0], callbacks=callbacks, tags=tags, metad │
│   508 │   │   │   │   _output_key                                            │
│   509 │   │   │   ]                                                          │
│   510                                                                        │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ch │
│ ains/base.py:312 in __call__                                                 │
│                                                                              │
│   309 │   │   │   )                                                          │
│   310 │   │   except BaseException as e:                                     │
│   311 │   │   │   run_manager.on_chain_error(e)                              │
│ ❱ 312 │   │   │   raise e                                                    │
│   313 │   │   run_manager.on_chain_end(outputs)                              │
│   314 │   │   final_outputs: Dict[str, Any] = self.prep_outputs(             │
│   315 │   │   │   inputs, outputs, return_only_outputs                       │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ch │
│ ains/base.py:306 in __call__                                                 │
│                                                                              │
│   303 │   │   )                                                              │
│   304 │   │   try:                                                           │
│   305 │   │   │   outputs = (                                                │
│ ❱ 306 │   │   │   │   self._call(inputs, run_manager=run_manager)            │
│   307 │   │   │   │   if new_arg_supported                                   │
│   308 │   │   │   │   else self._call(inputs)                                │
│   309 │   │   │   )                                                          │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ag │
│ ents/agent.py:1312 in _call                                                  │
│                                                                              │
│   1309 │   │   start_time = time.time()                                      │
│   1310 │   │   # We now enter the agent loop (until it returns something).   │
│   1311 │   │   while self._should_continue(iterations, time_elapsed):        │
│ ❱ 1312 │   │   │   next_step_output = self._take_next_step(                  │
│   1313 │   │   │   │   name_to_tool_map,                                     │
│   1314 │   │   │   │   color_mapping,                                        │
│   1315 │   │   │   │   inputs,                                               │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ag │
│ ents/agent.py:1038 in _take_next_step                                        │
│                                                                              │
│   1035 │   │   run_manager: Optional[CallbackManagerForChainRun] = None,     │
│   1036 │   ) -> Union[AgentFinish, List[Tuple[AgentAction, str]]]:           │
│   1037 │   │   return self._consume_next_step(                               │
│ ❱ 1038 │   │   │   [                                                         │
│   1039 │   │   │   │   a                                                     │
│   1040 │   │   │   │   for a in self._iter_next_step(                        │
│   1041 │   │   │   │   │   name_to_tool_map,                                 │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ag │
│ ents/agent.py:1038 in <listcomp>                                             │
│                                                                              │
│   1035 │   │   run_manager: Optional[CallbackManagerForChainRun] = None,     │
│   1036 │   ) -> Union[AgentFinish, List[Tuple[AgentAction, str]]]:           │
│   1037 │   │   return self._consume_next_step(                               │
│ ❱ 1038 │   │   │   [                                                         │
│   1039 │   │   │   │   a                                                     │
│   1040 │   │   │   │   for a in self._iter_next_step(                        │
│   1041 │   │   │   │   │   name_to_tool_map,                                 │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain/ag │
│ ents/agent.py:1134 in _iter_next_step                                        │
│                                                                              │
│   1131 │   │   │   │   if return_direct:                                     │
│   1132 │   │   │   │   │   tool_run_kwargs["llm_prefix"] = ""                │
│   1133 │   │   │   │   # We then call the tool on the tool input to get an o │
│ ❱ 1134 │   │   │   │   observation = tool.run(                               │
│   1135 │   │   │   │   │   agent_action.tool_input,                          │
│   1136 │   │   │   │   │   verbose=self.verbose,                             │
│   1137 │   │   │   │   │   color=color,                                      │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain_co │
│ re/tools.py:365 in run                                                       │
│                                                                              │
│   362 │   │   │   return observation                                         │
│   363 │   │   except (Exception, KeyboardInterrupt) as e:                    │
│   364 │   │   │   run_manager.on_tool_error(e)                               │
│ ❱ 365 │   │   │   raise e                                                    │
│   366 │   │   else:                                                          │
│   367 │   │   │   run_manager.on_tool_end(                                   │
│   368 │   │   │   │   str(observation), color=color, name=self.name, **kwarg │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain_co │
│ re/tools.py:337 in run                                                       │
│                                                                              │
│   334 │   │   try:                                                           │
│   335 │   │   │   tool_args, tool_kwargs = self._to_args_and_kwargs(parsed_i │
│   336 │   │   │   observation = (                                            │
│ ❱ 337 │   │   │   │   self._run(*tool_args, run_manager=run_manager, **tool_ │
│   338 │   │   │   │   if new_arg_supported                                   │
│   339 │   │   │   │   else self._run(*tool_args, **tool_kwargs)              │
│   340 │   │   │   )                                                          │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain_co │
│ mmunity/tools/sql_database/tool.py:62 in _run                                │
│                                                                              │
│    59 │   │   run_manager: Optional[CallbackManagerForToolRun] = None,       │
│    60 │   ) -> str:                                                          │
│    61 │   │   """Get the schema for tables in a comma-separated list."""     │
│ ❱  62 │   │   return self.db.get_table_info_no_throw(                        │
│    63 │   │   │   [t.strip() for t in table_names.split(",")]                │
│    64 │   │   )                                                              │
│    65                                                                        │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain_co │
│ mmunity/utilities/sql_database.py:455 in get_table_info_no_throw             │
│                                                                              │
│   452 │   │   demonstrated in the paper.                                     │
│   453 │   │   """                                                            │
│   454 │   │   try:                                                           │
│ ❱ 455 │   │   │   return self.get_table_info(table_names)                    │
│   456 │   │   except ValueError as e:                                        │
│   457 │   │   │   """Format the error message"""                             │
│   458 │   │   │   return f"Error: {e}"                                       │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain_co │
│ mmunity/utilities/sql_database.py:333 in get_table_info                      │
│                                                                              │
│   330 │   │   │   if self._indexes_in_table_info:                            │
│   331 │   │   │   │   table_info += f"\n{self._get_table_indexes(table)}\n"  │
│   332 │   │   │   if self._sample_rows_in_table_info:                        │
│ ❱ 333 │   │   │   │   table_info += f"\n{self._get_sample_rows(table)}\n"    │
│   334 │   │   │   if has_extra_info:                                         │
│   335 │   │   │   │   table_info += "*/"                                     │
│   336 │   │   │   tables.append(table_info)                                  │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/langchain_co │
│ mmunity/utilities/sql_database.py:356 in _get_sample_rows                    │
│                                                                              │
│   353 │   │   try:                                                           │
│   354 │   │   │   # get the sample rows                                      │
│   355 │   │   │   with self._engine.connect() as connection:                 │
│ ❱ 356 │   │   │   │   sample_rows_result = connection.execute(command)  # ty │
│   357 │   │   │   │   # shorten values in the sample rows                    │
│   358 │   │   │   │   sample_rows = list(                                    │
│   359 │   │   │   │   │   map(lambda ls: [str(i)[:100] for i in ls], sample_ │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/sqlalchemy/e │
│ ngine/base.py:1385 in execute                                                │
│                                                                              │
│   1382 │   │   │   │   exc.ObjectNotExecutableError(statement), replace_cont │
│   1383 │   │   │   )                                                         │
│   1384 │   │   else:                                                         │
│ ❱ 1385 │   │   │   return meth(self, multiparams, params, _EMPTY_EXECUTION_O │
│   1386 │                                                                     │
│   1387 │   def _execute_function(self, func, multiparams, params, execution_ │
│   1388 │   │   """Execute a sql.FunctionElement object."""                   │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/sqlalchemy/s │
│ ql/elements.py:334 in _execute_on_connection                                 │
│                                                                              │
│    331 │   │   self, connection, multiparams, params, execution_options, _fo │
│    332 │   ):                                                                │
│    333 │   │   if _force or self.supports_execution:                         │
│ ❱  334 │   │   │   return connection._execute_clauseelement(                 │
│    335 │   │   │   │   self, multiparams, params, execution_options          │
│    336 │   │   │   )                                                         │
│    337 │   │   else:                                                         │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/sqlalchemy/e │
│ ngine/base.py:1569 in _execute_clauseelement                                 │
│                                                                              │
│   1566 │   │   │   "compiled_cache", self.engine._compiled_cache             │
│   1567 │   │   )                                                             │
│   1568 │   │                                                                 │
│ ❱ 1569 │   │   compiled_sql, extracted_params, cache_hit = elem._compile_w_c │
│   1570 │   │   │   dialect=dialect,                                          │
│   1571 │   │   │   compiled_cache=compiled_cache,                            │
│   1572 │   │   │   column_keys=keys,                                         │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/sqlalchemy/s │
│ ql/elements.py:545 in _compile_w_cache                                       │
│                                                                              │
│    542 │   │   │   │   cache_hit = dialect.CACHE_HIT                         │
│    543 │   │   else:                                                         │
│    544 │   │   │   extracted_params = None                                   │
│ ❱  545 │   │   │   compiled_sql = self._compiler(                            │
│    546 │   │   │   │   dialect,                                              │
│    547 │   │   │   │   cache_key=elem_cache_key,                             │
│    548 │   │   │   │   column_keys=column_keys,                              │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/sqlalchemy/s │
│ ql/elements.py:567 in _compiler                                              │
│                                                                              │
│    564 │   │   """Return a compiler appropriate for this ClauseElement, give │
│    565 │   │   Dialect."""                                                   │
│    566 │   │                                                                 │
│ ❱  567 │   │   return dialect.statement_compiler(dialect, self, **kw)        │
│    568 │                                                                     │
│    569 │   def __str__(self):                                                │
│    570 │   │   if util.py3k:                                                 │
│                                                                              │
│ /home/ubuntu/miniconda3/envs/qback/lib/python3.11/site-packages/pybigquery/s │
│ qlalchemy_bigquery.py:137 in __init__                                        │
│                                                                              │
│   134 │   │   │   │    inline=False, **kwargs):                              │
│   135 │   │   if isinstance(statement, Column):                              │
│   136 │   │   │   kwargs['compile_kwargs'] = util.immutabledict({'include_ta │
│ ❱ 137 │   │   super(BigQueryCompiler, self).__init__(dialect, statement, col │
│   138 │                                                                      │
│   139 │   def visit_select(self, *args, **kwargs):                           │
│   140 │   │   """                                                            │
╰──────────────────────────────────────────────────────────────────────────────╯
TypeError: SQLCompiler.__init__() got multiple values for argument 'cache_key'