No exception when trying to connect to server

[shraik]

No exception thrown when trying to connect to obviously invalid address.
How to check that the server is available?

Package            Version
------------------ -----------
crate              2.0.0
geojson            3.2.0
greenlet           3.2.1
orjson             3.10.18
pip                25.1.1
SQLAlchemy         2.0.40
sqlalchemy-cratedb 0.42.0.dev2
typing_extensions  4.13.2
urllib3            2.4.0
verlib2            0.3.1

Test example:

import sqlalchemy as sa
from sqlalchemy.exc import SQLAlchemyError

# incorrect address
cons = "crate://user:a_secret_password@crate.airflow.local:4201?ssl=false"
# correct address: 
#cons = "crate://user:a_secret_password@crate.airflow.local:4200?ssl=false"

try:
    engine = sa.create_engine(cons, echo=False, connect_args={"timeout": 3.0})
    engine.connect()
    print("success connect to DB")
except SQLAlchemyError as err:
    print("error", err.__cause__)  # this will give what kind of error

tables = engine.connect().execute(sa.text("SHOW TABLES;")).all()
print(tables)

Out:

success connect to DB
Removed server http://crate.airflow.local:4201 from active pool
Traceback (most recent call last):
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connection.py", line 198, in _new_conn
    sock = connection.create_connection(
        (self._dns_host, self.port),
    ...<2 lines>...
        socket_options=self.socket_options,
    )
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\util\connection.py", line 85, in create_connection
    raise err
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\util\connection.py", line 73, in create_connection
    sock.connect(sa)
    ~~~~~~~~~~~~^^^^
ConnectionRefusedError: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение

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

Traceback (most recent call last):
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connectionpool.py", line 787, in urlopen
    response = self._make_request(
        conn,
    ...<10 lines>...
        **response_kw,
    )
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connectionpool.py", line 493, in _make_request
    conn.request(
    ~~~~~~~~~~~~^
        method,
        ^^^^^^^
    ...<6 lines>...
        enforce_content_length=enforce_content_length,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connection.py", line 445, in request
    self.endheaders()
    ~~~~~~~~~~~~~~~^^
  File "C:\Program Files\Python313\Lib\http\client.py", line 1333, in endheaders
    self._send_output(message_body, encode_chunked=encode_chunked)
    ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Program Files\Python313\Lib\http\client.py", line 1093, in _send_output
    self.send(msg)
    ~~~~~~~~~^^^^^
  File "C:\Program Files\Python313\Lib\http\client.py", line 1037, in send
    self.connect()
    ~~~~~~~~~~~~^^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connection.py", line 276, in connect
    self.sock = self._new_conn()
                ~~~~~~~~~~~~~~^^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connection.py", line 213, in _new_conn
    raise NewConnectionError(
        self, f"Failed to establish a new connection: {e}"
    ) from e
urllib3.exceptions.NewConnectionError: <urllib3.connection.HTTPConnection object at 0x000001D93D34D260>: Failed to establish a new connection: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение

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

Traceback (most recent call last):
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 587, in _request
    response = self.server_pool[next_server].request(
        method,
    ...<5 lines>...
        **kwargs,
    )
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 196, in request
    return self.pool.urlopen(
           ~~~~~~~~~~~~~~~~~^
        method,
        ^^^^^^^
    ...<4 lines>...
        **kwargs,
        ^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connectionpool.py", line 871, in urlopen
    return self.urlopen(
           ~~~~~~~~~~~~^
        method,
        ^^^^^^^
    ...<13 lines>...
        **response_kw,
        ^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connectionpool.py", line 871, in urlopen
    return self.urlopen(
           ~~~~~~~~~~~~^
        method,
        ^^^^^^^
    ...<13 lines>...
        **response_kw,
        ^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connectionpool.py", line 871, in urlopen
    return self.urlopen(
           ~~~~~~~~~~~~^
        method,
        ^^^^^^^
    ...<13 lines>...
        **response_kw,
        ^^^^^^^^^^^^^^
    )
    ^
  [Previous line repeated 7 more times]
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\connectionpool.py", line 841, in urlopen
    retries = retries.increment(
        method, url, error=new_e, _pool=self, _stacktrace=sys.exc_info()[2]
    )
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\urllib3\util\retry.py", line 519, in increment
    raise MaxRetryError(_pool, url, reason) from reason  # type: ignore[arg-type]
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
urllib3.exceptions.MaxRetryError: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: /_sql?types=true (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x000001D93D34D260>: Failed to establish a new connection: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение'))

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy_cratedb\dialect.py", line 268, in do_execute
    result = cursor.execute(statement, parameters)
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\cursor.py", line 57, in execute
    self._result = self.connection.client.sql(
                   ~~~~~~~~~~~~~~~~~~~~~~~~~~^
        sql, parameters, bulk_parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 508, in sql
    content = self._json_request("POST", self.path, data=data)
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 639, in _json_request
    response = self._request(method, path, data=data)
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 630, in _request
    self._drop_server(next_server, ex_message)
    ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 700, in _drop_server
    raise ConnectionError(
    ...<2 lines>...
    )
crate.client.exceptions.ConnectionError: No more Servers available, exception from last server: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: /_sql?types=true (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x000001D93D34D260>: Failed to establish a new connection: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение'))

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

Traceback (most recent call last):
  File "C:\python\cratedb_tests\cratedb-conn.py", line 15, in <module>
    tables = engine.connect().execute(sa.text("SHOW TABLES;")).all()
             ~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1416, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 523, in _execute_on_connection
    return connection._execute_clauseelement(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
        dialect,
    ...<8 lines>...
        cache_hit=cache_hit,
    )
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1843, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 2352, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\sqlalchemy_cratedb\dialect.py", line 268, in do_execute
    result = cursor.execute(statement, parameters)
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\cursor.py", line 57, in execute
    self._result = self.connection.client.sql(
                   ~~~~~~~~~~~~~~~~~~~~~~~~~~^
        sql, parameters, bulk_parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 508, in sql
    content = self._json_request("POST", self.path, data=data)
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 639, in _json_request
    response = self._request(method, path, data=data)
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 630, in _request
    self._drop_server(next_server, ex_message)
    ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\python\cratedb_tests\.venv\Lib\site-packages\crate\client\http.py", line 700, in _drop_server
    raise ConnectionError(
    ...<2 lines>...
    )
sqlalchemy.exc.OperationalError: (crate.client.exceptions.ConnectionError) No more Servers available, exception from last server: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: /_sql?types=true (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x000001D93D34D260>: Failed to establish a new connection: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение'))
[SQL: SHOW TABLES;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[amotl]

Hi @shraik,

thank you for writing in. It looks like the code correctly raises exceptions as displayed in your output?

ConnectionRefusedError: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение

sqlalchemy.exc.OperationalError: (crate.client.exceptions.ConnectionError) No more Servers available, exception from last server: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: /_sql?types=true (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x000001D93D34D260>: Failed to establish a new connection: [WinError 10061] Подключение не установлено, т.к. конечный компьютер отверг запрос на подключение'))

With kind regards,
Andreas.

[shraik]

let me clarify my question.

I took this article as an example and expected similar behavior:
https://stackoverflow.com/questions/41887344/how-to-verify-sqlalchemy-engine-object

the output "success connect to DB" shows that the exception was not thrown when
engine.connect()
was executed. It creates the illusion that the connection was established.

The error you specified, ConnectionRefusedError, is thrown when executing the line from the example above.
tables = engine.connect().execute(sa.text("SHOW TABLES;")).all()

Maybe my example was not very indicative. Let's adjust it like this. The output will remain unchanged.

import sys
import sqlalchemy as sa
from sqlalchemy.exc import SQLAlchemyError


cons = "crate://user:a_secret_password@crate.airflow.local:4201?ssl=false"
# cons = "crate://user:a_secret_password@crate.airflow.local:4200?ssl=false"

try:
    engine = sa.create_engine(cons, echo=False, connect_args={"timeout": 3.0})
    connection = engine.connect()
    print("success connect to DB")
except SQLAlchemyError as err:
    print("error", err.__cause__)  # this will give what kind of error
    sys.exit(1)

# we have connection, let's get data

tables = connection.execute(sa.text("SHOW TABLES;")).all()
print(tables)

[amotl]

Dear @shraik,

thanks for your report. We are able to confirm the behaviour your are observing. Contrary to that, other than a deviating TCP connection handling, when connecting to an instance of PostgreSQL per "postgresql://localhost/", SQLAlchemy emits those SQL statements, which would also make the procedure fail when there is no database server to connect to.

SET datestyle TO 'ISO'

SELECT t.oid,
       typarray
FROM pg_type t
JOIN pg_namespace ns ON typnamespace = ns.oid
WHERE typname = 'hstore';

In the case of CrateDB, no SQL commands are emitted, and the connection is HTTP-based, so it apparently only fails when actually trying to invoke SQL statements, and not on connect() already.

I think the situation should be improved by actually emitting a (dummy) SQL statement, to make the connection fail likewise on connect() already when applicable.

With kind regards,
Andreas.

[amotl]

Hi again,

we found the deviation is already originating at the lower level DBAPI driver, so we submitted a corresponding improvement to that project.

• Fix: Fail early when database cluster does not respond crate-python#711

With kind regards,
Andreas.

[amotl]

Dear @shraik,

your issue may have been solved by recent improvements to the Python driver. We just published a pre-release package 2.1.0.dev0, see also crate/crate-python#711 (comment).

Because sqlalchemy-cratedb uses a version constraint for this package like "crate>=2,<3", you should be able to install the pre-release alongside without facing any obstacles.

For example, because you are currently also using a pre-release of the SQLAlchemy dialect, use this command to install both packages:

uv pip install --upgrade 'crate>=2.1.0.dev0' 'sqlalchemy-cratedb>=0.42.0.dev2'

With kind regards,
Andreas.

[shraik]

Great! The exception is thrown correctly. But there is one nuance, can you check the operation of the "timeout" parameter?
I get a strange delay with throwing an exception when changing the parameter:

engine = sa.create_engine(cons, echo=False) =>
error ["Server not available, exception: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: / (Caused by ConnectTimeoutError(<urllib3.connection.HTTPConnection object at 0x00000180F5662F10>, 'Connection to crate.airflow.local timed out. (connect timeout=None)'))"]
Elapsed time: 0:03:52.292546

engine = sa.create_engine(cons, echo=False, connect_args={"timeout": 1}) =>
error ["Server not available, exception: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: / (Caused by ConnectTimeoutError(<urllib3.connection.HTTPConnection object at 0x000001EA64912F10>, 'Connection to crate.airflow.local timed out. (connect timeout=1)'))"]
Elapsed time: 0:00:11.255726

engine = sa.create_engine(cons, echo=False, connect_args={"timeout": 2})
error ["Server not available, exception: HTTPConnectionPool(host='crate.airflow.local', port=4201): Max retries exceeded with url: / (Caused by ConnectTimeoutError(<urllib3.connection.HTTPConnection object at 0x000001F525D72F10>, 'Connection to crate.airflow.local timed out. (connect timeout=2)'))"]
Elapsed time: 0:00:22.450192

Timeout

Connection timeouts (in seconds) can be configured with the optional timeout argument:

connection = client.connect(..., timeout=5)

import sys
import sqlalchemy as sa
from sqlalchemy.exc import SQLAlchemyError
from datetime import datetime


cons = "crate://user:a_secret_password@crate.airflow.local:4201?ssl=false"


start = datetime.now()
try:
    engine = sa.create_engine(cons, echo=False, connect_args={"timeout": 2})

    connection = engine.connect()
    print("success connect to DB")
except SQLAlchemyError as err:
    print("error", err.__cause__)  # this will give what kind of error
    print("Elapsed time:", datetime.now() - start)
    sys.exit(1)

# we have connection, let's get data
tables = connection.execute(sa.text("SHOW TABLES;")).all()
print(tables)

[amotl]

Hi @shraik. Your observations might be related to the backoff-factor, if I am evaluating your question right that you are asking about this detail?

I get a strange delay with throwing an exception when changing the parameter.

Can you clarify what is strange here? Is the exception not how you would expect it to happen, or otherwise, which delay is not how you would expect it, and why?

NB: I think it will be good to discuss this on a separate issue?

[amotl]

Hi again.

Great! The exception is thrown correctly.

Thanks for your feedback!

Can you check the operation of the "timeout" parameter?

I've created a dedicated ticket about your other question.

• Operation of the "timeout" parameter flawed? #219