python – Rich Benner – SQL Server Consulting

Generate Test Data with Faker & Python within SQL Server

Make sure you’ve done these steps first

Then let’s get started

We’re going to use a Python library called Faker which is designed to generate test data. You’ll need to open the command line for the folder where pip is installed. In my standard installation of SQL Server 2019 it’s here (adjust for your own installation);

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019PYTHON\PYTHON_SERVICES\Scripts

From here you want to run the following command to install mimesis;

pip install Faker

1	pip install Faker

Once it’s done we’ve got it installed, we can open SSMS and get started with our test data.

We’re going to get started with the sample queries from the official documentation but we have to add a print statement to see our results because we’re using SSMS;

DECLARE @sql nvarchar(max)

SET @sql = 
N'#! usr/bin/env python3

from faker import Faker
fake = Faker()

print(fake.name())'

execute sp_execute_external_script @language = N'Python', @script = @sql

DECLARE @sql nvarchar(max)

SET @sql =

N'#! usr/bin/env python3

from faker import Faker

fake = Faker()

print(fake.name())'

execute sp_execute_external_script @language = N'Python', @script = @sql

If you run this in SSMS you’ll see the output in the messages window

Now we know that works, let’s put this into a useable format within SQL Server.

This is going to be our block of Python;

#! usr/bin/env python3
import pandas as pd

from faker import Faker
fake = Faker()

# data dictionaries is how python stores tables
data_dict = { 
                "name":[],
                "address":[]
            }

# we're inserting 100 random names and addresses into this dictionary
for x in range(100):
    data_dict["name"].append(fake.name())
    data_dict["address"].append(fake.address())

# using pandas to create a data frame makes it into a more presentable format
output_data = pd.DataFrame(data_dict)

# this is the data we're going to export to SQL Server
OutputDataSet = output_data

#! usr/bin/env python3

import pandas as pd

from faker import Faker

fake = Faker()

# data dictionaries is how python stores tables

data_dict = {

"name":[],

"address":[]

}

# we're inserting 100 random names and addresses into this dictionary

for x in range(100):

data_dict["name"].append(fake.name())

data_dict["address"].append(fake.address())

# using pandas to create a data frame makes it into a more presentable format

output_data = pd.DataFrame(data_dict)

# this is the data we're going to export to SQL Server

OutputDataSet = output_data

For the purposes of this example, we’re going to make a temp table to store the data and view what we’ve done. Wrapping this python script into t-sql will give us an output like so;

DECLARE @sql nvarchar(max)

SET @sql = 
N'#! usr/bin/env python3
import pandas as pd

from faker import Faker
fake = Faker()

# data dictionaries is how python stores tables
data_dict = { 
                "name":[],
                "address":[]
            }

# we''re inserting 100 random names and addresses into this dictionary
for x in range(100):
    data_dict["name"].append(fake.name())
    data_dict["address"].append(fake.address())

# using pandas to create a data frame makes it into a more presentable format
output_data = pd.DataFrame(data_dict)

# this is the data we''re going to export to SQL Server
OutputDataSet = output_data'

IF OBJECT_ID('tempdb..#FakeData') IS NOT NULL DROP TABLE #FakeData
BEGIN
	CREATE TABLE #FakeData
	(
	 [name] varchar(max)
	,[address] varchar(max)
	)
END

INSERT INTO #FakeData (address, name) /* data comes out by column name alphabetically */
execute sp_execute_external_script @language = N'Python', @script = @sql

SELECT * FROM #FakeData

DECLARE @sql nvarchar(max)

SET @sql =

N'#! usr/bin/env python3

import pandas as pd

from faker import Faker

fake = Faker()

# data dictionaries is how python stores tables

data_dict = {

"name":[],

"address":[]

}

# we''re inserting 100 random names and addresses into this dictionary

for x in range(100):

data_dict["name"].append(fake.name())

data_dict["address"].append(fake.address())

# using pandas to create a data frame makes it into a more presentable format

output_data = pd.DataFrame(data_dict)

# this is the data we''re going to export to SQL Server

OutputDataSet = output_data'

IF OBJECT_ID('tempdb..#FakeData') IS NOT NULL DROP TABLE #FakeData

BEGIN

CREATE TABLE #FakeData

(

[name] varchar(max)

,[address] varchar(max)

)

END

INSERT INTO #FakeData (address, name) /* data comes out by column name alphabetically */

execute sp_execute_external_script @language = N'Python', @script = @sql

SELECT * FROM #FakeData

Go ahead and run it, you should see a sample of 100 names and addresses that are currently stored in your temp table;

There are far more options when using Faker. Looking at the official documentation you’ll see the list of different data types you can generate as well as options such as region specific data.

Go have fun trying this, it’s a small setup for a large amount of time saved.

Connect to Reddit from SQL Server

Fantastic, we’re about ready to connect to Reddit from within SQL Server!

Let’s crank open SSMS and start building our query. In a new query window we’ll start building our query and test it along the way.

To execute our script we’re going to use dynamic SQL to make it easier to read. Let’s get started setting down some foundations for our script.

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'#! usr/bin/env python3
import praw
import pandas'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'#! usr/bin/env python3

import praw

import pandas'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

The first line is the ‘shebang’ line and just allows the system to find Python correctly, we won’t be changing this at all. We’re also loading PRAW and Pandas (that we installed previously) so we can use them later.

Feel free to run this script as it is, you won’t get any results at this point but you should also not see any error messages

We’re going to build our connection string to Reddit next.

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'#! usr/bin/env python3
import praw
import pandas

redditConnect = praw.Reddit(client_id=''PERSONAL_USE_SCRIPT_14_CHARS'', \
                     client_secret=''SECRET_KEY_27_CHARS '', \
                     user_agent=''YOUR_APP_NAME'', \
                     username=''YOUR_REDDIT_USER_NAME'', \
                     password=''YOUR_REDDIT_LOGIN_PASSWORD'')'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'#! usr/bin/env python3

import praw

import pandas

redditConnect = praw.Reddit(client_id=''PERSONAL_USE_SCRIPT_14_CHARS'', \

client_secret=''SECRET_KEY_27_CHARS '', \

user_agent=''YOUR_APP_NAME'', \

username=''YOUR_REDDIT_USER_NAME'', \

password=''YOUR_REDDIT_LOGIN_PASSWORD'')'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

We’re creating a variable (redditConnect) and declaring the values we need. Use your own connection details we made when creating our Reddit API.

We then need to decide which Subreddit we’re going to gather data from. I’m going to use AskReddit as it’s one of the largest text-only subreddits and will be perfect for what we’re trying to do here.

subreddit = redditConnect.Subreddit(''AskReddit'')

1	subreddit = redditConnect.Subreddit(''AskReddit'')

We also need to decide what order we’re going to put the data into. We can sort by things like new, top, controversial etc. For this let’s grab ‘new’

subredditOrder = subreddit.new()

We can then test our connection is working by printing the first row of our data

for submission in subreddit.new(limit=1):<br>
    print(submission.title, submission.id)

1 2	for submission in subreddit.new(limit=1):<br> print(submission.title, submission.id)

Our whole block of code is going to look something like this

DECLARE @sql nvarchar(max) = 
N'#! usr/bin/env python3
import praw

redditConnect = praw.Reddit(client_id=''PERSONAL_USE_SCRIPT_14_CHARS'', \
                     client_secret=''SECRET_KEY_27_CHARS '', \
                     user_agent=''REDDIT_APP_NAME'', \
                     username=''REDDIT_USER_NAME'', \
                     password=''REDDIT_PASSWORD'')

subreddit = redditConnect.subreddit(''AskReddit'')

top_subreddit = subreddit.new(limit=1000)

for submission in subreddit.top(limit=1):
    print(submission.title)' 

EXEC sp_execute_external_script @language = N'Python', @script = @sql

DECLARE @sql nvarchar(max) =

N'#! usr/bin/env python3

import praw

redditConnect = praw.Reddit(client_id=''PERSONAL_USE_SCRIPT_14_CHARS'', \

client_secret=''SECRET_KEY_27_CHARS '', \

user_agent=''REDDIT_APP_NAME'', \

username=''REDDIT_USER_NAME'', \

password=''REDDIT_PASSWORD'')

subreddit = redditConnect.subreddit(''AskReddit'')

top_subreddit = subreddit.new(limit=1000)

for submission in subreddit.top(limit=1):

print(submission.title)'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

Go ahead and run this script (with your own info in there) and you should see a print out of the latest submission to your chosen Subreddit. Something like this.

If this doesn’t work for you then you may have to allow this connection through your firewall. In my example I disabled Windows Firewall but your method will be different depending on what you use for a firewall.

Congratulations. Next step is to actually do something with this data.

Create API Access to Reddit

The last thing we need to do before connecting to Reddit is to create our API access. It’s a simple process, let’s get it done.

First of all, you need to create a Reddit account. Go to the following link to create your user. Note down your username and password, we’ll need those later.

https://www.reddit.com/register

Once you have a reddit username set up make sure you’re logged in and go to the apps page;

https://www.reddit.com/prefs/apps

We’re going to create our app, click the nice big button.

Choose a name for your application then select the radio button to create a script. For the redirect URL you’ll want to enter http://localhost:8080

Once you’ve done these three things, click ‘create app’. You’ll be taken to the screen with some useful information you’ll need.

From this screen you’ll need to note your ‘personal use script; as well as the ‘secret’.

At this point, you should have 5 pieces of information;

Reddit Username
Reddit Password
App Name
Personal Use Script (14 characters)
Secret (27 characters)

Once you’ve got all of these we can use these to connect to Reddit from SQL Server (with Python).

Let’s go see how we can do this.

Installing Pandas with SQL Server and Python

We’re going to use the Pandas plugin to format our data to make it easier to read. If you’ve already used pip to install PRAW then this is going to be very easy. Let’s get started.

First of all we need to find our Python scripts folder. My default on SQL 2019 is here.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019PYTHON\PYTHON_SERVICES\Scripts

Once here, open the command line to this location and run

pip install pandas

1	pip install pandas

This is going to do the heavy lifting for you. You’ll just sit there and watch it install.

Once the script has finished you’ll see a screen like this.

To double check this has worked you can run the following command within SQL Server

DECLARE @sql nvarchar(max) = 
N'#! usr/bin/env python3
import pandas as pd'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

DECLARE @sql nvarchar(max) =

N'#! usr/bin/env python3

import pandas as pd'

EXEC sp_execute_external_script @language = N'Python', @script = @sql

If you see an error then you’ll have to try and reinstall Pandas. If not, congratulations, you’ve done it!

Install PRAW with SQL Server and Python

So you’ve installed SQL Server with Python and you’ve also installed pip onto your SQL/Python Instance? Great! Let’s go ahead and get the PRAW package installed.

PRAW stands for Python Reddit API Wrapper and it can be used for creating bots as well as scraping data directly from Reddit. We’ll be using pip to install as it’s the recommended method of installing PRAW.

First of all, find your Python Services Scripts folder. On a standard installation of SQL Server 2019 it’s in this location

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019PYTHON\PYTHON_SERVICES\Scripts

Open the command prompt at this file location and run

pip install praw

1	pip install praw

It’s going to go ahead and download/install PRAW for you

To check PRAW is installed go ahead and run the following command

execute sp_execute_external_script @language = N'Python', @script = N'import praw'

1	execute sp_execute_external_script @language = N'Python', @script = N'import praw'

If you do not have PRAW installed you’ll get an error message similar to this. Notice the line ImportError: No module named ‘praw’.

Whereas with a successful install you won’t see the error message

Congrats, you’ve installed PRAW. Well done!

Installing pip on SQL Server with Python

One of the easiest ways of getting packages into your Python install is to install pip.

First thing you’ll want to do is to make sure that you already have SQL Server installed with Python (check my post about that here). Once you’ve done that we’re ready to begin.

Let’s go ahead and download the pip file. Go here https://bootstrap.pypa.io/get-pip.py and save the page to your local machine. I’m going to save it to my Python folder.

Because I’ve used SQL Server 2019 (named SQL2019PYTHON) the location is below. You will want to find your version of this folder. Go ahead and make sure your get-pip.py file is in here.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019PYTHON\PYTHON_SERVICES\

To test you’ve got the right folder, open the command prompt for this location and type ‘python’. You should see what version of python is installed on your machine.

Once you’re sure you’ve got the right folder you want to go ahead and run

python get-pip.py

1	python get-pip.py

This is going to go ahead and install pip on your server.

To confirm your pip has installed correctly you’ll want to go into the Scripts sub folder

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019PYTHON\PYTHON_SERVICES\Scripts

And in the command line run the following command

pip --version

1	pip --version

You should see the specific version you have running.

And that’s it. You’ve got pip installed, you can now use this to get the packages you want.

Installing Python with SQL Server

First things first, you’ll want to choose your version of SQL Server. Python is available on 2017 and greater. For this demo I’ll be using SQL Server 2019 Developer Edition (CTP 2.2).

With 2019 CTP2.2 they’ve increased the requirement of your OS too, in my example I had a spare VM with Windows Server 2012 laying around but I needed to update this to Server 2016. Check the relevant documentation for the version you’re using.

Once you’ve got your install file ready, go ahead and get started with the installation.

When selecting your installation type, you want to choose a custom install

Give it somewhere to save your media

After selecting Install it’s going to go ahead and download your initial installation files. This should only take a couple of minutes based upon your download speeds.

Once downloaded feel free to read through the documentation.

Finished? Great. Now we can go ahead and get SQL Installed. We’re going to go to the installation tab and choose a fresh install.

Because this instance is not going to be doing any sort of production workload we’re going to go ahead and choose Developer Edition. This edition is free and comes with all enterprise features but is only for development work (check your licence terms if needed)

Once you’ve read the T&C’s you can decide to allow SQL to use Microsoft Update to check for patches. This is totally your call.

The installation is going to check any inconsistencies against it’s internal rule list. You’ll have to fix any failures here before moving on. I’ve just got a warning about Windows Firewall but I’m going to leave that for now

The next step we’re going to choose here is to install the database engine but also choose to install Machine Learning Services with the Python option.

Note: you can install Machine Learning Server as a standalone feature. We do not want to do this.

I’ve left the instance directories as standard. Feel free to change these if you would prefer them elsewhere.

Your next choice will be leaving as the default instance or giving your instance a name. I’m going name mine and call it SQL2019Python.

Next, choose what service accounts you want to boot at startup. I like agent starting so I’ll set that to automatic.

Notice the little check box underneath that talks about Instant File Initialisation. You want this ticked. If you’re not sure what it does then just trust me, you’ll thank me later.

Next, you’ll choose your Authentication modes and give your SA account it’s password. I’d also suggest clicking the “Add Current User” button just in case.

Next, read and consent to the Python Install.

You’ll see a summary of your installation options. Have a read and make sure you’re happy with everything. Once you are then go ahead and click Install.

Download is going to depend on your download speeds again so go make a cuppa while you wait, you deserve it.

Eventually you’ll be greeted by a lovely screen that tells you that everything is installed.

The first way to check that your instance is up and running is to go to your Services app and check that you have your SQL instance running.

Congratulations, SQL and Python are installed!

To double check that Python is working you’ll want to open SQL Server Management Studio (SSMS) (if you don’t have it check this post out) and connect to your instance.

Once connected, run the following script;

sp_configure 'external scripts enabled'

1	sp_configure 'external scripts enabled'

By default, this option is not enabled. To allow it you’ll need to run the following command.

sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

1 2	sp_configure 'external scripts enabled', 1 RECONFIGURE WITH OVERRIDE

Now run the first script and you’ll see that it’s now enabled.

Now to confirm Python is working go ahead and run a very simple Python script;

execute sp_execute_external_script @language = N'Python', @script = N'print(10+11)'

1	execute sp_execute_external_script @language = N'Python', @script = N'print(10+11)'

You’ll see the output in the messages tab

Congratulations, you have SQL Server installed with Python and it works!