Python in SQL Server: The Basics

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:

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

number1 = 50

number2 = 10

result = number1 + number2

print(result)

result = number1 - number2

print(result)

result = number1 / number2

print(result)

result = number1 * number2

print(result)

result = number1 % number2

print(result)

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:

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

number1 = 50

number2 = 10

number1 += number2

print(number1)

number1 = 50

number2 = 10

number1 -= number2

print(number1)

number1 = 50

number2 = 10

number1 *= number2

print(number1)

number1 = 50

number2 = 10

number1 /= number2

print(number1)

number1 = 50

number2 = 10

number1 %= number2

print(number1)

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:

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

number1 = 50

number2 = 10

print(number1 == number2)

print(number1 != number2)

print(number1 > number2)

print(number1 < number2)

print(number1 >= number2)

print(number1 <= number2)

The output looks like this:

Logical Operations

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

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

print(True and True)

print(True or False)

print(not(True))

Output:

IF and Else Statements

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

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

number1 = 50

number2 = 10

if number1 > number2:

print("Number 1 is greater than number 2")

else:

print("Number 2 is less than or equal to number 1")

Output:

Lists, Tuples, and Dictionaries

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

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

print("Creating a List")

cars = ["Toyota", "Honda", "Audi"]

print(cars[1])

print("Creating a Tuple")

cars2 = ("Ford", "Renault", "Mercedez")

print(cars2[2])

print("Creating a Dictionary")

cars3 = {1:"BMW", 2:"Suzuki", 3:"Tata"}

print(cars3[3])

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:

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

numbers = [1, 2, 3, 4, 5]

for num in numbers:

print(num)

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:

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

num = 0

while num < 10:

print(num)

num = num +1

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.

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

def printCube (num):

print(num ** 3)

printCube(5)

printCube(2)

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.

EXEC sp_configure 'external scripts enabled', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_execute_external_script

@language =N'Python',

@script = N'

import numpy as np

cube2 = np.power(2,3)

cube5 = np.power(5,3)

print(cube2 )

print(cube5)

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.

SQLShack

Python in SQL Server: The Basics

Installing ML Services and Activating External Scripts

Python Basic Operations

Arithmetic Operations

Assignment Operations

Comparison Operations

Logical Operations

IF and Else Statements

Lists, Tuples, and Dictionaries

Loops

For Loop

While Loop

Functions

Importing External Libraries

Conclusion

Other great articles from Ben

Installing ML Services and Activating External Scripts

Python Basic Operations

Arithmetic Operations

Assignment Operations

Comparison Operations

Logical Operations

IF and Else Statements

Lists, Tuples, and Dictionaries

Loops

For Loop

While Loop

Functions

Importing External Libraries

Conclusion

Other great articles from Ben

Related posts: