SQL Server 2017 will allow the use of Python scripts called external scripts. SQL Server has some Python packages automatically. Today, I had to talk about adding Python packages to SQL Server 2017.
To with Python in SQL Server 2017, we should instead enable the consumption of external scripts.
Enable SQL Server for Python Scripts
You manage Python “inside” of SQL Server making use of the sp_execute_external_script system stored procedure. To work this procedure, it’s best to enable your instance to hold remote script execution. That’s a very easy configuration change:
EXEC sp_configure ‘external scripts enabled’, 1
RECONFIGURE WITH OVERRIDE
Here’s a diagram that better assists to explain what the results are when you know this as external procedure.
You’ll note utilization of Launchpad.exe. If that services are not running, you will come across an error:
Msg 39011, Level 16, State 1, Line 4
SQL Server was canrrrt communicate with the LaunchPad service.
Please verify the configuration about the service.
If you go with SQL Server for R and Python, muscle mass to set the Launchpad plan to automatic startup.
Now, people are ready to do a script. Consider a look at the Python packages installed automatically.
Find All Python Packages The center of SQL Server 2017
This is easy that has a script just like it:
EXEC sp_execute_external_script
@language = N’Python’,
@script = N’
import pip
import pandas as pd
installed_packages = pip.get_installed_distributions()
installed_packages_list = sorted([“%s==%s” % (i.key, i.version)
for i in installed_packages])
df = pd.DataFrame(installed_packages_list)
OutputDataSet = df
‘
WITH RESULT SETS ( ( PackageVersion nvarchar ( 150 ) ) )
This returns 128 rows.
Find Specific Python Package Can be found in SQL Server 2017
There’s a option to search for one package, too. We only filter for almost any specific package name, enjoy this:
EXECUTE sp_execute_external_script
@language = N’Python’,
@script = N’
import pip
import pkg_resources
pckg_name = “revoscalepy”
pckgs = pandas.DataFrame([(i.key) for i in pip.get_installed_distributions()], columns = [“key”])
installed_pckg = pckgs.query(‘ ‘key == @pckg_name’ ‘)
print(“Package”, pckg_name, “is”, “not” if installed_pckg.empty else “”, “installed”)’
One word of warning here: Python is particular about indents. If you are planning to be using Python scripts with SQL Server, continually try to to use a true Python editor, like VS Code, without having it rely on using SSMS. When a indents are incorrect, you will uncover an error message like this:
Msg 39004, Level 16, State 20, Line 24
A ‘Python’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 24
Another script error occurred:
Error in execution. Check the output to read more.
Traceback (most recent call last):
File “”, line 3, in
import pip
^
IndentationError: unexpected indent
SqlSatelliteCall error: Error in execution. Check the output to read more.
The error is clear: “unexpected indent.” This won’t message also suggests the exact spot. Nonetheless could have many such errors from your script. Consumers using VS Code are going to be handy, and you will then cut and paste the script on the procedure.
OK, position if our package won’t be found? No hassle; we can install new ones using pip.
Adding Python Packages to SQL Server 2017
To supply a Python package for your instance of SQL Server you should use either a command line or Visual Studio employing the Python Environments window. I will bookmark use the command line shall we say.
The first thing we have to know is a location of packages used in Python for SQL Server. If SQL Server was installed with default settings, the directory is important will be something like this:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES
Considering manually find this directory, here’s the Python command an extra chance the information:
EXEC sp_execute_external_script
@language =N’Python’,
@script=N’import sys; print(”
“.join(sys.path))’
Cannot we know in which directory Python.exe resides, we open an order line there making pip. I have two warnings in your case. First, you must be running the command line to be an account with plenty permissions to make to this directory. Second, you have to be using the pip.exe situated in the /Scripts directory. In case you just call pip, you will employ whatever pip is scheduled in your PATH from previous Python installations.
So, to run the Keras package, I demand /Scripts directory and execute this command:
>pip.exe install keras
When i can run the script above to make sure that that Keras has become installed.
Summary
Using Python from SQL Server is not difficult. It’s as basic as configuring your instance permitting external scripts and calling an external script getting a stored procedure.
The caveat We’ve with using Python and SQL Server is actually gives you YAPI (An additional Python Install). It’s a challenge keeping track of your Python environment.
But when you have a could do with Python and come in contact with relational storage, then SQL Server has grown into an option. I saw scenarios in which you might take reap the benefits of SQL Server in a container, storing temporary results, letting Python concentrate on the data science stuff. Regarding who lives on the inside Visual Studio, this could be an ideal scenario to combine Python and SQL Server storage.