Calling Python from SQL Server

Hi Friends,

This post is about executing python script from sql server using the python environment you have and the solution is 'xp_cmdshell'.

It is very simple and easy actually just one line and this is:

EXEC master..xp_cmdshell N'C:\Python\ENV-Python3.7\Scripts\python.exe C:\P21\git\pythonpricingprototype\helloworld.py param[0] param[1]'

In the above code,
 the first path 'C:\Python\ENV-Python3.7\Scripts\python.exe' is for python.exe from the python environment you have/created.
 and the second path 'C:\P21\git\pythonpricingprototype\helloworld.py' is for the script which you need to execute.
then comes the parameters which you need to pass to python script.

Here is the python script helloworld.py for me.

import sys
import numpy
from __init__ import Logger

def main(name):
    print(name)

if __name__ == '__main__':
    Logger.debug('In Main')
    main(sys.argv[1])
    Logger.debug(sys.argv[1])

beauty of this is, it will resolve all your dependent scripts and packages automatically. As example I have __init__.py for logging related setting and initialization within the same folder ' C:\P21\git\pythonpricingprototype' and one package 'numpy' installed through pip for my python environment 'ENV-Python3.7.

Simple isn't it, but you need to make sure your python code shouldn't be a demon to kill the sql server process while running this through sql server.

** I still need to explore little more on this but if you have something which people should know please share it.

Thanks