How to run python scripts from SQL Server.

To run python or R language scripts through SSMS we need to install Machine Learning Services during the SQL installation. We will discuss in a later section how to install Machine Learning Service. Machine Learning Service has been introduced from SQL Server 2016 version which helps in processing R language scripts. From SQL Server 2017 version it has the capability to execute the Python scripts as well.

A brief introduction to R, Python, and Machine Learning Services:

R and Python both are free and open-source programming languages used for data analytics. R mainly used for statistical analysis and Python is more of a general-purpose programming language. Both have large communities that keep adding new libraries or tools regularly. Python is one of the most in-demand programming languages nowadays and most of the data scientists use R language for their analysis. So, in short, both have a huge demand in the market now.

On the other hand, Machine Learning Service is a feature of SQL Server which helps in running R or Python scripts. This mainly helps in performing data analytics on the data residing in SQL Server. From SQL Server 2016 version Microsoft has introduced a concept called extensibility framework, this allows us to run R scripts and from SQL Server 2017 this framework has the capability to run Python scripts as well.

Installing Machine Learning Service:

It is easy to install Machine Learning Service along with SQL Server. While installing SQL Server, in the “Feature Selection” window we have to select below “Machine Learning Services (In-Database)” options while performing the installation. Here I want to run only Python scripts so I have checked only the “Python” box. After this proceed with normal installation clicking on “Next”.

In “Consent to install Python” window, click on “Accept” and click on “Next”. This will install the necessary pre-requisite.

That’s it after clicking on the “Install” Machine Learning service feature also will get installed along with SQL Server instance.

How to run Python scripts:

There are two ways to execute Python scripts using Machine Learning service.

1. We can run directly from SSMS using the “sp_execute_external_script” stored procedure.
2. We can use Python client for writing scripts and then push them to remote SQL Server.

Run directly from SSMS using the “sp_execute_external_script” stored procedure:

This is the most commonly used method because it is easy to use compared to another method. We will execute a simple “c=a+b” script.

Before executing Python scripts make sure below 2 options are enabled.

• Make sure the SQL Server LaunchPad service is running.
• ‘external scripts enabled’ option is enabled through ‘sp_configure’.

Now we will run a simple addition python script through SSMS. In the below script, we are passing values ‘1’ and ‘2’ to ‘a’ and ‘b’ variables and sending the result value to ‘c’ and finally printing the value of ‘c’.

EXECUTE sp_execute_external_script @language = N'Python'

    , @script = N'

a = 1

b = 2

c = a+b

print(c)'

If there are no issues we should see output like below: 

We should see the output as ‘3’, as a+b which is 1+2 gives ‘3’ in the output section.

We have successfully executed our first python script that too through SSMS.

Pre-requisite Testing:

For testing purpose, I have stopped SQL Server Launchpad service and ran the above script again

“SQL Server Launchpad” service manages and executes external scripts. If SQL Server was not able to communicate with the “LaunchPad” Service we will receive below error.

In the same way, if ‘external scripts enabled’ option is not enabled through ‘sp_configure’ before executing any external scripts, in our scenario our Python scripts will receive below error:

The other option to execute python scripts using python client which we will discuss in another article.

Let me know in the comments sections below if you have started using python through SSMS or python client already and for what sort of tasks you are using.

Thanks VV!!

#python #sqlserver #SQL #externalscripts