By: Derek Colley | Updated: 2019-01-30 | Comments (1) | Related: > 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:
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.
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;
Now let's set up an alert which fires when event with error 50000 is detected, and notifies 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.
When Github is unavailable, we record an error in the SQL Server log and send an email to the operators.
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.
- Python Releases for Windows: https://www.python.org/downloads/windows/
- Get pip: https://pip.pypa.io/en/stable/installing/
- BeautifulSoup documentation: https://www.crummy.com/software/BeautifulSoup/
- MSSQLTips.com - Siddharth Mehta, SQL Server 2017 and Python Basics: https://www.mssqltips.com/sqlservertutorial/9073/sql-server-2017-and-python-basics/
- MSSQLTips.com - Siddharth Mehta, Python in SQL Server Ecosystem: https://www.mssqltips.com/sqlservertutorial/9074/python-in-sql-server-ecosystem/
- MSSQLTips.com - Various, SQL Server Agent Tips: https://www.mssqltips.com/sql-server-tip-category/27/sql-server-agent/
About the author
Dr. Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-01-30
