SQL Server’s integration of Python has been heavily marketed towards the machine learning and BI guys, but does it offer anything for the DBA?

All the attention has been on machine learning, so much so that for a while I didn’t pay it any attention at all, but then I got thinking to myself.  DBAs all over the place are loving PowerShell at the moment, it can do so much can’t it?  It’s fantastic for automation, we can push out scripts to multiple servers at the press of a button, we can pull data from all our instances into a centralised management database.  It’s great for keeping things up to date, it makes central management of our instances as easy as you like and monitoring, no need to have jobs or agents running on all those severs anymore reporting in, a single PowerShell script can go out and find out everything that we need to know.

Now, Python’s a very rich programming language, if only it could connect to a remote SQL Server it’d suddenly be easily as useful as PowerShell has proven to be.  If it could, we’d be able to go out and access all our servers from within a single stored proc with no need what so ever for nasty things like CMDEXEC or linked servers.

Well it can, just check this out….

Selecting From A Remote Server

Unlike when using PowerShell, there’s no need to make external calls using CMDEXEC, we can run our Python scripts all from within T-SQL using the rather lovely sp_execute_external_script.  This is the same proc that you’d use to run R scripts (if you’re that way inclined) but now with SQL2017 we can use it to run Python scripts too.  A part of me wonders if Microsoft has plans to extend it’s scope further, allowing it to also run PowerShell and perhaps even .NET code.

The basic syntax of sp_execute_external_script is simple, all we need to do is assign the @language parameter, the language that we’re using, in this case ‘Python’ and the @script parameter will take the code that we want to run.

Microsoft has very kindly provided us with a whole bunch of packages that do all sorts of funky things.  To connect to a remote server and return a dataset there are two that we’re interested in, pyodbc and pandas.  pyodbc exposes an API which we can use to connect to our server and pandas is a package primarily designed for data analysis, but for our purposes it can return a dataset to SQL.

The following code will connect to a remote server and return a list of databases on that instance.

EXEC sp_execute_external_script
@language = N'python',
@script =
N'import pyodbc
import pandas as pa
connection = pyodbc.connect(''DRIVER={ODBC Driver 13 for SQL Server};SERVER=SQL01\sql2016;UID=PythonUser;PWD=P4ssw0rd'')
query = ''SELECT name FROM sys.databases''
OutputDataSet = pa.read_sql(query,connection)
'
WITH RESULT SETS UNDEFINED

The code is fairly simple, we start by importing the two packages that we need.  We create a connection object using a connection string to our remote instance of SQL.  We assign our sql command to the query variable and finally call the Pandas method ‘read_sql’ which will use the provided connection string to connect to the remote instance and run the query.  The results are assigned to OutputDataSet and it’s this dataset that’s returned to SQL.

If we wanted to we can even pass parameters into the script, we can use @params to define parameters to pass into the python script.  The example below passes the sql query into the script.

EXEC sp_execute_external_script
@language = N'python',
@script =
N'import pyodbc
import pandas as pa
connection = pyodbc.connect(''DRIVER={ODBC Driver 13 for SQL Server};SERVER=SQL01\sql2016;UID=PythonUser;PWD=P4ssw0rd'')
OutputDataSet = pa.read_sql(query,connection)
',
@params = N'@query VARCHAR(100)',
@query = 'SELECT name FROM sys.databases'
WITH RESULT SETS UNDEFINED

Modifying Data

So we can select from a remote server but how do we go about modifying data?

Quite simple really, take a look at the code below for a quick update….

EXEC sp_execute_external_script
@language = N'python',
@script =
N'import pyodbc
connection = pyodbc.connect(''DRIVER={ODBC Driver 13 for SQL Server};SERVER=SQL01\sql2016;UID=PythonUser;PWD=P4ssw0rd;DATABASE=AdventureWorks'')
cursor = connection.cursor()
cursor.execute("UPDATE person.person SET LastName = ''Green'' WHERE LastName = ''Brown''")
connection.commit()
connection.close()
'

Conclusion

So there you have it, while there’s nothing Earth shattering here I hope you can see how useful Python could be for the DBA and not just the BI\Data Scientist.   The real beauty is that all this code could happily sit inside a stored proc, something that you can’t do all that well with PowerShell.