0

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?

2

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.

|improve this answer|||||
  • Thanks Ronaldo. That was right but since the data types of the sql server are set to these types, then I have to cast or convert these columns each time I want to read this table, right? and that tedious I guess :( – mpy Dec 18 '19 at 5:02
  • @mpy If you're gonna write too many queries like that, another possibility would be having a VIEW of that table with the CAST or CONVERT of those columns on the VIEW. That way you would write the convertion only once and call the VIEW on your queries. – Ronaldo Dec 18 '19 at 13:47
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.