python – Rich Benner's SQL Server Blog

Create API Access to Reddit

January 17, 2019January 10, 2019 Rich Benner Leave a comment

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

January 15, 2019January 10, 2019 Rich Benner Leave a comment

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 praw

1	pip install praw

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

January 10, 2019January 10, 2019 Rich Benner Leave a comment

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

January 8, 2019January 8, 2019 Rich Benner Leave a comment

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

January 3, 2019January 3, 2019 Rich Benner Leave a comment

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!