Run SQL query with python in SQL Server raises error: SqlSatelliteCall error: Unsupported input data type in column

Question

I am trying to run Python in SQL Server and want to define a variable which contains the result of a simple select query.

exec sp_execute_external_script
@language = N'python', @script = N'

import pandas as pd
inventory = pd.DataFrame(sql_inventory)

',
@input_data_1 = N'SELECT top 10 * from inventory',
@input_data_1_name = N'sql_inventory'

The structure of the SQL server table (inventory) is as below:

ProductID numeric(18,3)
RegionID numeric(18,3)
ShopCode int
QTY float
Date varchar(6)
Amount float
ID bigint

But I get the below error: A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 1 An external script error occurred:

Unsupported input data type in column 'ProductID'. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary. SqlSatelliteCall error: Unsupported input data type in column 'ProductID'. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary. STDOUT message(s) from external script: SqlSatelliteCall function failed. Please see the console output for more information. Traceback (most recent call last): File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall", params) File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call ret = px_call(functionname, params) RuntimeError: revoscalepy function failed.

Should I specify data types every time I want to read from a query?

Answer 1

Sometimes the error messge can be misleading. In this case it's not. You're trying to use unsupported data type:

Unsupported input data type in column 'ProductID'. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary.

These two columns are causing the error:

ProductID numeric(18,3)
RegionID numeric(18,3)

According to Microsoft docs, bigint is the SQLtype equivalent to numeric Python type.

Please, try to run your code like this:

exec sp_execute_external_script
@language = N'python', @script = N'

import pandas as pd
inventory = pd.DataFrame(sql_inventory)

',
@input_data_1 = N'SELECT TOP 10 ShopCode, QTY, Date, Amount, ID FROM inventory',
@input_data_1_name = N'sql_inventory'

If I'm right, it should run fine. Since both columns ProductID and RegionID are numeric type you must either CAST or CONVERT those columns to refer to them in the query, but beware of the possibility of precision loss when converting from numeric to float or real.

Should I specify data types every time I want to read from a query?

No. Just use the supported data types and you should be fine.

Answer 2

This Microsoft article explains what is happening fairly clearly, and the error message is fairly clear as well.

What it boils down to is that Python does not support Numeric or Decimal data types, as well as some other SQL Server data types, such as DateTime or any of the Geolocation data items. Thus, to work with these data types, you have to use different techniques.

This StackOverflow item has some good information on using pandas to handle this process, as well as some other items.