4e6sLkhh

1import sqlite3
2import json
3import time
4from base64 import b64encode, b64decode
5
6# Create some various vars
7timeframes = ["2008-05"]
8sql_transaction = []
9
10
11def create_comment_table(timeframe, c):
12    '''
13    Create a table in the database to store all of the comments, if one doesn't exist
14    '''
15    sql = " CREATE TABLE IF NOT EXISTS '{}'(comment_id ".format(timeframe)
16    sql +="TEXT PRIMARY KEY, parent_id TEXT, comment TEXT, "
17    sql +=" subreddit TEXT, unix_time INT, score INT, parent_comment TEXT)"
18    c.execute(sql)
19
20
21
22def add_to_database(comment_data, c, table):
23    comment = json.loads(comment_data)
24    body = b64encode(comment["body"].encode("utf-8"))
25    created_utc = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(int(comment["created_utc"])))
26    score = comment["score"]
27    comment_id = comment["name"]
28    parent_id = comment["parent_id"]
29    sub = comment["subreddit"]
30    sql =  "INSERT INTO '{}' (comment_id, parent_id, comment, subreddit, unix_time, score) VALUES".format(table)
31    sql += "('{0}', '{1}', '{2}', '{3}', '{4}', {5});".format(comment_id, parent_id, str(body)[2:-2], sub, created_utc, score)
32
33    try:
34        c.execute(sql)
35        return True
36    except Exception as e:
37        print(e)
38        print(sql)
39        return False
40   
41    
42
43if __name__ == "__main__":
44    # Connect to the "database"
45    connection = sqlite3.connect("reddit_comments.sqlite3")
46    c = connection.cursor()
47    
48    for timeframe in timeframes:
49        create_comment_table(timeframe, c)
50        row_counter = 0
51        with open("RC_{}".format(timeframe)) as comments:
52            for row in comments:
53                if not add_to_database(row, c, timeframe):
54                    input()
55                row_counter += 1
56
57                if row_counter % 5000 == 0:
58                    print(row_counter)
59
60    connection.commit()
61    connection.close()
62    print("Done")