SQL server python service-"DECLARE @myscript NVARCHAR(MAX) = N' " gives syntax error, what could be the possible mistake?

185 Views Asked by At

I am using SQL Server 2017 with in-database Machine Learning with python scripts with a goal to run Machine Learning models. While following a tutorial on PluralSight 'Getting started with Python on SQL Server' within a sql file, I Declare a variable with its data type and enter my Python script into it. But it throws a syntax error on the first line. My code is as below :

I am writing the statement as per the tutorial.

DECLARE @myscript NVARCHAR(MAX) = N 

error is as:

DECLARE @myscript NVARCHAR(MAX) = N'
                             ^
SyntaxError: invalid syntax 

Expectedly the code must declare a variable @myscript which I then use it during definition of input code.

Here is the full code below

DECLARE @myscript NVARCHAR(MAX) = N'
import sys
sys.path += ["D:\\sql_queries"]
import ml_models as ms
mydf = ms.fill_na(mydf)
mydf = ms.backup_columns(mydf)
mydf = ms.shaping_df(mydf)
mydf = ms.normalising_numerical_mydf(mydf)
mydf = ms.date_to_datetime(mydf)
mydf = ms.split_years_months(mydf)
mydf = ms.replacing_strings_with_integers(mydf)
mydf = ms.type_casting(mydf)
mydf = ms.label_encoder(mydf)
mydf = ms.drop_unnecessary_features(mydf)
x_train, x_valid, x_test, y_train, y_valid = ms.seperate_train_test_validation(mydf)
y_pred_rf = ms.random_forest_model(x_train, y_train, x_valid, y_valid, x_test)
y_pred_ada = ms.adboost_model(x_train, y_train, x_valid, y_valid, x_test)
y_pred_xgb = ms.xgb_model(x_train, y_train, x_valid, y_valid, x_test)
y_pred_lgb = ms.light_gradient_boost_model(x_train, y_train, x_valid, y_valid, x_test)

predictions = y_pred_rf*0.3 + y_pred_ada*0.2 + y_pred_xgb*0.2 + y_pred_xgb*0.3
';

DECLARE @myquery NVARCHAR(MAX) = N'
select * from banktest.dbo.train
UNION ALL
select *, 6 as loan_default from banktest.dbo.test;'
;

DECLARE @predictions float;

EXEC sp_execute_external_script
    @language = N'Python'
    , @script = @myscript
    , @input_data_1 = @myquery
    , @input_data_1_name = N'mydf'
    , @output_data_1_name = N'predictions'
    , @parallel = 1
    , @params = N'@predictions float out'
    , @predictions = @predictions OUT
WITH RESULT SETS ((predicted_value nvarchar(MAX)));
1

There are 1 best solutions below

0
Shreyas Moolya On

The error was due to directly running the code on python server using VS Code. For it to display the output we must execute the code by Ctrl+Shift+P