Disabling SSL when using SQLAlchemy

[Miayl]

Hi,
In the README there's an example which shows how to disable SSL when using DBAPI. Is this also possible with SQLAlchemy? If yes, can an example be provided how the engine should be defined to disable SSL?

[hovaesco]

yes, this is possible, here is an example:

from sqlalchemy import create_engine
from trino.sqlalchemy import URL

engine = create_engine(
    URL(
        host="localhost",
        port=8080,
        catalog="system"
    ),
    connect_args={
       "verify": False
    }
)

[Miayl]

Thanks for the reply! I tried it out while also passing user and password:

engine = create_engine(
    URL(
    host='<host>',
    port='443',
    catalog='output',
    user=trino_user,
    password=trino_password
),
connect_args={
    'verify': False
  }
)

But then I get this error when trying to execute something. My query works with jpype.dbapi2 and DBeaver so I think it's not my query.

{
	"name": "TypeError",
	"message": "'NoneType' object is not subscriptable",
	"stack": "---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[75], line 15
     13 source_table_query = sql_generator.get_source_table_sql()
     14 conn = engine.connect()
---> 15 conn.execute(text(source_table_query)).fetchall()

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\engine\\base.py:1418, in Connection.execute(self, statement, parameters, execution_options)
   1416     raise exc.ObjectNotExecutableError(statement) from err
   1417 else:
-> 1418     return meth(
   1419         self,
   1420         distilled_parameters,
   1421         execution_options or NO_OPTIONS,
   1422     )

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\sql\\elements.py:515, in ClauseElement._execute_on_connection(self, connection, distilled_params, execution_options)
    513     if TYPE_CHECKING:
    514         assert isinstance(self, Executable)
--> 515     return connection._execute_clauseelement(
    516         self, distilled_params, execution_options
    517     )
    518 else:
    519     raise exc.ObjectNotExecutableError(self)

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\engine\\base.py:1640, in Connection._execute_clauseelement(self, elem, distilled_parameters, execution_options)
   1628 compiled_cache: Optional[CompiledCacheType] = execution_options.get(
   1629     \"compiled_cache\", self.engine._compiled_cache
   1630 )
   1632 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1633     dialect=dialect,
   1634     compiled_cache=compiled_cache,
   (...)
   1638     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1639 )
-> 1640 ret = self._execute_context(
   1641     dialect,
   1642     dialect.execution_ctx_cls._init_compiled,
   1643     compiled_sql,
   1644     distilled_parameters,
   1645     execution_options,
   1646     compiled_sql,
   1647     distilled_parameters,
   1648     elem,
   1649     extracted_params,
   1650     cache_hit=cache_hit,
   1651 )
   1652 if has_events:
   1653     self.dispatch.after_execute(
   1654         self,
   1655         elem,
   (...)
   1659         ret,
   1660     )

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\engine\\base.py:1846, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1844     return self._exec_insertmany_context(dialect, context)
   1845 else:
-> 1846     return self._exec_single_context(
   1847         dialect, context, statement, parameters
   1848     )

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\engine\\base.py:1986, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1983     result = context._setup_result_proxy()
   1985 except BaseException as e:
-> 1986     self._handle_dbapi_exception(
   1987         e, str_statement, effective_parameters, cursor, context
   1988     )
   1990 return result

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\engine\\base.py:2356, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec)
   2354     else:
   2355         assert exc_info[1] is not None
-> 2356         raise exc_info[1].with_traceback(exc_info[2])
   2357 finally:
   2358     del self._reentrant_error

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\sqlalchemy\\engine\\base.py:1967, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1965                 break
   1966     if not evt_handled:
-> 1967         self.dialect.do_execute(
   1968             cursor, str_statement, effective_parameters, context
   1969         )
   1971 if self._has_events or self.engine._has_events:
   1972     self.dispatch.after_cursor_execute(
   1973         self,
   1974         cursor,
   (...)
   1978         context.executemany,
   1979     )

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\trino\\sqlalchemy\\dialect.py:421, in TrinoDialect.do_execute(self, cursor, statement, parameters, context)
    418 def do_execute(
    419     self, cursor: Cursor, statement: str, parameters: Tuple[Any, ...], context: DefaultExecutionContext = None
    420 ):
--> 421     cursor.execute(statement, parameters)

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\trino\\dbapi.py:589, in Cursor.execute(self, operation, params)
    586 else:
    587     self._query = trino.client.TrinoQuery(self._request, query=operation,
    588                                           legacy_primitive_types=self._legacy_primitive_types)
--> 589     self._iterator = iter(self._query.execute())
    590 return self

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\trino\\client.py:783, in TrinoQuery.execute(self, additional_http_headers)
    781 except requests.exceptions.RequestException as e:
    782     raise trino.exceptions.TrinoConnectionError(\"failed to execute: {}\".format(e))
--> 783 status = self._request.process(response)
    784 self._info_uri = status.info_uri
    785 self._query_id = status.id

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\trino\\client.py:595, in TrinoRequest.process(self, http_response)
    593 response = http_response.json()
    594 if \"error\" in response:
--> 595     raise self._process_error(response[\"error\"], response.get(\"id\"))
    597 if constants.HEADER_CLEAR_SESSION in http_response.headers:
    598     for prop in get_header_values(
    599         http_response.headers, constants.HEADER_CLEAR_SESSION
    600     ):

File ~\\AppData\\Roaming\\Python\\Python311\\site-packages\\trino\\client.py:563, in TrinoRequest._process_error(self, error, query_id)
    562 def _process_error(self, error, query_id):
--> 563     error_type = error[\"errorType\"]
    564     if error_type == \"EXTERNAL\":
    565         raise exceptions.TrinoExternalError(error, query_id)

TypeError: 'NoneType' object is not subscriptable"
}