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

Calling Python from C#

"Calling Python script from C#" doesn't seems to be a challenge or tough task. Even there are many articles available on internet which will tell you how to do that, but still I had to struggle a lot when I was doing this based on my need hence thought, it is better to share it with you all.

I had a full fledged Python project dependent on packages like Numpy, Pandas etc and I had to consume it through C# (a web api service). Technically from the C# I had a web api REST service endpoint which was processing data through python script/project.

My struggle was to resolve the other dependent script available in project and packages installed through pip. It took good amount of time to figure out the efficient way to solve this problem. During this I also researched about IronPython which allows you to fill the gap between .net and python but it didn't helped me and instead it was more problematic while resolving package like numpy, panda etc. Also I was not convinced to use IronPython just to execute python script from C# (.net).

Enough of talk, let's come to the solution. Below are steps which I followed to solve the problem

1. Created Main script (Main.py) which is an entry point to python from C#. something like this (sample):

import mypackage
from __init__ import Logger

def Main(requestid, data):
    try:
       #Code...calling other scripts/logics etc
    except Exception as e:
  Logger.error(e)

if __name__ == '__main__':
 try:
  Logger.debug('calling Main with paramaeters requestid:%s and\n data:%s' % (sys.argv[1], sys.argv[2]))
  Main(sys.argv[1], sys.argv[2])
  Logger.debug('Main Ends')
 except Exception as e:
  Logger.error(e)

Above code was a sample code which will get executed from C# and then this script will take care of executing other script. in .Net word this acts as a Main method of console application.

2. Made sure all the dependent python packages are installed through pip for the python environment (which is being used)

3. C# code to execute the script. This uses System.Diagnostics.ProcessStartInfo class to execute the python.exe and run the script main.py with arguments.

using System.Diagnostics;

//**C# code **//
 private dynamic CallPython(string requestId, string data)
        {
            // full path of python interpreter 
            string python = @"C:\Python\ENV-Python3.7\Scripts\python.exe";

            // python app to call 
            string myPythonApp = @"Main.py";

            // Create new process start info 
            ProcessStartInfo myProcessStartInfo = new ProcessStartInfo(python);
            myProcessStartInfo.WorkingDirectory = @"C:\Project\git\pythonpricingprototype";

            // make sure we can read the output from stdout 
            myProcessStartInfo.UseShellExecute = false;
            myProcessStartInfo.RedirectStandardOutput = true;

            // start python app with 3 arguments  
            // 1st arguments is pointer to itself,  
            // 2nd and 3rd are actual arguments we want to send 
            myProcessStartInfo.Arguments = myPythonApp + " " + requestId + " " + data;
            string result;
            using (Process myProcess = Process.Start(myProcessStartInfo))
            {
                using (StreamReader reader = myProcess.StandardOutput)
                {
                    result = reader.ReadToEnd();
                    myProcess.WaitForExit();
                }
            }

            return result;
        }

Explanation: There are three important line of code which plays the responsibility of resolving and running all dependent python packages/scripts.

• Python Interpreter (exe) path. : You must point to the path of python.exe available for the python environment where you have all dependent package installed.
  In my case it was: C:\Python\ENV-Python3.7\Scripts\python.exe
• Main script (entry point) name.
  In my case: Main.py
• Python project working directory: This was the the most important thing to resolve the all your other scripts (in project). In short it is the project root folder path where the Main.py and other *.py files are available.
  In my case: C:\Project\git\pythonpricingprototype
• then executing the command which is equivalent to:
  >>>main.py 12345 somedata

Rest all codes in C# are simple and good enough for self explanatory but instead if needed feel free to reach out to me.

That's all. Thanks for reading. Feel free to provide your valuable feedback.