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

pythonsql server

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?

Best Answer

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.

Related Question