How to run custom python module on SQL Server?

  • 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

    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)
  • Thanks for posting your issue and hopefully someone will answer soon.

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

  • 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 1 month ago by  Lowell.
    • This reply was modified 1 month 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!

  • 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.

  • 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.

     

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

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

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