Repository: trinodb/trino-python-client

Issue:
SQLAlchemy connection does not work after latest release
### Expected behavior

connection engine connects successfully 

```
engine = create_engine('trino://user:pass@trino.server.io:443/data')
connection = engine.connect()
```

### Actual behavior

Following code snippet returns error

```
engine = create_engine('trino://user:pass@trino.server.io:443/data')
connection = engine.connect()

rows = connection.execute(text("SELECT * FROM data.schema.table limit 5"))

print(rows.fetchall())
```

PRODUCES:
Traceback (most recent call last):
```
  File "/git/trino-python-client/main.py", line 19, in <module>
    rows = connection.execute(text("SELECT * FROM data.schema.table limit 5"))
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/sql/elements.py", line 523, in _execute_on_connection
    return connection._execute_clauseelement(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
        dialect,
    ...<8 lines>...
        cache_hit=cache_hit,
    )
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/trino/sqlalchemy/dialect.py", line 442, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/trino/dbapi.py", line 629, in execute
    self._iterator = iter(self._query.execute())
                          ~~~~~~~~~~~~~~~~~~~^^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/trino/client.py", line 886, in execute
    status = self._request.process(response)
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/trino/client.py", line 684, in process
    self.raise_response_error(http_response)
    ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^
  File "/Library/Caches/pypoetry/virtualenvs/trino-client-TtW9DZEh-py3.13/lib/python3.13/site-packages/trino/client.py", line 675, in raise_response_error
    raise exceptions.HttpError(
    ...<4 lines>...
    )
trino.exceptions.HttpError: error 404: b'404 page not found\n'
```

### Steps To Reproduce

Trino python client:
trino 0.334.0
Trino cluster (version 472)

### Log output

_No response_

### Operating System

macOS Sequoia Version 15.1

### Trino Python client version

0.334.0

### Trino Server version

472

### Python version

Python 3.13.2

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

Discussion hints:

Solution patch:
diff --git a/trino/dbapi.py b/trino/dbapi.py
index 9d2e277..fb73cc3 100644
--- a/trino/dbapi.py
+++ b/trino/dbapi.py
@@ -150,7 +150,7 @@ class Connection:
         schema=constants.DEFAULT_SCHEMA,
         session_properties=None,
         http_headers=None,
-        http_scheme=constants.HTTP,
+        http_scheme=None,
         auth=constants.DEFAULT_AUTH,
         extra_credential=None,
         max_attempts=constants.DEFAULT_MAX_ATTEMPTS,
@@ -202,7 +202,18 @@ class Connection:
         else:
             self._http_session = http_session
         self.http_headers = http_headers
-        self.http_scheme = http_scheme if not parsed_host.scheme else parsed_host.scheme
+
+        # Set http_scheme
+        if parsed_host.scheme:
+            self.http_scheme = parsed_host.scheme
+        elif http_scheme:
+            self.http_scheme = http_scheme
+        elif port == constants.DEFAULT_TLS_PORT:
+            self.http_scheme = constants.HTTPS
+        elif port == constants.DEFAULT_PORT:
+            self.http_scheme = constants.HTTP
+        else:
+            self.http_scheme = constants.HTTP
 
         # Infer connection port: `hostname` takes precedence over explicit `port` argument
         # If none is given, use default based on HTTP protocol


Test patch:
diff --git a/tests/unit/test_dbapi.py b/tests/unit/test_dbapi.py
index 49731d1..080a390 100644
--- a/tests/unit/test_dbapi.py
+++ b/tests/unit/test_dbapi.py
@@ -14,6 +14,7 @@ import uuid
 from unittest.mock import patch
 
 import httpretty
+import pytest
 from httpretty import httprettified
 from requests import Session
 
@@ -314,3 +315,26 @@ def test_description_is_none_when_cursor_is_not_executed():
     connection = Connection("sample_trino_cluster:443")
     with connection.cursor() as cursor:
         assert hasattr(cursor, 'description')
+
+
+@pytest.mark.parametrize(
+    "host, port, http_scheme_input_argument, http_scheme_set",
+    [
+        # Infer from hostname
+        ("https://mytrinoserver.domain:9999", None, None, constants.HTTPS),
+        ("http://mytrinoserver.domain:9999", None, None, constants.HTTP),
+        # Infer from port
+        ("mytrinoserver.domain", constants.DEFAULT_TLS_PORT, None, constants.HTTPS),
+        ("mytrinoserver.domain", constants.DEFAULT_PORT, None, constants.HTTP),
+        # http_scheme parameter has higher precedence than port parameter
+        ("mytrinoserver.domain", constants.DEFAULT_TLS_PORT, constants.HTTP, constants.HTTP),
+        ("mytrinoserver.domain", constants.DEFAULT_PORT, constants.HTTPS, constants.HTTPS),
+        # Set explicitly by http_scheme parameter
+        ("mytrinoserver.domain", None, constants.HTTPS, constants.HTTPS),
+        # Default
+        ("mytrinoserver.domain", None, None, constants.HTTP),
+    ],
+)
+def test_setting_http_scheme(host, port, http_scheme_input_argument, http_scheme_set):
+    connection = Connection(host, port, http_scheme=http_scheme_input_argument)
+    assert connection.http_scheme == http_scheme_set