forked from ankushagrawal94/TheHackerNewsBump
-
Notifications
You must be signed in to change notification settings - Fork 0
/
hnTableTohnTableMax.py
61 lines (51 loc) · 1.54 KB
/
hnTableTohnTableMax.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
#This file gets the most relevant HN event for each repo as determined by the number of upvotes recieved
import MySQLdb
import time
conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "password")
cursor = conn.cursor()
#cursor.execute ("DROP DATABASE IF EXISTS githubDB")
##cursor.execute ("CREATE DATABASE githubDB")
cursor.execute ("USE githubDB")
cursor.execute("DROP TABLE IF EXISTS hn_event_max")
cursor.execute ("""
CREATE TABLE hn_event_max
(
repo_name VARCHAR(255),
stars INT(6),
hn_points INT(6),
event_time DATE
)
""")
conn.commit()
print "succesfully created hn_event_max"
cursor.close()
conn.close()
try:
print "started"
startTime = time.time()
db = MySQLdb.connect(host="localhost", # your host, usually localhost
user="root", # your username
passwd="password", # your password
db="githubDB") # name of the data base
cur = db.cursor()
cur.execute(""" INSERT INTO hn_event_max
SELECT y.repo_name, y.stars, y.hn_points, y.event_time
FROM hn_event_table_two y
INNER JOIN (SELECT repo_name, max(hn_points) as max_points
FROM hn_event_table_two x
GROUP BY repo_name) x
ON y.repo_name = x.repo_name
AND x.max_points = y.hn_points
ORDER BY repo_name ASC""")
db.commit()
stopTime = time.time()
elapsedTime = stopTime - startTime
print elapsedTime
# print all the first cell of all the rows
for row in cur.fetchall() :
print str(row[0]) + '\t\t\t\t' + str(row[1])
cur.close()
db.close()
except Exception, e:
print e
raise e