When import and export file using Python with SQL server 2019 I get error ?

Question

I work on sql server 2019 when import and export excel file using python with sql server 2019
i get error as below

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 "<string>", line 5, in <module>
  File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\ABADC510-215D-4742-8988-DD47C060354B\sqlindb_0.py", line 31, in transform
    for i in pip.get_installed_distributions():
AttributeError: module 'pip' has no attribute 'get_installed_distributions'

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 "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
    rx_native_call("SqlSatelliteCall", params)
  File "D:\SQL Data\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.

so how to solve this issue please ?

What I have tried:
EXECUTE sp_execute_external_script
@language =N'Python',
@script=N'import pip
for i in pip.get_installed_distributions():
print(i)';
GO

Answer 1

This is the stackoverflow answer that explains the alternative. I haven't tried myself
stackoverflow.com/questions/49923671/are-there-any-function-replacement-for-pip-get-installed-distributions-in-pip

Answer 2

Hi @ahmed salah ,

If you have installed Python services and from SSMS enable the external scripting feature

Provide folder permission to access the excel files during import / export process, right-click on folder -> Properties -> Security -> (Full Control) to "ALL_APPLICATION_PACKAGES".
https://www.allaboutmssql.com/2019/07/sql-server-importexport-to-excel-using.html

By default, external Python and R scripts only have read permission to their working directories, you need give either Read&execute and /or Write permissions to the NT Service\MSSQLLaunchpad service user account and ALL APPLICATION PACKAGES on this directort
https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-server-machine-learning-services-2019?view=sql-server-ver15#file-permissions

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.