search

datacleaner / DataCleaner

The premier open source Data Quality solution
GNU Lesser General Public License v3.0
597 stars 180 forks source link

Importing Python scripts in SQL Server stored procedure #1876

Open cloudytechi opened 3 years ago

cloudytechi commented 3 years ago

I have a bunch of local Python scripts that I need to execute from a local MS SQL Server. There is no problem if I try to import standard Python libraries (for example, pandas). I get an error, when I try to import a Python file, called Simulator.py located in the C:/Users/amusaeva/PyCharmProjects/ARW/WorkforceModel folder.

EXEC sp_execute_external_script @language =N'Python', @script=N' import sys sys.path.insert(0, "C:/Users/amusaeva/PyCharmProjects/ARW/WorkforceModel") import Simulator '

Here is the error message I get when running this SQL script:

Msg 39004, Level 16, State 20, Line 0 A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

Msg 39019, Level 16, State 2, Line 0 An external script error occurred:

Error in execution. Check the output for more information. Traceback (most recent call last): File "", line 5, in File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\D4294516-2993-475D-9F61-DF7C5AF4FE69\sqlindb_0.py", line 35, in transform

import Simulator ModuleNotFoundError: No module named 'Simulator'

SqlSatelliteCall error: Error in execution. Check the output for more information. STDOUT message(s) from external script: SqlSatelliteCall function failed. Please see the console output for more information.

Traceback (most recent call last): File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall", params)

File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call ret = px_call(functionname, params)

RuntimeError: revoscalepy function failed.

I know there is definitely a Python script called Simulator.py in that WorkforceModel directory and I don't think I have any syntax mistakes because when I run the exact three lines in Python through a PS window, the module gets imported with no problem. Why does my stored procedure not see the Python script?

Pursuing Data Scientist Certification.

kaspersorensen commented 3 years ago

I don't see how this issue is related to DataCleaner. I think maybe you're wanting to post about the python library of the name "datacleaner" - https://pypi.org/project/datacleaner/. But despite the unfortunate name clash, these are not the same projects!