Run Python Scripts in SQL Server Agent


By:   |   Updated: 2019-01-30   |   Comments   |   Related: More > SQL Server Agent

Problem

You have some business logic or process to run that's written in Python, but you would like a way to schedule this using SQL Server Agent to integrate with other steps of a job.  SQL Server 2016 + supports Python via Machine Learning Services, but perhaps you are running a lower version or need to use custom Python libraries unavailable in the standard Anaconda distribution.  How do you execute Python code on-demand using SQL Server?

Solution

In this tip we demonstrate how to use SQL Server Agent to call Python functions or execute arbitrary Python code from within the context of a SQL Server Agent Job.  Python is a fast-growing, well-understood and mature language used in many contexts, including as part of the stack for many Linux-based web services, in Windows for file and process management, for implementing machine learning techniques and much more.  At the time of writing Python comprises over 164,000 packages which are maintained mostly as open-source by hundreds of thousands of developers.  In contrast, PowerShell exposes the .NET framework which is powerful but limited, and cannot match in many ways the flexibility of Python as a fully-featured language.  We will see this contrast in action by scraping a website for the status of a service, something not inherently supported in PowerShell.

SQL Server does not, at the present time, support the execution of Python scripts from SQL Agent directly.  When setting up a job you will notice in the dropdown for the Job Step that only a limited number of options are supported:

Shows the list of options in the drop-down menu.

Thus, to run Python in this way, we need to be inventive.  We can either use the command-line option (Operating System - CmdExec) or we can use Powershell to call Python scripts, which has a few more advantages in terms of setting up the environment (e.g. the PATH variable) and encapsulating the Python code so that the results can be piped to some output or used in a secondary job step.  Both of these options are demonstrated below.

Example

We will now set up an example of how to use SQL Agent in this way.  We will write a short Python function to scrape the status page of Github (https://www.githubstatus.com) to determine whether or not Github is fully operational or is experiencing any issues.  We will use the output of this function (exit code) to determine whether to raise a SQL Server alert to the development and DBA teams, since if Github is having difficulties, the CI/CD deployment process (code > source control > deployment) for the business may be affected which could cause deployment failures and consequences for the deployment pipeline.

To do this, we will use the Python standard library and a third-party Python utility called BeautifulSoup (a HTML parser), the requests package (a form of wget or curl for Python with extra functionality) and the sys package.  We will run this on Python 3.x, so we need to make sure Python 3.x is installed on the same server as SQL Server.  From a security perspective Python is simply a language, not a service, so this should not present significant security concerns (the Anaconda Python distribution is included as part of later SQL Server versions in any case).  To install Python, visit the link given in Next Steps.

Once Python is installed, we need the packages - first, pip, which is a Python installer and then the BeautifulSoup and request packages.  We can install pip from the get-pip.py script available at the link in Next Steps.  We then install the packages at the command line on the server (note that sys is already built-in), like this:

py -m pip install beautifulsoup4
py -m pip install requests

Now, create and save the following file, called CheckGithub.py, in a suitable directory.  For demo purposes mine is C:\TEMP\.

from bs4 import *                                               # import the BeautifulSoup package 
import requests # import the requests package
import sys # import the sys package (built-in)
try: # begin try block
page = requests.get("https://www.githubstatus.com") # fetch the Github status page
pageText = page.text # extract the text from the page
pageRendered = BeautifulSoup(pageText, 'html.parser') # render as a nested HTML structure
statusDiv = pageRendered.find("div",{"class":"page-status"}) # extract the page-status div
status = statusDiv.get_text() # extract the text from the div
status = status.strip() # remove the whitespace
except: # enter exception block
sys.exit(2) # exit script with code 2, script fail
if status == "All Systems Operational": # test return value
sys.exit(0) # exit with code 0, no errors
else: # else
sys.exit(1) # exit with code 1, Github down
 

When Github is operational, the text in the status variable is 'All Systems Operational' and the Python code returns 0 to the caller.  Otherwise, the Python code returns 1.

We can use this code by calling it from an Operating System (cmdexec) job step.  To do this, we need to add some wrapping code using the basic Windows scripting language to call the Python script we created.

py c:\temp\CheckGithub.py
SET EXITCODE = %ERRORLEVEL%
IF %EXITCODE% EQ 0 (
REM Github status is operational
EXIT 0
)
IF %EXITCODE% EQ 1 (
REM Github status is not fully operational
EXIT 1
)
IF %EXITCODE% EQ 2 (
REM The script failed to run correctly
EXIT 2
)

Now in the SQL Server Agent Job step, we have the option to specify the correct return code for a successful execution.  By default this is set to 0, which is what we need - if Github is operational, the exit code 0 is surfaced to the job execution engine and the step is successful.  Now, we can specify some behavior in the job step itself.

Let's define the first job step.  We will use our wrapping code which calls our new Python procedure.  We'll set the behavior to be to quit the job on success, and advance to the next step on failure, logging the output.  See the screenshot below.

We put the wrapper code in the job step and define advanced properties.

Now let's define Step 2 to throw a custom error.  This error will be trapped by the SQL Agent alerts process and fire an email to the development/DBA teams (operators).

RAISERROR('Github status non-operational or not found.  Please check https://www.githubstatus.com for more information.', 50000, 1) WITH LOG;

We define the step that throws the error.

Now let's set up an alert which fires when event with error 50000 is detected, and notifies the DBA and development teams.

We now define an alert to be sent to the DBA and development teams.

And that's it.  Now let's see if it works.  Currently, the job has no schedule, but you can easily create one to run once a minute, for example.  When the job runs successfully, nothing should happen - no alert is raised.

The job runs successfully.

When Github is unavailable, we record an error in the SQL Server log and send an email to the operators.

Shows the error message recorded in the log.

We can easily modify this to run via PowerShell too, which would allow us, for example, to differentiate between exit codes 2 and 3, something our example does not currently do - we simply trap the exit code in a PowerShell variable and perform some action in response, i.e. email the teams.  Here's a PowerShell variant for the wrapping code.  You will not need to create step 2 using this method:

import-module sqlserver 
$pyProcess = Start-Process py.exe -WindowStyle Hidden -ArgumentList "c:\temp\CheckGithub.py" -PassThru -Wait
if ( $pyProcess.ExitCode -eq 1 ) {
# Notify teams that Github appears to be non-operational
Invoke-Sqlcmd -Query "Github status non-operational or not found.  Please check https://www.githubstatus.com for more information.', 50000, 1) WITH LOG;" -ServerInstance "localhost" -Database "master" }
if ( $pyProcess.ExitCode -eq 2 ) {
# Notify teams that the job has failed.
Invoke-Sqlcmd -Query "Github status checker has failed.', 50000, 1) WITH LOG;" -ServerInstance "localhost" -Database "master" }

Another alternative to differentiate between the different exit codes is to continue using the command shell approach, but to test for exit code 1 (script works but Github not showing as operational), if true then go to the next step raising an error that Github is unavailable, if false test again for exit code 0, if true exit without error, and if false raise an error that the job itself failed.  You can amend the job steps to suit your circumstances.

Now you know how to call Python code from SQL Server, you can integrate your Python-based processes (monitoring, alerting, web stack, business logic, the list is endless) with your SQL Server jobs.

Next Steps

Please use the following links for further reading about the concepts introduced in this tip.



Last Updated: 2019-01-30


get scripts

next tip button



About the author
MSSQLTips author Derek Colley Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools