Suppose you want to use a machine learning model and add the results to your SQL Server database—how do you do that? You can’t use T-SQL for this; instead, you’ll need to use another programming language, such as Python or R. This blog focuses on combining Python and SQL Server. There are several ways to achieve this: you can work from a Python IDE using the pyodbc package to connect to your SQL Server, or you can use SQL Server’s Machine Learning Services, which allow you to program in Python directly from SQL Server Management Studio (SSMS).
The advantage of SQL Server Machine Learning Services is that you don’t have to move any data, which is a major benefit, especially when dealing with sensitive data. It’s also easy to integrate your model’s results into your database. This blog post covers the technical implementation of SQL Server Machine Learning Services and the error messages I encountered and resolved during this process.
Install
First, you'll need to make sure that the Machine Learning Services and Languages Extensions option is installed with Python as the language.
Open the SQL Server Installation Center and click ‘New SQL Server stand-alone installation or add features to an existing installation.’ You will then be prompted to select the media folder as the installation folder. In my case, I was able to select this folder: C:\SQLServerFull Use. Next, uncheck the box labeled ‘include SQL Server updates.’ On the ‘Installation Type’ page, select ‘Add features to an existing instance of SQL Server 2019’ and select your installation of SQL Server 2019. You will then be taken to the ‘Feature Selection’ page, where you should select Python under “Machine Learning Services and Languages.” To verify that the installation was successful, check to see if the following folder is available: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES.
Make sure you can run external scripts in SQL Server
To run Python scripts in SQL Server, you must enable the ‘external scripts enabled’ option. This option is disabled by default. You can enable it by running the following query.
EXECUTE sp_configure ‘external scripts enabled’, 1 RECONFIGURE WITH OVERRIDE
Execute Your Python Query in SQL Server
You have now completed all the steps required to run a Python script in SQL Server. If it doesn't work right away and you receive an error message, see below for a list of common error messages and their solutions.
Here's how to write a Python script in SQL Server:
EXECUTE sp_execute_external_script
@language = ’Python'
,@script = N’SQL_out = SQL_in.dropna();’
,@input_data_1 = N’SELECT Column1, Column2 FROM Table;’
,@input_data_1_name = N'SQL_in’
,@output_data_1_name = N'SQL_out’
WITH RESULT SETS(Column1 NVARCHAR(255) NOT NULL, Column2 NVARCHAR(255) NOT NULL);
As you can see, a stored procedure is executed that runs the external script. In it, you first specify which language you’re using. You also provide the query with an input query. Note that you can only specify one input. If you want to combine multiple tables as input, you’ll first need to write an SQL query or create a view that you can use as input. Give your input and output datasets names that you can use in the Python code. Next, you can insert your Python script. Note that if you use quotation marks in your script, you must now double them to ‘escape’ them. For example, when selecting a column: df = df.drop(columns = [”Column”]). In your Python script, you no longer need to import data; you can use the name ‘SQL_in’ directly in this example. You can also write the output to a table instead of just displaying it. To do this, remove the ‘WITH RESULT SETS’ statement at the end of the query and add an ‘INSERT INTO’ statement at the beginning of the query.
Error messages in your script
There's a good chance that when you run a Python script for the first time, you'll get an error message. Figuring out where the message is coming from and what you need to change to fix it can take quite a bit of time.
ModuleNotFoundError: No module named ‘…’.
The Python installation in SQL Server 2019 comes with a number of standard packages. You may find that a particular package is missing, or that you need a newer version of a package. You can use the following script to check which packages and versions are installed:
EXECUTE sp_execute_external_script
@language = ’Python'
@script = N'import pkg_resources'
import pandas
OutputDataSet = pandas.DataFrame(sorted([(i.key, i.version) for i in pkg_resources.working_set]))’
WITH result sets((Package NVARCHAR(128), Version NVARCHAR(128)));
To install a new package, open the command prompt as an administrator. Change the directory to C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Scripts and then run the following code:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Scripts>pip.exe install “package name”
If you want to upgrade a package, run the following code: pip.exe install ‘package’ –upgrade. Do you want to upgrade to a specific version or install a specific version? Then add the version number to the command: pip.exe install ‘package’==’version number’ for installation, and pip.exe install ‘package’==’version number’ –upgrade for upgrading.
The EXECUTE statement failed because its WITH RESULT SET clause specified 1 result set, and the statement attempted to return more result sets than that.
If you add a ‘WITH RESULT SETS’ statement to your query, make sure to specify the same number of columns in this statement as your Python code generates. Are you getting this error message? If so, make sure you specify the correct columns in your ‘WITH RESULT SETS’ statement.
Invalid BXL stream error while running BxlServer: caught exception: Error communicating between BxlServer and client: 0x000000e9
Sometimes you get this error. It’s not immediately clear how to resolve it. If you get this error and see something like ‘Access denied’ at the top, there’s a good chance it’s the same error I encountered. I got this error because the model I wanted to apply to the dataset was printing text, and I didn’t have ‘write access.’ In this case, I was able to add ‘disp = False’ to my model fit to prevent the error. How to prevent a model from printing text will vary depending on the model.
ImportError: cannot import the name ‘Panel’ from ‘pandas’
If you want to upgrade the standard version of Pandas in SSMS, or if installing another package automatically triggers an upgrade of Pandas, you'll see this error message. Next, locate the following Python script: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\site-packages\revoscalepy\functions\RxSummary.py and open it as an administrator in, for example, Notepad++. This file imports ‘panel,’ but this function is no longer used at all in the script. Delete the section of code where ‘panel’ is imported, save the script, and the error message will be resolved.
After following these steps, you should be able to run Python scripts in SQL Server and add the output to your database so you can use it wherever you want.
