def add_items_to_database(items, verbose):
db = DB()
# db.query("SET NAMES utf8;")
for item in items:
url_path = urlparse.urlparse(item.url).path[1:]
url_path_groups = url_path.split('/')
github_repo_path = '/'.join(url_path_groups[:2])
cmd = """
INSERT INTO hacker_news(id,
added_at,
submission_time,
title,
url,
github_repo_name)
VALUES (%d, '%s', '%s', '%s', '%s', '%s')
""" % (
item.item_id,
date_for_mysql(datetime.now()),
date_for_mysql(item.submission_time),
str_for_mysql(item.title),
str_for_mysql(item.url),
str_for_mysql(github_repo_path)
)
if verbose:
print cmd
# db.query(cmd)
try:
db.query(cmd)
except MySQLError as err:
print "ERROR:", err
db.conn.rollback()
else:
db.commit()
return True
def insert_user_repo_relationship(db, repo, user, date):
hash_object = hashlib.md5(user.login+repo.full_name)
query = """
INSERT INTO github_user_starred_repos(
record_id,
added_at,
user_id,
starred_repo_id,
starred_at)
VALUES ('{0}', '{1}', {2}, {3}, '{4}')
""".format(
hash_object.hexdigest(),
date_for_mysql(datetime.now()),
user.id,
repo.id,
date_for_mysql(date) if date else "NULL")
try:
# print "\t\tAdding user_repo_relationship to db"
# print query
db.query(query)
except MySQLError as err:
print "***ERROR***", err
return False
else:
db.commit()
return True
def add_repo_to_db(db, repo, from_hacker_news=False):
hn_flag = 1 if from_hacker_news else 0
query = """
INSERT INTO github_repos(
added_at,
id,
repo_name,
description,
author_id,
author_name,
created_at,
last_modified,
language,
stargazers_count,
forks_count,
from_hacker_news)
VALUES ('%s', %d, '%s', '%s', %d, '%s', '%s', '%s', '%s', %d, %d, %d)
""" % (
date_for_mysql(datetime.now()),
repo.id,
str_for_mysql(repo.full_name),
str_for_mysql(repo.description),
repo.owner.id,
str_for_mysql(repo.owner.name),
date_for_mysql(repo.created_at),
date_for_mysql(repo.updated_at),
str_for_mysql(repo.language),
repo.stargazers_count,
repo.forks_count,
hn_flag)
# print '\t' + query
try:
db.query(query)
except MySQLError as err:
# for primary key collisions
print "ERROR: ", err
else:
db.commit()
return True
def insert_user(db, user):
query = """
INSERT INTO github_users(
added_at,
id,
username,
full_name,
email,
location,
created_at,
public_repos_count,
private_repos_count,
followers_count,
contributions_count)
VALUES ('%s', %d, '%s', '%s', '%s', '%s', '%s', %d, %d, %d, %d)
""" % (
date_for_mysql(datetime.now()),
user.id,
str_for_mysql(user.login),
str_for_mysql(user.name),
str_for_mysql(user.email),
str_for_mysql(user.location),
date_for_mysql(user.created_at),
user.public_repos if user.public_repos else 0,
user.total_private_repos if user.total_private_repos else 0,
user.followers if user.followers else 0,
user.contributions if user.contributions else 0)
try:
# print "\t\tAdding user to db"
# print query
db.query(query)
except MySQLError as err:
print "***ERROR***:", err
return False
else:
db.commit()
return True
