[sqlalchemy] execute("select 1") gives TypeError: int() argument must be a string, a bytes-like object or a real number, not 'NoneType'

[tildesrc]

When I run

db = sqlalchemy.create_engine('my-databricks-sql-warehouse')
db.execute('select 1').scalar()

I get

  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 1276, in scalar
    return self._only_one_row(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 559, in _only_one_row
    row = onerow(hard_close=True)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 1816, in _fetchone_impl
    return self.cursor_strategy.fetchone(self, self.cursor, hard_close)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 974, in fetchone
    self.handle_exception(result, dbapi_cursor, e)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 955, in handle_exception
    result.connection._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2138, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 969, in fetchone
    row = dbapi_cursor.fetchone()
  File "/usr/local/lib/python3.10/dist-packages/databricks/sql/client.py", line 684, in fetchone
    return self.active_result_set.fetchone()
  File "/usr/local/lib/python3.10/dist-packages/databricks/sql/client.py", line 934, in fetchone
    res = self._convert_arrow_table(self.fetchmany_arrow(1))
  File "/usr/local/lib/python3.10/dist-packages/databricks/sql/client.py", line 884, in _convert_arrow_table
    res = df.to_numpy(na_value=None)
  File "/usr/local/lib/python3.10/dist-packages/pandas/core/frame.py", line 1981, in to_numpy
    result = self._mgr.as_array(dtype=dtype, copy=copy, na_value=na_value)
  File "/usr/local/lib/python3.10/dist-packages/pandas/core/internals/managers.py", line 1702, in as_array
    arr[isna(arr)] = na_value
TypeError: int() argument must be a string, a bytes-like object or a real number, not 'NoneType'

Same result with execute("select count(*) from my_table")

[kravets-levko]

Hi @tildesrc! Which version of this library and sqlalchemy you use?

[tildesrc]

Hey @kravets-levko. It's databricks-sql-connector==2.9.4 and SQLAlchemy==1.4.51.

[susodapop]

Hi @tildesrc does your code work if you don't use .scalar()?

[tildesrc]

Hi @tildesrc does your code work if you don't use .scalar()?

@susodapop, I can get the cursor without an issue but as soon as I try to get a result from the cursor I get the same error as scalar:

>>> cursor = db.execute('select 1')
>>> next(cursor)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 1006, in __next__
    return self._next_impl()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 640, in _next_impl
    row = self._onerow_getter(self)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 457, in onerow
    row = self._fetchone_impl()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 1816, in _fetchone_impl
    return self.cursor_strategy.fetchone(self, self.cursor, hard_close)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 974, in fetchone
    self.handle_exception(result, dbapi_cursor, e)
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 955, in handle_exception
    result.connection._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2138, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 969, in fetchone
    row = dbapi_cursor.fetchone()
  File "/usr/local/lib/python3.10/dist-packages/databricks/sql/client.py", line 684, in fetchone
    return self.active_result_set.fetchone()
  File "/usr/local/lib/python3.10/dist-packages/databricks/sql/client.py", line 934, in fetchone
    res = self._convert_arrow_table(self.fetchmany_arrow(1))
  File "/usr/local/lib/python3.10/dist-packages/databricks/sql/client.py", line 884, in _convert_arrow_table
    res = df.to_numpy(na_value=None)
  File "/usr/local/lib/python3.10/dist-packages/pandas/core/frame.py", line 1981, in to_numpy
    result = self._mgr.as_array(dtype=dtype, copy=copy, na_value=na_value)
  File "/usr/local/lib/python3.10/dist-packages/pandas/core/internals/managers.py", line 1702, in as_array
    arr[isna(arr)] = na_value
TypeError: int() argument must be a string, a bytes-like object or a real number, not 'NoneType'

It also might be helpful to know that there is no issue running db.execute('select 1, 2').one(), db.execute('select "1"').scalar(), db.execute('select 1.0').scalar(), db.execute('select true').scalar() or db.execute('select NULL').scalar().

[susodapop]

What version of Pandas do you have installed?

[susodapop]

If you pin to pandas 2.1.0 I believe this will work for you.

[tildesrc]

It looks like we're using pandas==2.2.0, we'll try 2.1.0 and see if that helps.

[tildesrc]

@susodapop we downgraded pandas to 2.1.0 and that fixed the issue. Thanks!