bot.py

#!/usr/bin/python
import praw
import pdb
import re
import os
import sys
import MySQLdb
import json
import base64
import zlib

# Constants
sub_to_query = "homeimprovement"

# Open database connection
db = MySQLdb.connect("localhost","user","pass","database" )

# Create the Reddit instance
reddit = praw.Reddit('bot1')

# Get the contents of the wiki page
page = reddit.subreddit(sub_to_query).wiki['usernotes']

# Parse out the JSON
data = json.loads(page.content_md)

# Get the array of mods
mods = data["constants"]["users"]

# Get the array of warnings
warnings = data["constants"]["warnings"]

#sys.exit()

# Get just the blob
blob = data["blob"]

# Base64 decode the blob
decoded = base64.b64decode(blob)

# Decompress the decoded string
raw = zlib.decompress(decoded)

# Parse out the new JSON
parsed = json.loads(raw)
print(parsed)

# Prepare a cursor object using cursor() method
cursor = db.cursor()

# Parse out all the users from the main array
for item in parsed:
	username = item
	# Parse out all the notes from the user array
	for notes in parsed[item]["ns"]:
		note = notes["n"]
		link = notes["l"]
		# Confirm the link isn't null
		if not link:
			link = ''
		else:
			linka = link.split(",")
			link = linka[1]
			link = "https://www.reddit.com/" + link #Combine the reddit url with the ID
		time = notes["t"]
		mod = notes["m"]
		mod = mods[mod] #Get the right mod from the array
		warning = notes["w"]
		warning = warnings[warning] #Get the right warning from the array
		# Attempt to insert the data into the database
		try:
			cursor.execute("INSERT INTO database (username, note, link, time, moderator, warning) values (%s,%s,%s,%s,%s,%s)", (username, note, link, time, mod, warning))
			db.commit()
		except Exception as e:
			print("Rolling back")
			print(e)
			db.rollback()

#print(parsed)

# Disconnect from server
db.close()