diff options
Diffstat (limited to 'database.py')
| -rwxr-xr-x | database.py | 75 |
1 files changed, 59 insertions, 16 deletions
diff --git a/database.py b/database.py index 78cf237..1877ab7 100755 --- a/database.py +++ b/database.py @@ -3,7 +3,9 @@ from dataclasses import dataclass from github import Github from lxml import html import configparser +import curiouscat import threading +import operator import datetime import requests import twython @@ -225,7 +227,9 @@ class Database: threading.Thread(target = update_cache).start() out = {} with self.__connection.cursor() as cursor: - cursor.execute("SELECT tweet_id, tweeted_at, tweet FROM diary WHERE replying_to IS NULL ORDER BY tweeted_at DESC;") + # cursor.execute("SELECT tweet_id, tweeted_at, tweet FROM diary WHERE replying_to IS NULL ORDER BY tweeted_at DESC;") + # attempt to ignore curiouscat automatic tweets by comparing with the q&a table + cursor.execute("SELECT tweet_id, tweeted_at, tweet FROM diary WHERE replying_to IS NULL AND tweet_id NOT IN (SELECT tweet_id FROM diary INNER JOIN qnas ON SUBSTRING(tweet, 1, 16) = SUBSTRING(question, 1, 16)) ORDER BY tweeted_at DESC;") for tweet_id, tweeted_at, tweet_text in cursor.fetchall(): # print(tweet_id, tweeted_at, tweet_text) out[tweeted_at] = [{ @@ -304,9 +308,45 @@ class Database: self.__connection.commit() + def get_curiouscat_username(self): + with self.__connection.cursor() as cursor: + cursor.execute("SELECT link FROM headerLinks WHERE name = 'curiouscat';") + return urlparse(cursor.fetchone()[0]).path.split("/")[1] + + def append_curiouscat_qnas(self, qnas): + with self.__connection.cursor() as cursor: + for qna in qnas: + cursor.execute("SELECT curiouscat_id FROM qnas WHERE curiouscat_id = %s;", (qna["id"], )) + if cursor.fetchone() is None: + + cursor.execute("INSERT INTO `qnas` VALUES (%s, %s, %s, %s, %s);", ( + qna["id"], qna["link"], qna["datetime"], qna["question"], qna["answer"] + )) + print("Appended question with timestamp %s" % datetime.datetime.fromtimestamp(qna["id"]).isoformat()) + + else: + print("Skipped question with timestamp %s" % datetime.datetime.fromtimestamp(qna["id"]).isoformat()) + self.__connection.commit() + + def get_biggest_curiouscat_timestamp(self): + with self.__connection.cursor() as cursor: + cursor.execute("SELECT MAX(`timestamp`) FROM `qnas`;") + return cursor.fetchone()[0] + + def get_curiouscat_qnas(self): + with self.__connection.cursor() as cursor: + cursor.execute("SELECT * FROM qnas;") + return sorted(cursor.fetchall(), key = operator.itemgetter(2), reverse = True) + def update_cache(): # print("updating cache...") with Database() as db: + db.append_curiouscat_qnas( + curiouscat.get_all_curiouscat_qnas_before( + db.get_curiouscat_username(), + db.get_biggest_curiouscat_timestamp() + ) + ) db.fetch_diary() db.update_twitter_cache(request_recent_tweets(10000)) # print("Done updating twitter cache...") @@ -339,23 +379,26 @@ def request_recent_commits(since = datetime.datetime.now() - datetime.timedelta( out = [] for repo in g.get_user().get_repos(): # print(repo.name, list(repo.get_branches())) - for commit in repo.get_commits(since = since): - out.append({ - "repo": repo.name, - "message": commit.commit.message, - "url": commit.html_url, - "datetime": commit.commit.author.date, - "stats": { - "additions": commit.stats.additions, - "deletions": commit.stats.deletions, - "total": commit.stats.total - } - }) + try: + for commit in repo.get_commits(since = since): + out.append({ + "repo": repo.name, + "message": commit.commit.message, + "url": commit.html_url, + "datetime": commit.commit.author.date, + "stats": { + "additions": commit.stats.additions, + "deletions": commit.stats.deletions, + "total": commit.stats.total + } + }) + except Exception as e: + print(e) + return sorted(out, key = lambda a: a["datetime"], reverse = True) if __name__ == "__main__": - import app + # print(request_recent_commits()) with Database() as db: - print(app.get_sidebar_img(db)) - # print(db.get_sidebar_images()) + print(db.get_curiouscat_qnas()) |