Bdtjtk

I'm trying to built a Python model within MS SQL SERVER 2017. I've attempted to use some tutorials but the result was far from expected. I'm wondering what's wrong with this script (loading SQL Table into pandas dataframe):

USE PREPRESS_TMP;

GO



EXEC sp_execute_external_script

    @language=N'Python',

    @script=N'

dataset = pandas.DataFrame(input_data)  

    ',

@input_data_1 = N'Select * FROM dbo.Rests_GO'





GO

The error message is:

STDOUT message(s) from external script: 



Express Edition will continue to be enforced.

SqlSatelliteCall function failed. Please see the console output for more information.

Traceback (most recent call last):

  File "C:Program FilesMicrosoft SQL 

ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagesrevoscalepycomputecontextRxInSqlServer.py", line 406, in 

rx_sql_satellite_call

    rx_native_call("SqlSatelliteCall", params)

  File "C:Program FilesMicrosoft SQL 

ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagesrevoscalepyRxSerializable.py", line 291, in rx_native_call

ret = px_call(functionname, params)

RuntimeError: revoscalepy function failed.

I'll be glad to accept any help.

You're using Express Edition right? See this: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017.
There's nothing wrong with your code though you should include the input data to be clear as below

use AdventureWorksDW2014
go

EXEC sp_execute_external_script

@language =N'Python',
@script= N'
import pandas as pd
from pandas import DataFrame
OutputDataSet = pd.DataFrame(InputDataSet.describe())
',
@input_data_1 = N'SELECT
CAST(TotalProductCost AS float)
, CAST(UnitPrice AS Float)
, CAST(OrderQuantity AS FLOAT)
FROM FactInternetSales'
with result sets ((
TotalProductCost float
, UnitPrice Float
, OrderQuantity FLOAT
))

Although I can't answer your errors, you do need another parameter "input_data_x_name" for the code to work:

EXEC sp_execute_external_script

@language=N'Python',

@script=N'dataset = pandas.DataFrame(input_data)',

@input_data_1 = N'Select * FROM dbo.Rests_GO',

@input_data_1_name = N'input_data' 

Although Express Edition is one of the SQL Server editions, this message is subtly different and it's telling you that Python client libraries are licensed under the limitations of SQL Server's Express Edition. You can safely ignore it unless you're concerned about scalability further down the line. You'll actually see the same error message about limits (in-memory data sets and a maximum of 2-core processing) on SQL Server Standard Edition - I've seen it numerous times, and have just double checked it for accuracy.
In summary the first message is an informational message, not an error message, so you can safely ignore that part of the output.

The second part of the message is what you want to focus on (almost invariably when debugging this type of TSQL/Python code).

Therefore, user3912517's solution given above is the correct one, though it might be useful to understand why.

Simple explanation:
Python needs to reference the data set passed into it from SQL Server with a name.

It does this via the @input_data_1_name parameter, which effectively "names" the data returned by the query defined in @input_data_1.

This provides the link between the data set returned by the query that's run in the SQL Server environment and the dataframe that the Python code needs to reference the same data set in the Python environment.