Using Reddit data with SQL Server and Python

  1. You’ve installed SQL Server with Python
  2. You’ve then installed pip
  3. Then you used pip to install PRAW
  4. You’ve also installed Pandas using pip
  5. You’ve created your Reddit API
  6. And you’ve got a working connection to Reddit

Now let’s actually gather this data and turn it into something useful inside SQL Server.

We’re going to build on our previous steps and create a Stored Procedure that we can simply execute from wherever we want and it will start populating data.

In previous steps we’ve only taken data from one subreddit but that’s a bit boring. Let’s make a list of subreddits that can be used by our SP.

We’re creating new table called py_SubredditList and inserted a list of our choosing. The list above are fairly good subreddits to get large blocks of text from. Feel free to change this list above to your favourite subreddits.

We’re going to grab one subreddit at a time and use it

Let’s start working on our Python code. First thing we’ll need is somewhere to push all of our data. Python uses things called dictionaries so we’ll make one called topics_dict;

Let’s dump our data into here

Dictionaries aren’t easy for us to interpret so let’s create a data frame using Pandas;

This data frame is what we’re going to return from our python block;

We’re going to output this data set into a temporary table;

Then we’re going to execute the python script and output the data into that temp table

And we’re going to increment our subreddit Hits by one so we’re not hitting the same subreddit all the time.

We’re going to create a permanent table to hold this data for later use

Finally we’ll put the data from our temp table into the permanent table

Putting together all of these elements you’ll come out with something like this;

FOR EVERYBODY WHO JUST WANTS THE FINISHED SP, IT’S HERE!

Installing this SP isn’t going to do anything until you execute it. Go on, live life on the edge and try it. Snoo agrees you should.

Then check out the py_RedditData table and you will have something like this;

Included you’ll be able to see your data lengths so you can filter out if you want. The DataType field is in there for you to experiment. We’re only pulling title and body from these submissions but you can also pull fields such as url (the post URL), score (int) and created (datetime). Check out the PRAW documentation for all available fields.

You can call this SP however you like. My preference is to call it once per minute from an agent job and leave it overnight so the Reddit API call limit doesn’t get hit but you can do it however you want.

That’s it. You’re done. Go make a cuppa.

WP Twitter Auto Publish Powered By : XYZScripts.com
%d bloggers like this: