logger.py

from datetime import datetime
import sqlite3
import praw

reddit = praw.Reddit(
    client_id='client_id',
    client_secret='client_secret',
    password='password',
    user_agent='user_agent',
    username='username'
)

conn = sqlite3.connect('civworldpowers.db')
c = conn.cursor()


def build_db():
    """Creates a new table the first time the script is ran on a new machine"""
    c.execute('''CREATE TABLE IF NOT EXISTS posts(
    date TEXT, time TEXT, user TEXT, flair TEXT, title TEXT, id TEXT, score TEXT, updated TEXT
    )''')
    conn.commit()


def store(_data):
    """Save the data to the database"""

    while True:
        try:
            c.execute('''INSERT INTO posts(date, time, user,
            flair, title, id, score, updated) VALUES (?,?,?,?,?,?,?,?)''', _data)
        except sqlite3.OperationalError:
            continue
        else:
            conn.commit()
            break


def get_viewed():
    """Extracts all historic post id's for deduplication"""
    c.execute('''SELECT id FROM posts''')
    return [post_id[0] for post_id in c.fetchall()]


def translate_timestamp(timestamp):
    """Translate a Unix timestamp into a tuple of date and time"""
    string = datetime.fromtimestamp(int(timestamp)).strftime("%Y%m%d %H:%M:%S")
    return string.split()


def get_data(sub):
    """Gather desired data into a tuple for storage"""
    flair = sub.link_flair_css_class
    date, _time = translate_timestamp(sub.created_utc)
    try:
        name = sub.author.name
    except:
        name = 'deleted'
    return date, _time, name, flair, sub.title, sub.id, sub.score, 'False'


def main():
    """Monitor the subreddit and store submissions as they come
    All posts will initially have updated set to False.
    You must update the flair and score before calculating scores.
    Flairs and scores can only be updated once so you should do it
    immediately before calculating the results.
    """

    viewed = get_viewed()
    subreddit = reddit.subreddit('CivWorldPowers')
    submission_stream = subreddit.stream.submissions()
    for sub in submission_stream:
        if sub.id in viewed:
            continue
        data = get_data(sub)
        store(data)
        viewed.append(sub.id)


if __name__ == '__main__':
    build_db()
    main()