How to execute Python code in Microsoft SQL Server on T-SQL

PREREQUISITES AND SQL SERVER PREPARATION

In order to execute the Python code in an SQL Server database, certain conditions must be met.

MICROSOFT SQL SERVER IS AT LEAST VERSION 2017

Support for Python language appeared only in Microsoft SQL Server 2017, so it is possible to execute code on Python only starting from 2017 version of SQL Server.

THE “MACHINE LEARNING SERVICES” MUST BE INSTALLED

In Microsoft SQL Server 2016 for the first time it is possible to execute instructions directly in the database in other languages, in particular, the R language.

WHAT IS “MACHINE TRAINING SERVICES” IN SQL SERVER

Machine Training Services is an SQL Server component that allows you to run scripts in Python and R with relational data.

INSTALLING THE “MACHINE TRAINING SERVICES” COMPONENT

If you do not have “Machine Training Services” installed, you can reinstall them. To do this, run “SQL Server Installation Center“, on the “Installation” tab select “New installation of an isolated instance of SQL Server or adding components to an existing installation“.

ON SQL SERVER, YOU MUST ALLOW THE USE OF EXTERNAL SCRIPTS

By default, the execution of external scripts using the Python language in Microsoft SQL Server is prohibited, so you must first enable this feature on SQL Server.

HOW TO ALLOW USING EXTERNAL SCRIPTS IN SQL SERVER

To allow the use of external scripts in the Python language, you must enable the system parameter “external scripts enabled” in SQL Server, this is done using the system procedure sp_configure.

EXECUTING PYTHON CODE IN MICROSOFT SQL SERVER IN T-SQL

Python code is executed, as well as code of other languages, such as R, in Microsoft SQL Server using the system stored procedure sp_execute_external_script.

SYNTAX SP_EXECUTE_EXTERNAL_SCRIPT

sp_execute_external_script
@language = N'language',
@script = N'script'
[ , @input_data_1 = N'input_data_1' ]
[ , @input_data_1_name = N'input_data_1_name' ].
[ , @output_data_1_name = N'output_data_1_name' ].
[ , @parallel = 0 | 1 ]
[ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ , ...n ]' ]
[ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ , ...n ] ]
[ WITH RESULT SETS ( <result_sets_definition> )] ;

  • @language — the parameter in which the script language is specified;
  • @script — script text;
  • @input_data_1 — the parameter specifies the input data used by external script in the form of T-SQL query;
  • @input_data_1_name — the parameter specifies the name of the variable used to represent the query defined in @input_data_1;
  • @output_data_1_name — the parameter specifies the variable name in the external script that contains data returned to SQL Server after the stored procedure call;
  • @parallel — the parameter includes parallel execution of R scripts (value 1). Default value for this parameter is 0 (without parallelism);
  • @params — declaration of input parameters used in the external script;
  • @parameter1 — list of values of input parameters, used in the external script;
  • WITH RESULT SETS — by default the resulting set returned by the sp_execute_external_script procedure is displayed with unnamed columns. To name the columns of the resulting set, use the WITH RESULT SETS sentence for the EXECUTE command.

EXAMPLES OF PYTHON CODE EXECUTION IN SQL SERVER

Let’s run some examples.

Example 1 — Output the result into a standard data output

In this case, we simply perform some calculations and get the result into a standard data output.

Example 2 — Output the result in tabular form

Let’s make our Python code a bit more complicated and form data on it in such a way as to get it in our usual form, i.e. tabular.

Example 3 — Transferring data to Python code

Here we will consider an example of transmitting data, e.g. tabular data, received with a simple SELECT query to a procedure.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Akademily

Akademily

We conduct reviews, guides and comparative tests of gaming laptops, monitors, graphics cards, keyboards, mouses, headsets and chairs to help you buy the best ga