Infrastructure at your Service

Stéphane Haby

SQL Server 2017: first steps with Python Service

Last year, I written blogs about SQL Server 2016 and his integration from the R language.
Just for information, you can find these blogs about SQL Server 2016 R Services here:

Now, the next version of SQL Server, named officially SQL Server 2017 integrate also the language Python.
The first step is to install the Python Services during the installation of SQL Server.
Python01
In the Instance Features > Database Engine Services, you have a new structure named “Machine Learning Services (In-Database)” with R and Python.
I check the case “Python” and next button.
After the classical instance, server and database Engine Configurations, a window “Consent to install Python” need your acceptance to be validate and go to the next installation step.
Python02
After that, the installation begins and failed… :-(
Python03
The eternal question, WHY???…
Like a majority of peoples, I don’t read the text in the window “Consent to install Python”.
The text explains “By clicking Next, you will start the download and installation of these packages to your machine.”. This means that you need an access to Internet.
You can also go the installation log file to see the error message:
Python10
After activating the access to Internet to download all necessary packages to use Python, the installation was successful. :-D
Python04
I have the curiosity to have a look in the installation file and find the feature parameter for the script installation for Python. In addition of ADVANCEDANALYTICS, to install Python the parameter is SQL_INST_MPY.
Python05
Like for R, in the instance configuration, you need to set the ‘external script enabled’ to 1.
If you haven’t read my article on SQL Server 2016: R Services, I rewrite the method to search and configure the service:
Script to search the configuration:

SELECT * FROM sys.configurations WHERE name='external scripts enabled'

Python06
As you can see in the result, this configuration is not dynamic and need a restart of the engine.
After a restart, the configuration is enabled.
Python07
Time to test a query….
Like my article SQL Server 2016 – R Services Tips: How to find installed packages using T-SQL?, I will find all installed packages.
I have created the Query using packages pip and pandas:

  • pip is a package management system used to install and manage software packages
  • Pandas is a library for data manipulation and analysis

The query uses the stored procedure sp_execute_external_script and Python as parameter for the language:

exec sp_execute_external_script  @language =N'Python',
@script=N'import pip
import pandas
installed_packages = pip.get_installed_distributions()
installed_packages_list = sorted(["%s %s" % (i.key,i.version)
     for i in installed_packages])
OutputDataSet = pandas.DataFrame(installed_packages_list)'

Python08

 

You can see that you have 125 packages installed for Python with the CPT2 of SQL Server 2017.
Have fun with Python on SQL Server… 8-)

 

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager