Import Dataset In Python From SQL Server – Get Started with SQL Server Machine Learning Services – Part 4

Introduction

In our previous article, we discuss about how to run Python in SQL Server that hard coded. In this article we will discuss about the data which is already stored in our SQL Server Databases.

Steps to Process Python with SQL Server Data

In order to work with Data using Python, we have to follow these steps

  • By Importing data using SELECT Statement (T-SQL Query)
  • Process those data using Python code or using Python libraries
  • Export the processed data in SQL Server Standard format
Importing Data using T-SQL

In the pervious article, we discussed about the parameters that are used in the System Stored Procedure sp_execute_external_script. We also discussed about the input optional parameter called @input_data_1. In this parameter we have to pass or write our standard T-SQL Query that selects data from our table(s).

The result of our T-SQL query is converted into Data frame and stored in variable name InputDataset.

Processing Data using Python code or Libraries

Now we have collected our required data. Now the next step is manipulate the data that is, the InputDataSet variable using Python code or Python libraries such as Pandas, the most widely used libraries. We no need to install this library, its already installed during our Python installation.

Exporting Our Data Frame

After manipulating the data, now we have to export our data back to SQL Server, which will represent the Data Frame in Standard SQL Server format, for that we can create another variable as OutputDataSet. Assign our manipulated data frame to this output variable.

When the script completes, the results will be displayed in Standard SQL Server Result Set by converting the data frame. That result can be used in report or just like any normal result set.

Sample Script

EXEC sp_execute_external_script @language = N'Python',   
@script = N'  
import pickle  
irismodel = pickle.loads(nb_model)  
species_pred = irismodel.predict(iris_data[[1,2,3,4]])  
iris_data["PredictedSpecies"] = species_pred  
OutputDataSet = iris_data.query( ''PredictedSpecies != SpeciesId'' )[[0, 5, 6]]  
print(OutputDataSet)  
'  
, @input_data_1 = N'select id, "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'  
, @input_data_1_name = N'iris_data'  
, @params = N'@nb_model varbinary(max)'  
, @nb_model = @nb_model  
WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));

Conclusion

In this simple article, we discussed how to process the dataset by importing from SQL Server Table. We will see more concepts on this in our upcoming articles. Please share your feedbacks in the comments section.

Other Articles in this Series

One thought on “Import Dataset In Python From SQL Server – Get Started with SQL Server Machine Learning Services – Part 4

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: