Ben Richardson

Python in SQL Server: The Basics

February 13, 2019 by

With the introduction of SQL Machine Learning Services, it is now possible to run Python Scripts from any SQL Server client such as SQL Server Management Studio. In addition to directly running the Python Scripts on SQL Server Clients, you can write Python Code on native Python editors and run it remotely on SQL Server using Python clients for SQL Server.

In this article, we will see how to execute some of the basic Python functionalities within SQL Server Management Studio. The article provides an introduction to running basic Python scripts in SQL Server Management Studio.

Installing ML Services and Activating External Scripts

Before you can run Python scripts on SQL Server, you need to install SQL Server with Machine Learning Services enabled. The process of installing SQL Server with machine learning services has been explained in detail in Configuring R Services in SQL Server. The article also explains how to activate the external scripts and run your first Python program. I would advise you to read that article, set up the environment for running the Python script and then come back and continue this article from here.

Python Basic Operations

Following are some of the basic types of operations that you can perform with Python in SQL Server.

Arithmetic Operations

You can perform all the basic Python arithmetic operations on SQL Server as shown below:

In the script above, addition, subtraction, multiplication, division and modulus operation has been performed on two numbers and the result has been printed on the console. The output looks like this:

Assignment Operations

Assignment operations are used to assign the value of a constant or a variable to another variable. SQL Server supports all the Python assignment operators such as assign (=), add and assign (+=), subtract and assign (-=), multiply and assign (*=), divide and assign (/=), and modulus and assign (%=). Look at the following examples:

Output:

Comparison Operations

Some of the Python Comparison Operations supported by SQL Server are equals (==), not equals (! =), greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=). Look at the following example:

The output looks like this:

Logical Operations

SQL Server supports three logical Python operations: AND, OR, NOT. They are as follows:

Output:

IF and Else Statements

The if/else statements are used for decision making in Python.

Output:

Lists, Tuples, and Dictionaries

Lists, tuples, and dictionaries are the most commonly used Python data structures that store collections of data.

In the script above, we create a list “cars”, a tuple “cars2” and a dictionary “cars3”. We then display the second item from the list and the third item from the tuple using their indexes. Finally, we print the dictionary item with the key 3.

The output looks like this:

Loops

The loops are used to repeatedly execute a piece of code. They can also be used to iterate through the items of a collection.

For Loop

The “for loop” is used to repeatedly execute code for a specified number of times or until all the items in a list, tuple or dictionary have been iterated. Look at the following example:

In the output, you will see all the numbers in the “numbers” list, printed on the console as shown below:

While Loop

The “while loop” executes until the condition specified by the while loop becomes false. Have a look at this code:

The while loop in the script above will execute until the value for the “num” becomes greater or equal to 10.

Functions

SQL Server also supports both built-in and custom Python functions. The following example demonstrates the use of custom Python function in SQL Server.

In the script above we defined a function “printCube” which accepts one parameter and returns its cube. We then use this function to find the cube of numbers 5 and 2 respectively. The output looks like this:

Importing External Libraries

Like any Python editor, you can also import Python libraries in any SQL Server client such as SQL Server Management Studio. In the following script, we will import the Python Numpy library and will use its “power” function to take the power of a number.

In the output, you will see the cube of 2 and 5.

Conclusion

SQL Server, with the addition of Machine Learning Services is capable of running Python Scripts. In this article, we ran Python scripts for some of the most basic functionalities using SQL Server. We saw how to run a variety of Python operations, decision statements, iteration statements, list, tuples, dictionaries, and functions using SQL Server Management Studio. After reading this article, you should be comfortable with executing basic Python scripts on SSMS.

Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
138 Views