SQL Server 2017 – In-Database Python Setup

January 15, 2018

With SQL Server 2016, the ability to execute R code in SQL Server was added. SQL Server 2017 added functionality to run Python code as well. This functionality for either language can be installed as part of the SQL Server installation, or added to an existing instance.

Issues:
My initial attempt at Python installation didn’t go smoothly. Multiple attempts at install would hang up and not progress. However, once I removed the Python 3 installation from the machine, the install was able to complete.
The Launchpad service gets installed as part of the Python/R package as well. This service needs to be running in order to execute Python or R code. At first I created a new user for the service, but I ran into errors trying to execute code.
Msg 39021, Level 16, State 1, Line 55
Unable to launch runtime for ‘Python’ script. Please check the configuration of the ‘Python’ runtime.
Msg 39019, Level 16, State 2, Line 55
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

I ended up giving the service an administrator account to run under, which cleared up this issue.

Configuration:
The ‘External Scripts Enabled’ setting should be set to true in order to run Python/R code.

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

Execution:
I’ll create a small table to have some test data.

drop table if exists dbo.Team;
create table dbo.Team(Team varchar(20) not null primary key);
insert into dbo.Team values('Falcons'), ('Saints'), ('Panthers'), ('Buccaneers');

And then run a simple loop to read the team names from the table and write them to the results pane.

exec sp_execute_external_script @language = N'Python', @script = N'for x in InputDataSet.Team: print(x)', @input_data_1 = N'select Team from dbo.Team';

InputDataSet is the default name for the data set read in from the database. There is a parameter for the sp_execute_external_script proc to set a different name, if so desired.

Additional Links:
sp_execute_external_script
Set up Python Machine Learning Services


Python Data Access Introduction

June 23, 2014

In researching some aspects of data science, I ran across this article Python Displacing R As The Programming Language For Data Science. Instead of using a domain specific language like R, people are preferring to use a general purpose language for the same functions, so I wanted to get familiar with the basics of Python.

Installing Python:
We need to install Python, as well as an editor or IDE for coding. JetBrains (the creators of ReSharper) have a Python IDE PyCharm, and they make a community edition available.
I went with the latest edition of Python (3.4.1), although the version 2 is still available.
Download Python 3.4.1
IDE – Jetbrains – PyCharm
I’m a big fan of using Chocolatey for installs, so the commands for the two needed components are:
cinst PyCharm-community
cinst python

Getting Started:
Python is an interpreted, dynamically typed and strongly typed language. It is case sensitive and everything is an object.
Comments are preceded with a #.
To get help on an object: help(object).
dir(object) will list all of an object’s methods.
On opening the IDE, we need to select a Python Interpreter – Browse to the intalled Python.exe.

Code Examples:
I wanted to run through some examples with working with data. For the first, I read data from a file (using the results from the 2013 Atlanta Falcons season) to calculate the average scores.
The data file is at 2013 Falcons Results

from statistics import mean
import csv

filePath = "E:/2013FalconsResults.txt"

falconsScores = []
opponentsScores = []

with open(filePath, "r") as f:
    reader = csv.DictReader(f, delimiter = "\t")
    help(reader)
    for row in reader:
        print(row["WeekNumber"] + " : " + row["Opponent"])
        falconsScores.append(int(row["FalconsScore"]))
        opponentsScores.append(int(row["OpponentScore"]))

print ("Falcons average score: " + str(mean(falconsScores)))
print ("Opponents average score: " + str(mean(opponentsScores)))		

For the 2nd example, we’ll connect to a database to get the same Falcons’ results and display them. The data file is available at 2013 Falcons Results, this can be imported into a ‘FalconsResults2013’ table in your database.

import pyodbc

connectionString = 'DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=yes;'

db = pyodbc.connect(connectionString)
cursor = db.cursor()
cursor.execute ('select WeekNumber, GameDate, Opponent, Result, FalconsScore, OpponentScore, HomeGame from FalconsResults2013')
rs = cursor.fetchall()
for row in rs:
    print ('Week #' + str(row[0]) + ': Falcons ' + str(row[4]) + ' ' + row[2] + ' ' + str(row[5]))

Documentation:
Python Documentation:
https://www.python.org/doc/

Learn Python The Hard Way:
http://learnpythonthehardway.org/book/

Python in 10 minutes:
http://www.stavros.io/tutorials/python/