Basics of R and Python Execution in SQL Server

  • Article

In a previous post, we talked about how to set up Machine Learning Services in SQL Server. Now I will cover the basics of how to Execute R and Python in T-SQL statements. The video below will help you understand the basic syntax, parameter names, and data types used. I also summarized the key steps if you want to read along. Check out the SQL Machine Learning Services documentation to learn more.

How to Execute R/Python Code in SQL Server

Basics

Ensure Machine Learning Services is already installed and enabled. After setup I will now show you the basics of executing R and Python code within a T-SQL statement. While I use Python in these samples, you can do everything with R as well.

  1. Open up SQL Server Management Studio and make a connection to your server.
  2. Open a new query.
  3. Paste this basic example:
  4. EXEC sp_execute_external_script @language = N'Python', @script = N'print(3+4)'
  5. Sp_execute_external_script is a special system stored procedure that enables R and Python execution in SQL Server.
  6. There is a "language" parameter that allows us to choose between Python and R.
  7. There is a "script" parameter where we can paste R or Python code.

Parameter introduction

Now that we discussed a basic example, let's start adding more pieces.

EXEC sp_execute_external_script  @language =N'Python', @script = N'OutputDataSet = InputDataSet;', @input_data_1 =N'SELECT 1 AS Col1';

  1. Machine Learning Services provides more natural communications between SQL and R or Python with an input data parameter that accept any SQL query. The input parameter name is called "input_data_1".
  2. You can see in the python code that there are default variables defined to pass data between Python and SQL. The default variable names are "OutputDataSet" and "InputDataSet"
  3. You can change these default names like this example:
  4. EXEC sp_execute_external_script  @language =N'Python', @script = N'MyOutput = MyInput;', @input_data_1_name = N'MyInput', @input_data_1 =N'SELECT 1 AS foo', @output_data_1_name =N'MyOutput';
  5. As you executed these examples, you might have noticed that they each return a result with "(No column name)"? You can specify a name for the columns that are returned by adding "WITH RESULT SETS" to the end of the statement:
  6. EXEC sp_execute_external_script  @language =N'Python', @script=N'MyOutput = MyInput;', @input_data_1_name = N'MyInput', @input_data_1 =N'SELECT 1 AS foo,2 AS bar', @output_data_1_name =N'MyOutput' WITH RESULT SETS ((MyColName int, MyColName2 int));
  7. WITH RESULT SETS is a comma separated list of column names and their datatypes.

Input/Output DataTypes

Alright, let's discuss a little more about the input/output data types used between SQL and Python.

  1. Your input SQL SELECT statement passes a "Dataframe" to python relying on the Python Pandas package.
  2. Your output from Python back to SQL also needs to be in a Pandas Dataframe object. If you need to convert scalar values into a dataframe here is an example:
  3. EXEC sp_execute_external_script  @language =N'Python', @script=N'import pandas as pdc = 1/2d = 1*2s = pd.Series([c,d])df = pd.DataFrame(s)OutputDataSet = df'
  4. Variables c and d are both scalar values, which you can add to a pandas Series if you like, and then convert them to a pandas dataframe.
  5. This one shows a little bit more complicated example, go read up on the python pandas package documentation for more details and examples.
  6. EXEC sp_execute_external_script  @language =N'Python', @script=N'import pandas as pds = {"col1": [1, 2], "col2": [3, 4]}df = pd.DataFrame(s)OutputDataSet = df'

You now know the basics to execute Python in T-SQL! Did you know you can also write your R and Python code in your favorite IDE like RStudio and Jupyter Notebooks and then remotely send the execution of that code to SQL Server? Check out these documentation links and stay tuned for more blog posts:
https://aka.ms/R-RemoteSQLExecution
https://aka.ms/PythonRemoteSQLExecution

Leave a comment below to ask questions or even tell us about the ways you have used Machine Learning Services in your database.