Create Python apps using SQL Server

Create Python apps using SQL Server on Windows

In this section, you will get SQL Server 2017 on Windows. After that you will install the necessary dependencies to create Python apps with SQL Server.

Step 1.1 Install SQL Server

  1. If you don’t have SQL Server 2017 Developer (or above) installed, click here to download the SQL Server exe.

  2. Run it to start the SQL installer.

  3. Click Basic in Select an installation type.

  4. Click Accept after you have read the license terms.

  5. (Optional) if you need to, you can choose a custom installation location for SQL Server.

  6. Click Install to proceed with the installation.

You now have SQL Server installed and running locally on your Windows computer! Check out the next section to continue installing prerequisites.

Step 1.2 Install Python

Download and run the installer here

Next, add Python to your path

  1. Press start

  2. Search for “Advanced System Settings”

  3. Click on the “Environment Variables” button

  4. Add the location of the Python27 folder to the PATH variable in System Variables. The following is a typical value for the PATH variable C:\Python27

You have succesfully installed Python on your machine!

Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

  1. Install the ODBC Driver.

  2. Install the SQL Server Command Line Utilities.

After installing SQLCMD, you can connect to SQL Server using the following command from a CMD session:Terminal

sqlcmd -S localhost -U sa -P your_password
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.

This how to run a basic inline query. The results will be printed to STDOUT.Terminal

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

Results

--------------------------------------------------------
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
  Apr 29 2016 23:23:58
  Copyright (c) Microsoft Corporation
  Developer Edition (64-bit)

1 rows(s) returned

Executed in 1 ns.

You have successfully installed SQL Server Command Line Utilities on your Windows machine!

Step 2.1 Install the Python driver for SQL Server

Terminal

pip install virtualenv #To create virtual environments to isolate package installations between projects
virtualenv venv
venv\Scripts\activate
pip install pyodbc

Step 2.2 Create a database for your application

Connect to SQL Server using SQLCMD and execute the following statement to create a database called SampleDB.Terminal

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

Step 2.3 Create a Python app that connects to SQL Server and executes queries

Create a new folder for the sampleTerminal

mkdir SqlServerSample
cd SqlServerSample

Execute the T-SQL scripts below in the terminal with sqlcmd to a table and insert some row.Terminal

sqlcmd -S localhost -U sa -P your_password -Q "USE DATABASE SampleDB; CREATE TABLE Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
sqlcmd -S localhost -U sa -P your_password -Q "USE DATABASE SampleDB; INSERT INTO Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"

Using your favorite text editor, create a new file called crud.py in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.Python

import pyodbc
server = 'localhost'
database = 'SampleDB'
username = 'sa'
password = 'your_password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

print ('Inserting a new row into table')
#Insert Query
tsql = "INSERT INTO Employees (Name, Location) VALUES (?,?);"
with cursor.execute(tsql,'Jake','United States'):
    print ('Successfully Inserted!')


#Update Query
print ('Updating Location for Nikita')
tsql = "UPDATE Employees SET Location = ? WHERE Name = ?"
with cursor.execute(tsql,'Sweden','Nikita'):
    print ('Successfully Updated!')


#Delete Query
print ('Deleting user Jared')
tsql = "DELETE FROM Employees WHERE Name = ?"
with cursor.execute(tsql,'Jared'):
    print ('Successfully Deleted!')


#Select Query
print ('Reading data from table')
tsql = "SELECT Name, Location FROM Employees;"
with cursor.execute(tsql):
    row = cursor.fetchone()
    while row:
        print (str(row[0]) + " " + str(row[1]))
        row = cursor.fetchone()

Run your Python script from the terminal.Terminal

python crud.py

Results

Inserting a new row into table
Successfully Inserted!
Updating Location for Nikita
Successfully Updated!
Deleting user Jared
Successfully Deleted!
Reading data from table
Jake United States

Congratulations! You created your first Python app with SQL Server! Check out the next section to learn about how you can make your Python app faster with SQL Server’s Columnstore feature.

Step 3.1 Create a new table with 5 million using sqlcmd

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

Step 3.2 Create a Python app that queries this tables and measures the time taken

Terminal

mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

Using your favorite text editor, create a new file called columnstore.py in the SqlServerColumnstoreSample folder. Paste the following code inside it.Python

import pyodbc
import datetime
server = 'localhost'
database = 'SampleDB'
username = 'sa'
password = 'your_password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
tsql = "SELECT SUM(Price) as sum FROM Table_with_5M_rows"
a = datetime.now()
with cursor.execute(tsql):
  b = datetime.now()
  c = b - a
  for row in cursor:
    print ('Sum:', str(row[0]))
  print ('QueryTime:', c.microseconds, 'ms')

Step 3.3 Measure how long it takes to run the query

Run your Python script from the terminal.Terminal

python columnstore.py

Results

Sum: 50000000
QueryTime: 363ms

Step 3.4 Add a columnstore index to your table.

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;" Copy

Step 3.5 Measure how long it takes to run the query with a columnstore index

Terminal

python columnstore.py

Results

Sum: 50000000
QueryTime: 5ms

Congratulations! You just made your Python app faster using Columnstore Indexes!

Reference : https://www.microsoft.com/en-us/sql-server/developer-get-started/python/windows

PreviousPython SQL LibrariesNextPython

Last updated