connection arguments are not recognized
DSLituiev opened this issue · 0 comments
DSLituiev commented
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"}