Stored procedure cannot be the input to the Python script in MS SQL Server

I am in the journey of using python in MS SQL Stored procedures.

I am big fan of stored procedures since my earlier days in Sybase in 1993 up till now. 

The same for Oracle packages and procedures. 

With stored procedures , we can encapsulate all the business and data logic.

Being the stored procedure is inside the database , the data and logic is secure , highly performant and so on. 

In essence , it is an API that can be called from any languages / tools. 

MS SQL server started supporting embedding python scripts in T-SQL. 

Unfortunately , the input for the python cannot be stored procedure. 

Hopefully , it is changed in the coming releases. 

Here we go 

a) We will create a table emp with 2 records in it. 

C:\>sqlcmd -W -S DBSER\SQL2019DEV -d rptsdb

1>

2> select * from emp;

3> go

id fname lname

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

1 Jane Doe

1 John Doe

(2 rows affected)

b) Create python external script with simple select as the input. 

1> EXECUTE sp_execute_external_script @language = N'Python',

2> @script = N'

3~ ## print(InputDataSet)

4~ OutputDataSet = InputDataSet

5~ ',

6> @input_data_1 = N'SELECT fname  , lname  FROM emp  ORDER BY 1;'

7> WITH RESULT SETS ((  fname nvarchar(50), lname nvarchar(50)     ));

8> go

fname lname

----- -----

Jane Doe

John Doe

(2 rows affected)

c) Create stored procedure to list the records from the table 'emp' 

1> create procedure p_emp_list

2> as

3> begin

4> SELECT fname , lname

5> FROM  emp

6> end;

7> go

1> exec p_emp_list;

2> go

fname lname

----- -----

Jane Doe

John Doe

(2 rows affected)

d) Let us use the above stored procedure as the input to Python script 

1> EXECUTE sp_execute_external_script @language = N'Python',

2> @script = N'

3~ ## print(InputDataSet)

4~ OutputDataSet = InputDataSet

5~ ',

6> @input_data_1 = N'exec p_emp_list;'

7> WITH RESULT SETS ((  fname nvarchar(50), lname nvarchar(50) ));

8> go

Msg 39001, Level 16, State 2, Server DBSER\SQL2019DEV, Line 1

Only SELECT statement is supported for input data query to 'sp_execute_external_script' stored procedure.

Msg 11536, Level 16, State 1, Server DBSER\SQL2019DEV, Line 1

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Accessing Python Libraries in SQL Server

I have been exploring to setting up a contextualized search on one of my datasets. For this experiment,  I was planning on using Machine Learning Services  ( MLS) in SQL Server 2019.
And , the library I was word2vec . This library is not part of the Standard libraries that came with SQL Server MLS .

Took sometime , to get this installed in my SQL Server environment. I was hoping , this blog post will be helpful , if anyone is trying to install python libraries in SQL Server .

First , we need to identify the system path , where Python is installed.


C:\>sqlcmd -W -S SERNJ\SQL2019DB
1>
2> EXEC sp_execute_external_script
3>   @language =N'Python',
4>   @script=N'import sys; print("\n".join(sys.path))'
5> go
STDOUT message(s) from external script:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\python37.zip
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\DLLs
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages\win32
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages\win32\lib
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages\Pythonwin
C:\ProgramData\SQL2019DEV\Temp-PY\Appcontainer1\3E21F1D4-0032-473A-A2EB-D6763111F7F9\rxLibs
C:\ProgramData\SQL2019DEV\Temp-PY\Appcontainer1\7AD5E2EF-A002-4B2E-8ED1-4BFC71DBDB57

Then navigate to the python services scripts  ( in my case , C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Scripts ) directory and install gensim using pip.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Scripts>pip install gensim

pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.

Collecting gensim

  Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

  Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

  Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

  Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

  Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

  Could not fetch URL https://pypi.org/simple/gensim/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/gensim/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping

  Could not find a version that satisfies the requirement gensim (from versions: )

No matching distribution found for gensim

pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.

Could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping

For the above command to work , we need to add these directories to the path. 

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Lib\site-packages\conda

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Library\bin

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Scripts

Now , try the pip again to install gensim package.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Library>pip install gensim

Collecting gensim

  Downloading https://files.pythonhosted.org/packages/0b/66/04faeedb98bfa5f241d0399d0102456886179cabac0355475f23a2978847/gensim-3.8.3-cp37-cp37m-win_amd64.whl (24.2MB)

    100% |████████████████████████████████| 24.2MB 1.3MB/s

Collecting Cython==0.29.14 (from gensim)

  Downloading https://files.pythonhosted.org/packages/1f/be/b14be5c3ad1ff73096b518be1538282f053ec34faaca60a8753d975d7e93/Cython-0.29.14-cp37-cp37m-win_amd64.whl (1.7MB)

    100% |████████████████████████████████| 1.7MB 6.5MB/s

Collecting smart-open>=1.8.1 (from gensim)

  Downloading https://files.pythonhosted.org/packages/74/77/744c79da6e66691e3500b6dffff29bdd787015eae817d594791edc7b719b/smart_open-2.0.0.tar.gz (103kB)

    100% |████████████████████████████████| 112kB 3.3MB/s

Requirement already satisfied: scipy>=0.18.1 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from gensim) (1.1.0)

Requirement already satisfied: six>=1.5.0 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from gensim) (1.12.0)

Requirement already satisfied: numpy>=1.11.3 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from gensim) (1.15.4)

Requirement already satisfied: requests in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from smart-open>=1.8.1->gensim) (2.21.0)

Collecting boto (from smart-open>=1.8.1->gensim)

  Downloading https://files.pythonhosted.org/packages/23/10/c0b78c27298029e4454a472a1919bde20cb182dab1662cec7f2ca1dcc523/boto-2.49.0-py2.py3-none-any.whl (1.4MB)

    100% |████████████████████████████████| 1.4MB 6.5MB/s

Collecting boto3 (from smart-open>=1.8.1->gensim)

  Downloading https://files.pythonhosted.org/packages/c8/1e/587abcd94e8f6dbd42df730f40eb5f7313b6fd7255f5ef5a0db53d116999/boto3-1.13.1-py2.py3-none-any.whl (128kB)

    100% |████████████████████████████████| 133kB 6.5MB/s

Requirement already satisfied: idna<2 .9="">=2.5 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (2.8)

Requirement already satisfied: urllib3<1 .25="">=1.21.1 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (1.24.1)

Requirement already satisfied: certifi>=2017.4.17 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (2019.3.9)

Requirement already satisfied: chardet<3 .1.0="">=3.0.2 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (3.0.4)

Collecting s3transfer<0 .4.0="">=0.3.0 (from boto3->smart-open>=1.8.1->gensim)

  Downloading https://files.pythonhosted.org/packages/69/79/e6afb3d8b0b4e96cefbdc690f741d7dd24547ff1f94240c997a26fa908d3/s3transfer-0.3.3-py2.py3-none-any.whl (69kB)

    100% |████████████████████████████████| 71kB 4.6MB/s

Collecting botocore<1 .17.0="">=1.16.1 (from boto3->smart-open>=1.8.1->gensim)

  Downloading https://files.pythonhosted.org/packages/46/b8/588f44ac91f280beabd0d5ce192a65f50e32e39ebb2a4193590ccb3afff2/botocore-1.16.1-py2.py3-none-any.whl (6.2MB)

    100% |████████████████████████████████| 6.2MB 6.7MB/s

Collecting jmespath<1 .0.0="">=0.7.1 (from boto3->smart-open>=1.8.1->gensim)

  Downloading https://files.pythonhosted.org/packages/a3/43/1e939e1fcd87b827fe192d0c9fc25b48c5b3368902bfb913de7754b0dc03/jmespath-0.9.5-py2.py3-none-any.whl

Requirement already satisfied: docutils<0 .16="">=0.10 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from botocore<1 .17.0="">=1.16.1->boto3->smart-open>=1.8.1->gensim) (0.14)

Requirement already satisfied: python-dateutil<3 .0.0="">=2.1 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from botocore<1 .17.0="">=1.16.1->boto3->smart-open>=1.8.1->gensim) (2.8.0)

Building wheels for collected packages: smart-open

  Running setup.py bdist_wheel for smart-open ... done

  Stored in directory: C:\Users\zahir\AppData\Local\pip\Cache\wheels\27\65\38\8d7f5fe8d7afb4e4566587b2d1933cec185fba19257836c943

Successfully built smart-open

Installing collected packages: Cython, boto, jmespath, botocore, s3transfer, boto3, smart-open, gensim

  Found existing installation: Cython 0.29.2

    Uninstalling Cython-0.29.2:

      Successfully uninstalled Cython-0.29.2

Successfully installed Cython-0.29.14 boto-2.49.0 boto3-1.13.1 botocore-1.16.1 gensim-3.8.3 jmespath-0.9.5 s3transfer-0.3.3 smart-open-2.0.0

Now , we should be able to use gensim in SQL Server . 

More to come on this. 

Stay tuned.