I am trying to get started running python inside of Sql Server 2017 with Python Machine Learning Services.
Out of the box it works and I can run simple scripts like listing the installed packages:
EXEC sp_execute_external_script @language = N'Python', @script = N'import pkg_resources print([p.project_name for p in pkg_resources.working_set])'
When I want to add a new package I follow the Microsoft documentation for 2017: https://docs.microsoft.com/en-us/sql/machine-learning/package-management/install-python-packages-standard-tools?view=sql-server-2017
which says to basically just pip install the new package. So I go to the sql server python install:
C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICES
scriptspip.exe install pyarrow
This installs an upgraded numpy version as a dependency and when I then try to call even simple python scripts like above I get the following error:
Msg 39012, Level 16, State 1, Line 0 Unable to communicate with the runtime for 'Python' script. Please check the requirements of 'Python' runtime.
Nothing will run while numpy is in this newer version. I can fix the issue by repairing the Sql Server install from the installation media, but that gets me back to my initial setup without the required pyarrow package.
After trying to troubleshoot (incl. installing specific versions of pandas and numpy) I now can’t get back to a working state with repairs – will try uninstall and reinstalling the feature.
I can see that the procedure has changed for installing python packages in Sql Server 2019 (https://docs.microsoft.com/en-us/sql/machine-learning/package-management/install-additional-python-packages-on-sql-server?view=sql-server-ver15) so there might be some flaws in 2017 but I can’t find it documented anywhere.
I run the latest Sql Server 2017 (cu21) developer edition.
- I’m not too familiar but it says in multiple places that Sql Server 2017 Machine Learning services comes with Conda and I’m not sure if Conda and pip are compatible?
- The error suggests a dependency from microsofts revoscalepy on pandas and again from pandas on numpy.
Current full error:
Msg 39012, Level 16, State 1, Line 0 Unable to communicate with the runtime for 'Python' script. Please check the requirements of 'Python' runtime. STDERR message(s) from external script: Traceback (most recent call last): File "<string>", line 1, in <module> File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagesrevoscalepy__init__.py", line 99, in <module> from .RxSerializable import RxMissingValues File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagesrevoscalepyRxSerializable.py", line 10, in <module> from pandas import DataFrame File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandas__init__.py", line 39, in <module> from pandas.core.api import * File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandascoreapi.py", line 10, in <module> from pandas.core.groupby import Grouper STDERR message(s) from external script: File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandascoregroupby__init__.py", line 1, in <module> from pandas.core.groupby.groupby import GroupBy # noqa: F401 File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandascoregroupbygroupby.py", line 25, in <module> from pandas.util._validators import validate_kwargs File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandasutil_validators.py", line 7, in <module> from pandas.core.dtypes.common import is_bool File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandascoredtypescommon.py", line 10, in <module> from pandas.core.dtypes.dtypes import ( STDERR message(s) from external script: File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandascoredtypesdtypes.py", line 16, in <module> from .inference import is_list_like File "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICESlibsite-packagespandascoredtypesinference.py", line 9, in <module> from pandas.compat import ( ImportError: cannot import name 'Set' Completion time: 2020-08-25T09:43:23.9780283+02:00
I had the same type of issue with the pandas module on SQL Server 2017 with the same ImportError message that you received. The issue surfaced after installing CU22 for SQL Server 2017 recently and was not present when I set up the environment in question about two months earlier.
In my setup of the environment I performed an upgrade of the numpy and pandas modules to the latest available versions for Python 3.5.2 (which is the default Python version included with SQL Server 2017):
- Pre-installed version: 1.12.1
- Updated version: 1.18.5
- Pre-installed version: 0.19.2
- Updated version: 0.24.2
The reason for the upgrades were requirements from other modules (on numpy) and compatiblity issues between pandas and the new numpy version.
After receiving the ImportError error message when doing import pandas, I pinpointed it to line 9-10 in the file:
C:Program FilesMicrosoft SQL ServerMSSQL14.<instance_name>PYTHON_SERVICESLibsite-packagespandascoredtypes
from pandas.compat import ( PY2, Set, re_type, string_and_binary_types, string_types, text_type)
As I could not find any reason nor any reference from Microsoft on why this should stop working, I did some research and found this post from the pandas Github project:
It seems that there can be issues with old, installed versions of the numpy and pandas modules, even though they are supposed to be removed by pip.exe during the module upgrade/installation process.
Fair enough, I ran repeated uninstalls of the pandas and numpy modules until pip.exe no longer reported any installed versions and the related directories were gone from Libsite-packages
Set-Location 'C:Program FilesMicrosoft SQL ServerMSSQL14.instance_namePYTHON_SERVICESScripts' & '.pip.exe' uninstall pandas # repeated 2 times & '.pip.exe' uninstall numpy # repeated 2 times
In both cases, both the updated and some remaining parts of the original versions of the modules were removed.
I then reinstalled both modules and had no issues after that:
& '.pip.exe' install --upgrade numpy & '.pip.exe' install --upgrade pandas