Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Basic Python Concepts



By:
Overview

Python is an interpreted, interactive, object-oriented programming language. It incorporates modules, exceptions, dynamic typing, very high level dynamic data types, and classes. Python has packages that encapsulates different categories of functionality in libraries (also called packages). For applying the statistical analysis one often needs sample data. Assuming that we already have sample datasets at hand, we can use Python for analyzing the statistical computations and algorithms. To develop these computations, one needs to know regular programming constructs like variables, data types, operators, loops, etc.

Explanation

Most of the programming constructs that are available in Python are also available in T-SQL. Our intention is not to learn Python in full detail, but to learn Python constructs that enable us to consume the unique Python libraries and data processing / computation mechanisms that are not available in T-SQL. In this lesson we will be learning the basic concepts of Python, just sufficient enough for us to apply Python functions and packages, so that we can apply the concepts for the data passed from the SQL Server data repository.

Python version, packages and datasets

We already learned in the last lesson how to check the version of Python runtime that the database engine is communicating. It necessary to know the version of Python you are working with, as that can be considered as the basis of what is supported by a particular version of Python. Using the sp_execute_external_script, with a simple Python property “sys.version”, we can check the details of Python version as shown below. Print function prints the output on the SQL Server Management Studio (SSMS) message console. In this lesson our focus is developing the fundamentals of Python. We will discuss the details of sp_execute_external_script in the next lesson. Until then just consider this procedure as execution wrapper.

SSMS Python Testing - Description: SSMS Python Testing

The next step is to explore the different libraries available with Python installation by default. You can explore them from here. You can load any given library by using the import function. We already looked at an example of the use of this function in the above code.

Variables, Comments and Printing Data

In Python, a variable is created by using the assignment operator “=“. Python variables do not need explicit declaration. The data type of the variable is determined by the data stored in R. The code can be commented in Python using the # character. The operand to the left of the = operator is the name of the variable and the operand to the right of the = operator is the value stored in the variable. Let’s understand these concepts by an example.

execute sp_execute_external_script 
@language = N'Python',
@script = N'
 
var1 = "Siddharth"
Var1 = "Sid"
var2 = 100
var3 = 50.5
var4 = "TRUE"
 
print(var1)
print(Var1)
print(var2 + var3)
print(var4)
 
print(type(var1))
print(type(var2))
print(type(var3))
print(type(var4))
'
   

When executing the above code and the output should look as shown below. Below are the points you can derive from the above example:

  • Variables can be created using the “=“ (assignment) operator.
  • Variables are case-sensitive. Var1 and var1 are considered different variables.
  • Data-type of the variable is determined by the type of data stored in the variable.
  • You can get the value of variables using the print function
  • Type function can be used on variables to determine the data type of the variables which is classified in five major types – numbers, string, list, tuple and dictionary.
  • There are other data structure types too, but we are limiting our discussion to the basic types.
Python variables - Description: Python variables

Arithmetic, Operators, Loops

The table below shows a list of arithmetic and logical operators in Python. It’s not an exhaustive list, but covers major operators that you may use normally when you start learning Python.

Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
** Exponentiation
% Modulus
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
== Exactly equal to
!= Not equal to
! NOT
| OR
& AND

Though these operators are self-explanatory, below is a basic example of how you can use these operators. Here we have used these operators on actual values. You can use these operators in the same way on variables too.

Python Operators - Description: Python Operators

Finally, considering we have the data with us, there is a high possibility that we may have to loop into the data for applying some statistical computations. So we need to learn at least one looping technique in Python. Below is a simple example of a while loop.

execute sp_execute_external_script 
@language = N'Python',
@script = N'
 
count = 0
while (count < 9):
   print ("The count is:", count)
   count = count + 1
   if (count == 5) : break
'
   

In this example we are assigning the value of 0 to a variable “count”. We are printing the value of “count” in the loop and incrementing the value of i. We are also placing a condition that if value of count reaches 5, then break out of the loop using the “break” statement.

Python Loop - Description: Python Loop

We have not learned anything in this lesson that we cannot achieve with T-SQL. But once the data is submitted from SQL Server data tables / views to Python runtime for processing the data, we need to use programming constructs in Python language. Now that we have a basic idea of how to apply arithmetic on the data in Python, let's learn how to accept and receive data between T-SQL and Python in the next lesson.

Additional Information
  • Consider implementing all the operators and practice to use the same on variables in Python.

Last Update: 9/18/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 01, 2018 - 2:48:58 PM - Abhi Reddy Back To Top

execute sp_execute_external_script
@language = N'Python',
@script = N'
 
var1 = "Siddharth"
Var1 = "Sid"
var2 = 100
var3 = 50.5
var4 = "TRUE"
 
print(var1)
print(Var1)
print(var2 + var3)
print(var4)
 
print(type(var1))
print(type(var2))
print(type(var3))
print(type(var4))
'

I am getting following error:

Msg 39020, Level 16, State 2, Procedure sp_execute_external_script, Line 1 [Batch Start Line 4]
Feature 'Advanced Analytics Extensions' is not installed. Please consult Books Online for more information on this feature.


Learn more about SQL Server tools