How to run custom python module on SQL Server?

Question

Post reply

How to run custom python module on SQL Server?

yyang5823

Old Hand

Points: 333
More actions
June 15, 2024 at 1:39 am

#4428603
I am working on a small test script on SQL server 2022 using python module. The module does the simple calculation of pi * 2 (3.1415926 * 2). The test script does not work with MouleNotFoundError when using the custom module. While the test script worked fine when same python code was directly imbedded in SQL script. So far, I have not been able to determine the root cause of the error. I will appreciate any suggestions or troubleshooting tips
TEST ENVIRONMENT
- SQL Server 2022 (selected Machine Learning option)
- Python version 3.10
- The SQL installation followed following online instructions:
  https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16
PYTHON CUSTOME MODULE (compute_tau.py)
```
import pandas as pd

def compute_tau(mypi: pd.DataFrame, multiplier: int ):
    mytau = pd.DataFrame([multiplier * mypi["pi"][0]],columns=["tau"])
    return mytau, float(mytau["tau"][0])

def test_compute_tau():
    mypi=pd.DataFrame([3.1415926], columns=["pi"])
    df, tau=compute_tau(mypi,2)
    print('DataFrame:\n', df)
    print('tau = ', tau)

if __name__== '__main__':
    test_compute_tau()
```
SQL SCRIPT
(note: the compute_tau.py was copied to folder: c:\temp )
```
ERROR MESSAGE IN SQLDECLARE @myscript NVARCHAR(MAX) = N'
import sys
sys.path += ["c:\\temp"]
import compute_tau as ct
mytau, tau = ct.compute_tau(mypi, multiplier)
';

DECLARE @myquery NVARCHAR(MAX) = N'
SELECT cast(3.14159 as float) AS pi;
';

DECLARE @tau float;

EXEC sp_execute_external_script   
    @language = N'Python'   
    , @script = @myscript  
    , @input_data_1 = @myquery 
    , @input_data_1_name = N'mypi'    
    , @output_data_1_name = N'mytau'
    , @parallel = 1  
    , @params = N'@multiplier int, @tau float out'
    , @multiplier = 2
    , @tau = @tau OUT
WITH RESULT SETS ((tau float));

DECLARE @msg varchar(200) = 
   'tau = ' + ISNULL(CAST(@tau as varchar(30)), 'null');

PRINT @msg;
```
ERROR MESSAGE IN SQL
Msg 39004, Level 16, State 20, Line 2
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. External script request id is 2981F136-8A1B-4FAD-B7D0-D23CF14561E4.
Msg 39019, Level 16, State 2, Line 2
An external script error occurred:
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "C:\ProgramData\SQL_PYTHON\Temp-PY\Appcontainer1\2981F136-8A1B-4FAD-B7D0-D23CF14561E4\sqlindb_0.py", line 43, in transform
import compute_tau as ct
ModuleNotFoundError: No module named 'compute_tau'
Error in execution. Check the output for more information.
SqlSatelliteCall error: Error in execution. Check the output for more information
OTHER TEST
To make sure the custom module is working properly, I wrote a python program: custom_module.py to test it. This program worked fine without any error. I am wondering why the custom module works fine in python program, but not working in SQL
```
import pandas as pd
import compute_tau as ct

mypi=pd.DataFrame([3.1415926], columns=["pi"])
multiplier = 2
mytau, tau=ct.compute_tau(mypi,multiplier)
print('DataFrame:\n', mytau)
print('tau = ', tau)
```

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Lowell SSC Guru Points: 323518 More actions · Answer 2

my knee jerk reaction is two fold.

did you run pip import compute_tau on the server itself? all your libraries you have need to be accessible to SQL's version of python, and that might not be true.

second i'd also want to check that the account running the sql services does not have access to c:\temp.

i like to put PowerShell's or python script sin a sub folder where the data directory or the log directory exist, so that i am sure that permissions are not my problem.

can you either explicitly add the service account to that folder, or move the file to a path we know the account has access to?

(SELECT * FROM sys.dm_server_services) will get the accounts in question, ad don't make them local administrators or anything like that, it's unnecessary.

sys.path += ["c:\\temp"]

This reply was modified 3 months, 1 week ago by Lowell.
This reply was modified 3 months, 1 week ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yyang5823 Old Hand Points: 333 More actions · Answer 3

Since pip is designed to load libraries or packages from the repository shared by Python communities. The modules registered in Pypi repository need to have required format. Since compute_tau is just a small python module I created, I don't think it can be loaded using pip.

Followed you instructions on checking file access permissions, besides "full control", I granted all other permissions to the account that run "SQLServer" and "SQLLauncher" services. These changes did not make any difference. I still got same error as before.

Emperor100 SSCrazy Points: 2161 More actions · Answer 4

Ensure that your module is developed using the same version of Python that is installed on the server while initial installation of the SQL instance.

Instead of C:\Temp, create a folder inside the sql installation folder on the server, save the scripts there (OR) check on the server where other python files(Python_services\Scripts\) have been saved.

Also, you can find the directory path on the server by running the following code from SSMS after connecting to the instance.

EXEC sp_execute_external_script
  @language =N'Python',
  @script=N'import sys; print("\n".join(sys.path))'

Calling custom modules should work.

=======================================================================