Creates the base statistical data table.

create_statagur_db.py

from sqlalchemy.engine import create_engine
from sqlalchemy.orm import sessionmaker, mapper
from sqlalchemy import MetaData, Table, Column, String
import json
import os
import requests
import gc

# to run, go to http://api.imgur.com/oauth2/addclient and
# request an anonymous user API key. then the script will
# be able to create the database.
gallery = {"gallery.json": "https://api.imgur.com/3/gallery.json"}
header = {"Authorization": "Client-ID YOUR-API-KEY"}


def Refresh():
    """
    Removes old gallery.json file.
    :return:
    """
    if os.path.isfile('gallery.json') is True:
        os.remove('gallery.json')
    else:
        pass


def Save():
    """
    Saves the response json to a file.
    :return:
    """
    Refresh()
    for key, value in gallery.items():
        response = requests.get(value, headers=header)
        with open(key, 'w') as f:
            json.dump(response.json(), f, indent=4)
    print(gc.garbage)

def CreateDatabase():
    """
    Creates the initial database, returns the global engine connection.
    """
    os.remove('test.db')
    engine = create_engine("sqlite:///test.db", echo=True)
    metadata = MetaData()
    gallery = Table("gallery", metadata,
        Column("id", String(100), primary_key=True),
        Column("comment_preview", String(100), nullable=True),
        Column("images_count", String(100), nullable=True),
        Column("section", String(100), nullable=True),
        Column("oc", String(100), nullable=True),
        Column("vote", String(100), nullable=True),
        Column("points", String(100), nullable=True),
        Column("privacy", String(100), nullable=True),
        Column("cover_height", String(100), nullable=True),
        Column("views", String(100), nullable=True),
        Column("cover", String(100), nullable=True),
        Column("account_id", String(100), nullable=True),
        Column("is_album", String(100), nullable=True),
        Column("favorite", String(100), nullable=True),
        Column("ups", String(100), nullable=True),
        Column("topic_id", String(100), nullable=True),
        Column("nsfw", String(100), nullable=True),
        Column("account_url", String(100), nullable=True),
        Column("title", String(255), nullable=True),
        Column("comment_count", String(100), nullable=True),
        Column("layout", String(100), nullable=True),
        Column("cover_width", String(100), nullable=True),
        Column("downs", String(100), nullable=True),
        Column("link", String(100), nullable=True),
        Column("datetime", String(100), nullable=True),
        Column("platform", String(100), nullable=True),
        Column("score", String(100), nullable=True),
        Column("description", String(255), nullable=True),
        Column("width", String(100), nullable=True),
        Column("type", String(100), nullable=True),
        Column("bandwidth", String(100), nullable=True),
        Column("height", String(100), nullable=True),
        Column("size", String(100), nullable=True),
        Column("animated", String(100), nullable=True),
        Column("mp4", String(100), nullable=True),
        Column("looping", String(100), nullable=True),
        Column("gifv", String(100), nullable=True),
        Column("webm", String(100), nullable=True),
        Column("topic", String(100), nullable=True))
    metadata.create_all(engine)

def LoadSession():
    """"""
    DbPath = 'test.db'
    global global_engine
    global_engine = create_engine('sqlite:///%s' % DbPath, echo=True)
    Session = sessionmaker(bind=global_engine)
    session = Session()
    return session

def LoadGallery():
    metadata = MetaData(0)
    gallery = Table('gallery', metadata, autoload_with=global_engine)
    return gallery


if __name__ == '__main__':
    Refresh()
    Save()
    CreateDatabase()
    with open('test.json', 'r') as data_file:
        data = json.load(data_file)
    session = LoadSession()
    gallery = LoadGallery()
    for post in data['data']:
        insert = gallery.insert().values(post)
        global_engine.execute(insert)
        session.commit()
    print(gc.garbage)