Executing Python Script from SSMS

Question

Hi All,

I am trying to read a csv file using python in SSMS. On executing the code i am getting the following error "File not found". However, i can read the file using python command prompt editor

execute sp_execute_external_script   
@language=N'Python',  
@script=N'  
import pandas as pd  
pd.read_csv("C:\edb\csv_files\TR_data.csv")  
'  

Error Message

Msg 39004, Level 16, State 20, Line 41
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 41
An external script error occurred:

Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\7F5EC096-E9B4-4316-A5E3-16F3161DF178\sqlindb_0.py", line 33, in transform
pd.read_csv("C:\edb\csv_files\TR_data.csv")
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 678, in parser_f
return _read(filepath_or_buffer, kwds)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 440, in _read
parser = TextFileReader(filepath_or_buffer, **kwds)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 787, in init
self._make_engine(self.engine)

Msg 39019, Level 16, State 2, Line 41
An external script error occurred:
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1014, in _make_engine
self._engine = CParserWrapper(self.f, **self.options)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1708, in init
self._reader = parsers.TextReader(src, **kwds)
File "pandas_libs\parsers.pyx", line 384, in pandas._libs.parsers.TextReader.cinit
File "pandas_libs\parsers.pyx", line 695, in pandas._libs.parsers.TextReader._setup_parser_source
FileNotFoundError: File b'C:\edb\csv_files\TR_data.csv' does not exist

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.

Completion time: 2020-09-28T19:55:49.2310059-07:00

Answer 1

From the docs:

File permissions

By default, external Python and R scripts only have read access permission to their working directories.

If your Python or R scripts need access to any other directory, you need give either Read & execute and/or Write permissions to the NT Service\MSSQLLaunchpad service user account and ALL APPLICATION PACKAGES on this directory.

Follow the steps below to grant access.

In File Explorer, right click on the folder you want to use as working directory, and select Properties.
Select Security and click Edit... to change permissions.
Click Add...
Make sure the From this location is the local computer name.
Enter ALL APPLICATION PACKAGES in Enter the object names to select and click Check Names. Click OK.
Select Read & execute under the Allow column.
Select Write under the Allow column, if you want to grant write permissions.
Click OK and OK.

SQL Server 2019 on Windows: Isolation changes for Machine Learning Services - File Permissions

The reason granting folder permissions to the accounts is because it's using the Windows Mandatory Integrity Control which allows you to specify that an application runs without access to the permissions of the account it's running under.

Mandatory Integrity Control (MIC) provides a mechanism for controlling access to securable objects. This mechanism is in addition to discretionary access control and evaluates access before access checks against an object's discretionary access control list (DACL) are evaluated.

This feature was initially introduced to allow web browsers to run with less than the desktop user's security privileges.

Answer 2

Hello,

Having the same issue with this very simple script.

Am working on the SQL Server (2019) server itself, I am an administrator, external scripts is enabled (so yes, it executes other python code but no csv import). Trying to read a csv file from a folder where EVERYONE is allowed read & execute.
What am I missing?

Exact similar script as Erland above.
Code:

EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pandas as pd
df = pd.read_csv(r"C:\Temp\myfile.csv")
print (df)
'

Error:
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1014, in _make_engine
self._engine = CParserWrapper(self.f, **self.options)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1708, in init
self._reader = parsers.TextReader(src, **kwds)
File "pandas_libs\parsers.pyx", line 384, in pandas._libs.parsers.TextReader.cinit
File "pandas_libs\parsers.pyx", line 695, in pandas._libs.parsers.TextReader._setup_parser_source
FileNotFoundError: File b'C:\Temp\myfile.csv' does not exist

SqlSatelliteCall error: Error in execution. Check the output for more information.

Answer 3

Good call by Melissa on the r!

Unfortunately, that is not the only answer. I tried this on my system:

execute sp_execute_external_script 
@language=N'Python',
@script=N'
import pandas as pd
pd.read_csv(r"C:\temp\slask.csv")
'

And on my SQL 2017 instance it fails with an error that it can't parse the file (that is, it finds the file), but on SQL 2019, I get the same error as you. I seem to recall that I have read or been told that the Launchpad service has been locked down in what it can access. (I'm running the Launchpad service on SQL 2019 as my own user, and yet it can't find the file!).

Unfortunately, I don't have the time right now to research where you need to put the file, but I guess there should be samples or docs out there that can give you a hint. Else I will have to see if I can check this tomorrow.

Answer 4

Hi @Mohammad Sufian ,

Python is interpreting \t as a tab in the string 'csv_files\TR_data.csv'.

You can change this, as you've found out, by using r"..." to indicate it as a raw-string, which means python ignores backslashes.

Please have a try with below:

  pd.read_csv(r"C:\edb\csv_files\TR_data.csv")  

Or you could try to change the name of csv file to another one which is not head with T like 'UTR_data.csv'.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

All permission were set correctly

Output of the script

STDOUT message(s) from external script:
3. 0 27

Completion time: 2020-09-29T20:58:23.6357962-07:00