Python in SQL Server

Python is an interpreted programming language. Python is a very strong programming language for multi purposes like machine learning/complex computing/ scientific and theoretical based solutions etc. I do not want to cover the details as We have a “Stairway to Python” initiative by Kiran Reddy. I strongly recommend to read the series to know and learn more about Python. For now, Let us cover Python in SQL Server…

Prerequisites

Python support in SQL Server, is a great move from Microsoft to challenge other technologies.SQL Server 2017 by default has in-built Python libraries to support the requirements.

Let us quickly see the prerequisites for Python in SQL Server.

1. Install SQL Server 2017 Developer or above edition

As SQL Server 2017 has introduced support of Python script/code execution, Lets us download and install SQL Server 2017 Developer version as a first step.

2. Select the Machine learning features that includes Python and R while installing SQL Server 2017

It is important for us to understand we need to select Machine Learning Services(previously known as R Services(SQL 2016) in “Feature Selection” while installing SQL Server 2017.

3. Make sure the Launchpad Service is selected with Automatic start-up

Launchpad is an integral part of Machine Learning Services execution meaning to executing the Python or R code in SQL Server. The below diagram explains the usage of Launchpad service.

Note, This is important when you have multiple Launchpad services exists in the same environment and one of them have any issues that could not start, none of the Launchpad services may not start at all(manually). To be safer side, we can make this setting Automatic at initial stage.

4. Enable “External Scripts Enabled” in the configuration

To run Python code in SQL Server, we need to use sp_execute_external_script system stored procedure. To use this procedure, we need to enable the configuration option “external scripts enabled” as below.

sp_configure 'external scripts enabled',1
reconfigure with override

5. Restart the SQL Service

This is important as “External Scripts Enabled” option is not a dynamic configuration that needs an explicit restart of the service to affect the changes.

A quick Demo

Now, we have set up the environment Lets quickly do a script execution.


EXEC sp_execute_external_script @language = N'Python', 
@script = N'print("Python in SQL Server is a revolution!!!")'

Thats it for now, hope you enjoyed the post.

Advertisements

One thought on “Python in SQL Server

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