TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

[rahul-dwiwedi]

Getting "TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'" while executing query on databricks.

Environment:
OS: Mac
Python: 3.9.6

Requirements:

certifi==2023.11.17  
charset-normalizer==3.3.2
databricks-sql-connector==3.0.1
et-xmlfile==1.1.0
idna==3.6
lz4==4.3.3
numpy==1.26.3
oauthlib==3.2.2
openpyxl==3.1.2
pandas==2.2.0
pyarrow==14.0.2
python-dateutil==2.8.2
python-dotenv==1.0.0
pytz==2023.3.post1
requests==2.31.0
six==1.16.0
SQLAlchemy==2.0.25
thrift==0.16.0
typing_extensions==4.9.0
tzdata==2023.4
urllib3==2.1.0

Traceback:

Traceback (most recent call last):
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/connect2.py", line 28, in <module>
    data = result.fetchall()
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 1325, in fetchall
    return self._allrows()
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 551, in _allrows
    rows = self._fetchall_impl()
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py", line 2103, in _fetchall_impl
    return self.cursor_strategy.fetchall(self, self.cursor)
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py", line 1139, in fetchall
    self.handle_exception(result, dbapi_cursor, e)
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py", line 1080, in handle_exception
    result.connection._handle_dbapi_exception(
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2347, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py", line 1135, in fetchall
    rows = dbapi_cursor.fetchall()
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/databricks/sql/client.py", line 935, in fetchall
    return self.active_result_set.fetchall()
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/databricks/sql/client.py", line 1209, in fetchall
    return self._convert_arrow_table(self.fetchall_arrow())
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/databricks/sql/client.py", line 1149, in _convert_arrow_table
    res = df.to_numpy(na_value=None)
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-packages/pandas/core/frame.py", line 1981, in to_numpy
    result = self._mgr.as_array(dtype=dtype, copy=copy, na_value=na_value)
  File "/Users/rahul/Desktop/Projects/Data-Access-RND/databricks/venv/lib/python3.9/site-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 number, not 'NoneType'

As per my initial findings. when the query result has all the integer columns the df to numpy conversion is raising this issue.
res = df.to_numpy(na_value=None)

Example Query to recreate this issue. SELECT 1

[susodapop]

We'll have a fix for this merged into the connector shortly. Meanwhile, you can fix this by pinning your pandas dependency to pandas 2.1.x.

The same issue happened with dbt-databricks (which depends on this connector) and was fixed in databricks/dbt-databricks#564

[susodapop]

The fix will be incorporated into the 3.0.2 release shortly.

[aru-trackunit]

Out of curiosity is the fix also included in version < 3.0.0? I've executed that on version 2.9.5 and pandas 2.2.1 and I still get this issue.
Is fixing it meaning using pandas lower than 2.2?

[susodapop]

I don't think this was back-ported. So you will need to pin to a lower pandas version even on databricks-sql-connector <= 3.0.0