Get started with Python on SQL Server – Run Python with T-SQL on SSMS

With SQL Server 2016 Microsoft added Machine Learning support with R Language in SQL engine itself and called it SQL Server R Services .

Going ahead with the new SQL Server 2017 version Microsoft added Python too as part of Machine Learning with existing R Language , and thus renamed it to SQL Server Machine Learning Services .

Installation/Setup

Here are few steps to get you started with Python programming in SQL Server, so that you can run Python scripts with T-SQL scripts within SSMS:

1. Feature Selection:While installing SQL Server 2017 make sure you’ve selected below highlighted services

2. Configure “external scripts enabled”:Post installation run below SQL statements to enable this option

sp_configure 'external scripts enabled'
GO
sp_configure 'external scripts enabled', 1;
GO
RECONFIGURE; 
GO
sp_configure 'external scripts enabled'
GO

3. Restart SQL Server service:by “services.msc” program from command prompt, and run below SQL statement, this should show run _value = 1

sp_configure 'external scripts enabled'
GO

4. Launchpad Service:Make sure this service is running, in “services.msc” program from command prompt. Restart the service, and it should be in Running state.

Its a service to launch Advanced Analytics Extensions Launchpad process that enables integration with Microsoft R Open using standard T-SQL statements. Disabling this service will make Advanced Analytics features of SQL Server unavailable.

Post restarting this service, it should return run _value = 1 on running the query mentioned at Step #3

Run Python from SSMS

So as you’ve installed SQL Server with ML services with Python & R, and enabled the components, now you can try running simple “Hello World!” program to test it:

EXEC sp_execute_external_script 
	@language = N'Python', 
	@script = N'print(''Hello Python !!! from T-SQL'')'

Let’s do simple math here:

EXEC sp_execute_external_script 
@language = N'Python', 
@script = N'
x = 5
y = 6
a = x * y
b = x + y
print(a)
print(b)'

If you still face issues you can go through addition configuration steps mentioned in [ MSDN Docs link ] .

Advertisements