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

Question

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)));

I have pasted only the code which contains the error. The text of @myscript is lengthy. Also the interpreter tells that there is some error in '(MAX)' part of the code — Shreyas Moolya, May 24 '19 at 06:32
That syntax error message looks like a *Python* error, not a *SQL Server* error. Are you maybe pasting this code into a Python interpreter? It isn't Python, it's SQL. — BoarGules, May 24 '19 at 17:11
Yes thanks. i was trying to debug the code through SQL Server management studio. I have connected the jupyter notebook to the SQL server to debug only to find my mistakes in my code flow. Also I am now using revoscalepy library to connect to perform in-database computations — Shreyas Moolya, May 26 '19 at 07:41
Directly debugging the code through SQL Server Management Studio does not seem to be a good idea. — Shreyas Moolya, May 26 '19 at 07:43
I figured it out. I was directly trying to run the code through VS Code which gave me this error. When I tried to execute(instead of run) the script the output is displayed — Shreyas Moolya, May 30 '19 at 09:37
Yeah, I thought you were getting a Python syntax error in response to correct SQL. But it was hard to be sure from the question. — BoarGules, May 30 '19 at 09:55

score 0 · Answer 1 · answered May 30 '19 at 09:41

0

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

answered May 30 '19 at 09:41

Shreyas Moolya

339
4
19

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

1 Answers1