connection arguments are not recognized

[DSLituiev]

I'm running

%sql --connection_arguments '{"schema": "omop"}' {connection_str}

and getting "No transaction in progress" error:

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1785 
-> 1786             result = context._setup_result_proxy()
   1787 

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py in _setup_result_proxy(self)
   1405         if self.is_crud or self.is_text:
-> 1406             result = self._setup_dml_or_text_result()
   1407         else:

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py in _setup_dml_or_text_result(self)
   1487         cursor_description = (
-> 1488             strategy.alternate_cursor_description or self.cursor.description
   1489         )

/opt/conda/lib/python3.7/site-packages/pyhive/presto.py in description(self)
    225         self._fetch_while(
--> 226             lambda: self._columns is None and
    227             self._state not in (self._STATE_NONE, self._STATE_FINISHED)

/opt/conda/lib/python3.7/site-packages/pyhive/common.py in _fetch_while(self, fn)
     45         while fn():
---> 46             self._fetch_more()
     47             if fn():

/opt/conda/lib/python3.7/site-packages/pyhive/presto.py in _fetch_more(self)
    307         """Fetch the next URI and update state"""
--> 308         self._process_response(self._requests_session.get(self._nextUri, **self._requests_kwargs))
    309 

/opt/conda/lib/python3.7/site-packages/pyhive/presto.py in _process_response(self, response)
    348         if 'error' in response_json:
--> 349             raise DatabaseError(response_json['error'])
    350 

DatabaseError: {'message': 'No transaction in progress', 'errorCode': 21, 'errorName': 'NOT_IN_TRANSACTION', 'errorType': 'USER_ERROR', 'failureInfo': {'type': 'com.facebook.presto.spi.PrestoException', 'message': 'No transaction in progress', 'suppressed': [], 'stack': ['com.facebook.presto.execution.CommitTask.execute(CommitTask.java:44)', 'com.facebook.presto.execution.CommitTask.execute(CommitTask.java:30)', 'com.facebook.presto.execution.DataDefinitionExecution.start(DataDefinitionExecution.java:181)', 'com.facebook.presto.$gen.Presto_0_240_1_amzn_0____20210709_150854_1.run(Unknown Source)', 'com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:242)', 'com.facebook.presto.dispatcher.LocalDispatchQuery.lambda$startExecution$5(LocalDispatchQuery.java:115)', 'java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)', 'java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)', 'java.lang.Thread.run(Thread.java:748)']}}

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
<ipython-input-32-cd4c0a076fb2> in <module>
----> 1 get_ipython().run_line_magic('sql', 'SELECT * FROM omop.condition_occurrence LIMIT 10')

/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py in run_line_magic(self, magic_name, line, _stack_depth)
   2325                 kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2326             with self.builtin_trap:
-> 2327                 result = fn(*args, **kwargs)
   2328             return result
   2329 

<decorator-gen-127> in execute(self, line, cell, local_ns)

/opt/conda/lib/python3.7/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

<decorator-gen-126> in execute(self, line, cell, local_ns)

/opt/conda/lib/python3.7/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

/opt/conda/lib/python3.7/site-packages/sql/magic.py in execute(self, line, cell, local_ns)
    215 
    216         try:
--> 217             result = sql.run.run(conn, parsed["sql"], self, user_ns)
    218 
    219             if (

/opt/conda/lib/python3.7/site-packages/sql/run.py in run(conn, sql, config, user_namespace)
    366                 txt = sqlalchemy.sql.text(statement)
    367                 result = conn.session.execute(txt, user_namespace)
--> 368             _commit(conn=conn, config=config)
    369             if result and config.feedback:
    370                 print(interpret_rowcount(result.rowcount))

/opt/conda/lib/python3.7/site-packages/sql/run.py in _commit(conn, config)
    344     if _should_commit:
    345         try:
--> 346             conn.session.execute("commit")
    347         except sqlalchemy.exc.OperationalError:
    348             pass  # not all engines can commit

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   1250                 params,
   1251                 _EMPTY_EXECUTION_OPTS,
-> 1252                 future=False,
   1253             )
   1254 

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _exec_driver_sql(self, statement, multiparams, params, execution_options, future)
   1551             execution_options,
   1552             statement,
-> 1553             distilled_parameters,
   1554         )
   1555 

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1812         except BaseException as e:
   1813             self._handle_dbapi_exception(
-> 1814                 e, statement, parameters, cursor, context
   1815             )
   1816 

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1993             elif should_wrap:
   1994                 util.raise_(
-> 1995                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   1996                 )
   1997             else:

/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205 
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1784             context.post_exec()
   1785 
-> 1786             result = context._setup_result_proxy()
   1787 
   1788             if not self._is_future:

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py in _setup_result_proxy(self)
   1404     def _setup_result_proxy(self):
   1405         if self.is_crud or self.is_text:
-> 1406             result = self._setup_dml_or_text_result()
   1407         else:
   1408             strategy = self.cursor_fetch_strategy

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py in _setup_dml_or_text_result(self)
   1486             )
   1487         cursor_description = (
-> 1488             strategy.alternate_cursor_description or self.cursor.description
   1489         )
   1490         if cursor_description is None:

/opt/conda/lib/python3.7/site-packages/pyhive/presto.py in description(self)
    224         # Sleep until we're done or we got the columns
    225         self._fetch_while(
--> 226             lambda: self._columns is None and
    227             self._state not in (self._STATE_NONE, self._STATE_FINISHED)
    228         )

/opt/conda/lib/python3.7/site-packages/pyhive/common.py in _fetch_while(self, fn)
     44     def _fetch_while(self, fn):
     45         while fn():
---> 46             self._fetch_more()
     47             if fn():
     48                 time.sleep(self._poll_interval)

/opt/conda/lib/python3.7/site-packages/pyhive/presto.py in _fetch_more(self)
    306     def _fetch_more(self):
    307         """Fetch the next URI and update state"""
--> 308         self._process_response(self._requests_session.get(self._nextUri, **self._requests_kwargs))
    309 
    310     def _decode_binary(self, rows):

/opt/conda/lib/python3.7/site-packages/pyhive/presto.py in _process_response(self, response)
    347             self._state = self._STATE_FINISHED
    348         if 'error' in response_json:
--> 349             raise DatabaseError(response_json['error'])
    350 
    351 

DatabaseError: (pyhive.exc.DatabaseError) {'message': 'No transaction in progress', 'errorCode': 21, 'errorName': 'NOT_IN_TRANSACTION', 'errorType': 'USER_ERROR', 'failureInfo': {'type': 'com.facebook.presto.spi.PrestoException', 'message': 'No transaction in progress', 'suppressed': [], 'stack': ['com.facebook.presto.execution.CommitTask.execute(CommitTask.java:44)', 'com.facebook.presto.execution.CommitTask.execute(CommitTask.java:30)', 'com.facebook.presto.execution.DataDefinitionExecution.start(DataDefinitionExecution.java:181)', 'com.facebook.presto.$gen.Presto_0_240_1_amzn_0____20210709_150854_1.run(Unknown Source)', 'com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:242)', 'com.facebook.presto.dispatcher.LocalDispatchQuery.lambda$startExecution$5(LocalDispatchQuery.java:115)', 'java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)', 'java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)', 'java.lang.Thread.run(Thread.java:748)']}}
[SQL: commit]
(Background on this error at: http://sqlalche.me/e/14/4xp6)

at the same time, sqlalchemy works well with the same parameters:

user = os.environ["USER"]
connection_str = f'presto://{user}@presto:{port}/hive'
engine = create_engine(connection_str,
                       connect_args={"schema": "omop"}