gUKu42RP

1#!/usr/bin/python
2import os
3import json
4import time
5import sqlite3 as lite
6from pprint import pprint as pp
7
8start_time = time.time()
9
10
11
12DATABASE = os.path.curdir + '/sqlite3.db'
13
14DATAFILES = [
15    '/home/nils/git/LNU-2DV513-Databaseteori/assignment-2/RC_2007-10',
16    '/home/nils/git/LNU-2DV513-Databaseteori/assignment-2/RC_2011-07',
17    '/home/nils/git/LNU-2DV513-Databaseteori/assignment-2/RC_2012-12',
18]
19
20
21def database_file_exists() -> bool:
22    """Does the database file exists?"""
23    if os.path.exists(DATABASE):
24        return True
25    return False
26
27
28def database_is_empty() -> bool:
29    if os.path.getsize(DATABASE) == 0:
30        return True
31    return False
32
33
34def create_database():
35    if database_file_exists() is False:
36        print('Creating database file.')
37        con = lite.connect(DATABASE)
38        con.close()
39    if database_is_empty():
40        print('Creating database table.')
41        con = lite.connect(DATABASE)
42        cur = con.cursor()
43        query = '''CREATE TABLE Comment
44                (id INTEGER PRIMARY KEY NOT NULL UNIQUE,
45                parent_id STRING NOT NULL,
46                link_id STRING NOT NULL,
47                name STRING NOT NULL,
48                author STRING NOT NULL,
49                body TEXT NOT NULL,
50                subreddit_id STRING NOT NULL,
51                subreddit STRING NOT NULL,
52                score INTEGER NOT NULL,
53                created_utc INTEGER NOT NULL)'''
54        cur.execute(query)
55
56
57create_database()
58
59
60def fill_database():
61    print('Importing data to database.')
62    con = lite.connect(DATABASE)
63    for file in DATAFILES:
64        with open(file) as f:
65            for line in f:
66                cur = con.cursor()
67                comment = json.loads(line)
68                # pp(comment)
69                # import pdb; pdb.set_trace()
70                cur.execute('INSERT INTO Comment (id, parent_id, link_id, name, author, body, subreddit_id, subreddit, score, created_utc) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', (
71                    int(comment['id'], 36),
72                    comment['parent_id'],
73                    comment['link_id'],
74                    comment['name'],
75                    comment['author'],
76                    comment['body'],
77                    comment['subreddit_id'],
78                    comment['subreddit'],
79                    comment['score'],
80                    comment['created_utc']))
81    con.commit()
82    con.close()
83    print('Importing done.')
84
85fill_database()
86
87print('Elapsed time calculated from python: {}'.format(time.time() - start_time))