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:
- SQL Server 2016: R Services
- SQL Server 2016 – R Services Tips: How to find installed packages using T-SQL?
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.
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.
After that, the installation begins and failed… 🙁
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:
After activating the access to Internet to download all necessary packages to use Python, the installation was successful. 😀
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.
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'
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.
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)'
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… 😎