Why Python and how to use it in SQL Server 2017

Microsoft has integrated Python in SQL Server 2017 which can be used for in-database analysis purpose. In this post, we are going to explore “Why Python and how to use it in SQL Server 2017”, and then we will explore that how we can use it in SQL Server 2017.

Why Python

Python is a general purpose object oriented programming language which can be used to develop applications for a variety of domains. We can use Python for almost everything from desktop and website development, gaming, robotics, scientific and numeric computing to spacecraft control and much more. Python is a high-level programming language which is an interpreted language (execute line by line) instead of compiled language. The Python has gained popularity because of its user friendliness. The developers fall in love with Python because it is easy to learn, but still very powerful. The technology giants like Google, YouTube, Dropbox, Maya, Netflix, NASA, iRobot and many more use Python extensively.

Because of its flexibility and large pre-built libraries, Python has become the first language of choice for the data scientists. It helps data scientists in their day to day tasks. Python provides very powerful ways to analyze texts. Python gets easily integrated with Big Data ecosystem also. The machine learning libraries like scikit–learn, Tensorflow, and Theano are very useful in implementing machine learning models in Python.

Now, Python is emerging as a game-changer because of its flexibility and rapid application development. Let’s have a quick look that why Python is gaining popularity day by day:

Easy to learn

As a new programming language, Python is easy to learn and apply from day 1. It uses plain English like syntax and keywords to make it more easy for developers.

Elegant syntax

The code written in Python is extremely clean and readable. It does not use curly braces and parentheses to define the scope of objects, instead, it uses whitespace indentations which increase readability. A simple code in Python looks like below:

import sys #not being used in our code
x = 1
if x == 1:
	print("The value of x is 1")
else:
	print("The value of x is not 1") 

In above code, we can see that we don’t have any extra characters like curly braces {} and parentheses () at all.

Highly productive

Python improves productivity of a developer to a great extent in comparison with other statically typed languages like C, C++, and Java. The size of the code gets reduced drastically in Python code from one-third to one-fifth of the C++ or Java code. The Python’s elegant code improves code maintenance also.

Rich support libraries and communities

The Python has a large collection of pre-built standard libraries which speeds up the development process greatly. We can get pre-built libraries for many tasks from text analysis and web development to scientific computing and machine learning. All these libraries are available with one line of Python code for us.

Python has a strong and highly supportive community which is increasing day by day. A larger and supportive community is extremely useful for new developers.

Highly portable and scalable

Programs written in Python gets executed on all platforms with no or minimal changes in the source code. This increases the program portability and scalability.

Integration with other languages

Python code can also be integrated C, C++, .Net and Java very easily. Python code can be called from C and C++ code also. It makes Python an extensible language.

Interactive mode (REPL)

The REPL (read-eval-print loop) provided by Python works like a command line interpreter. It provides a way to write and execute code immediately which is very useful in proto typing and immediate code testing purposes.

All these above characteristics make Python a preferable language over others. Now, let’s explore that how we can use it in SQL Server 2017.

Using Python in SQL Server 2017

In SQL Server 2016, Microsoft added R programming for in-database data analysis. Now, in SQL Server 2017, Microsoft has added Python as an in-database component, which can be used for deep data analysis and in the implementation of machine learning models in the database itself.

To use Python in SQL Server, we need to install SQL Server 2017 (preview version of SQL Server 2017 was available to download when this post was being written) as previous versions of SQL Server did not support Python within the database itself. Also, during SQL Server installation, we must select Python from “Machine Learning Services (In-Database)” option available in instance feature.

To execute a Python script from SQL Server, we use system stored procedure “sp_execute_external_script” provided by SQL Server which can be used to execute Python scripts. However, if external script execution feature is not enabled on the server, it will raise an error. Before executing this procedure we need to make sure that this feature is enabled on the server. To check and enable this feature on the server as below:

Enable external script execution feature on the server

We can check whether external script execution feature is enabled or not on the server using below command:

EXEC sp_configure 'external scripts enabled'
GO

The run_value must be 1 if it is enabled on the server. If it is not, we can use below command to enable it on the server:

sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

After executing this command, restart the SQL Server service in order to use this feature. Once this feature gets enabled, we can use this procedure to execute external scripts on the server.

Execute Python code in SQL Server 2017

To execute a Python script, we can use procedure “sp_execute_external_script” and supply its parameter values. Just to keep it simple, in below code, we are reading data from dbo.test table and using Python, we are looping through the rows of this table and printing the values of each row.

 
EXEC sp_execute_external_script  
@language =N'Python',
@script=
N'
for i in InputDataSet.id:
	print(i)
',
@input_data_1 = N'SELECT id FROM dbo.test'

Output:

In case, we are facing any issue related to the permission for external script execution, we can grant permission to the user to execute external scripts. Below scripts can help to grant the appropriate permission required in order to execute external scripts.

Permission to execute external scripts to a user

Below command can be used to grant permission to a user to execute an external script:

USE MyTestDB
GO
GRANT EXECUTE ANY EXTERNAL SCRIPT TO UserName

If you want to know more about the new features available in SQL Server 2017, you can read it here. To know more about Python and its history, you can visit the Wikipedia link. To download SQL Server 2017, you can click here.

Thanks for the reading. Please share your inputs in the comment section of this post.