As part of the effort Microsoft is making at incorporating analytics, Python is being added into SQL Server 2017.   This means SQL Server will support the two primary languages of Data Science within SQL Server, R and Python.  As I have previously reviewed using R in SQL Server, I wanted to also review using Python with SQL Server. Since Python is near the top of the most popular programming language charts, many people are interested in learning more about it.  As many data professionals are unfamiliar with Python, I wanted to introduce the topic not just here, but in my upcoming webinar for 24 Hours of PASS on Implementing Advanced Analytics with SQL Server 2017 and Python.

Installing Python in SQL Server

The process for using Python in SQL Server is very similar to the previous process of installing R.  Microsoft renamed R Services to Machine Learning Services, and now allows both R and Python to be installed, as shown in the screen.  Microsoft’s version of Python uses Anaconda, which is an open source analytics platform created by Continuum. This is where Python differs from other open source languages, as Continuum is providing the version of Python as it contains data science components which are not included in the standard distribution of Python. Continuum also sells an enterprise version of Anaconda, with of course more features than come with the free version. It is important to remember the python environment as you will need select the same distribution when running Python code outside of SQL Server.

Configuration Changes for Python

The last thing needed to run Python is to configure and restart the SQL Server Services. In a new query type the following command

sp_configure 'external scripts enabled', 1
GO
Reconfigure
GO

After restarting the SQL Server Service, SQL Server will now run Python code, or if you installed SQL Server with both R and Python as I did, both languages can be used.

Python Development Environments

SQL Server Management Studio is designed for writing TSQL code, not Python.  The process for implementing Python code in SQL Server would be first to create and test the code in Python, then once the code is working, deploy the code in SQL Server.  There are a number of different User Interfaces that you might want to consider when writing Python.  Python comes with IDLE, but as it rather a feature bereft application, chances are that if one is coding Python, they want to use some other user interface.  Some of the more common ones are JetBrain’s PyCharm , Atom Python Tools or the UI Windows developers use the most, Visual Studio with Python language support.  Selecting and setting up the environments is a surprisingly complex process.  Python is a very flexible language and is widely used beyond the realm of data science to do things like create web applications.  For this reason, the environments selected matter as they create different ecosystems.

Incorporating Python to solve Data Science Solutions

In my upcoming session for 24 hours of PASS, I will review the pros and cons of several development environments, and let you know which one I selected and the steps needed to make it work.  We will also take a look at implementing some Python code in SQL Server so that we can perform advanced analytical analysis with Python.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur