def insert_author(author_id):
"""
Insert single new author to table.
Args:
:author_id: string with id of author
"""
conn = get_db_connection()
cur = conn.cursor()
sql = """
INSERT INTO author (author_id)
VALUES(%s);
"""
try:
try:
cur.execute(sql, (author_id, ))
conn.commit()
# Exception for duplicate record
except psycopg2.IntegrityError:
conn.rollback()
except Exception as e:
print(f"Couldn't insert new author. Error: {e}")
conn.rollback()
cur.close()
conn.close()
def insert_ticker_mention(ticker, comment_id):
"""
Insert single new comment to table.
Provide following datapoints of comment:
Args:
:ticker: string with ticker
:comment_id: string with comment_id
"""
conn = get_db_connection()
cur = conn.cursor()
sql = """
INSERT INTO ticker_mention (ticker_id, comment_id)
VALUES(%s, %s);
"""
try:
try:
cur.execute(sql, (
ticker,
comment_id,
))
conn.commit()
# Exception for duplicate record
except psycopg2.IntegrityError:
conn.rollback()
except Exception as e:
print(f"Couldn't insert new comment. Error: {e}")
conn.rollback()
cur.close()
conn.close()
def insert_source(source):
"""
Insert single new source to table.
Args:
:source: string with name of source
"""
conn = get_db_connection()
cur = conn.cursor()
sql = """
INSERT INTO source (source)
VALUES(%s);
"""
try:
try:
cur.execute(sql, (source, ))
conn.commit()
# Exception for duplicate record
except psycopg2.IntegrityError:
conn.rollback()
except Exception as e:
print(f"Couldn't insert new source. Error: {e}")
conn.rollback()
cur.close()
conn.close()
def get_dash_data():
"""Get all data for dash app."""
conn = get_db_connection()
cur = conn.cursor()
sql = """
SELECT
ticker_control.name,
ticker_mentions.ticker,
count(ticker_mentions.mention_id) as count,
sum(ticker_mentions.score) as score,
datetime(ticker_mentions.timestamp, 'unixepoch'),
strftime('%Y-%m-%d %H',
datetime(ticker_mentions.timestamp, 'unixepoch'))
FROM
ticker_mentions
INNER JOIN
ticker_control on ticker_control.ticker = ticker_mentions.ticker
GROUP BY
ticker_mentions.ticker,
strftime('%Y-%m-%d %H',
datetime(ticker_mentions.timestamp, 'unixepoch'))
ORDER BY
count(ticker_mentions.mention_id) DESC,
ticker_mentions.ticker;
"""
cur.execute(sql)
return cur.fetchall()
def get_all_submission_ids():
"""Get all unique submission ids."""
conn = get_db_connection()
cur = conn.cursor()
sql = """
SELECT submission_id from submission;
"""
cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()
return [id[0] for id in result]
def count_current_tickers():
"""Count number of ticker in table."""
conn = get_db_connection()
cur = conn.cursor()
sql = """
SELECT count(ticker_id) from ticker;
"""
cur.execute(sql)
result = cur.fetchone()[0]
cur.close()
conn.close()
return result
def get_all_control_tickers():
"""Get list of all valid tickers."""
conn = get_db_connection()
cur = conn.cursor()
sql = """
SELECT ticker_id from ticker;
"""
cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()
return [t[0] for t in result]
def get_most_recent_submission():
"""Get most recent submission id."""
conn = get_db_connection()
cur = conn.cursor()
sql = """
SELECT submission_id
FROM submission
WHERE timestamp = (SELECT max(timestamp) FROM submission);
"""
cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()
return [id[0] for id in result]
def get_dates_for_top10_mentioned_tickers():
"""Get 10top mentioned tickers for each available date."""
conn = get_db_connection()
cur = conn.cursor()
sql = """
SELECT
ticker_mentions.ticker,
date(ticker_mentions.timestamp, 'unixepoch')
FROM
ticker_mentions
GROUP BY
ticker_mentions.ticker
ORDER BY
count(ticker_mentions.mention_id) DESC
LIMIT 10;
"""
cur.execute(sql)
return cur.fetchall()
def insert_submission(submission_id, timestamp, score, nr_comments, author_id,
source):
"""
Insert single new submission to table.
Provide following datapoints of submission:
Args:
:submission_id: string with id
:timestamp: timestamp UTC
:score: int with score
:nr_comments: int with nr_comments
:author_id: string with author_id
:source: string with source
"""
conn = get_db_connection()
cur = conn.cursor()
sql = """
INSERT INTO submission (submission_id, timestamp, score,
nr_comments, author_id, source_id)
VALUES(%s, %s, %s, %s, %s,
(SELECT source_id from source where source = %s));
"""
try:
try:
cur.execute(sql, (
submission_id,
timestamp,
score,
nr_comments,
author_id,
source,
))
conn.commit()
# Exception for duplicate record
except psycopg2.IntegrityError:
conn.rollback()
except Exception as e:
print(f"Couldn't insert new submission. Error: {e}")
conn.rollback()
cur.close()
conn.close()
def insert_tickers(ticker_list):
"""
Insert multiple new tickers to table.
Args:
:ticker_list: [(ticker_id, company_name, )]
"""
conn = get_db_connection()
cur = conn.cursor()
sql = """
INSERT INTO ticker (ticker_id, company_name)
VALUES(%s, %s);
"""
try:
cur.executemany(sql, ticker_list)
conn.commit()
except Exception as e:
print(f"Couldn't insert control tickers. Error: {e}")
conn.rollback()
cur.close()
conn.close()
def insert_comment(comment_id, timestamp, score, submission_id, author_id):
"""
Insert single new comment to table.
Provide following datapoints of comment:
Args:
:comment_id: string with comment_id
:timestamp: timestamp UTC
:score: int with score
:submission_id: string with submission_id
:author_id: string with author_id
"""
conn = get_db_connection()
cur = conn.cursor()
sql = """
INSERT INTO comment (comment_id, timestamp, score, submission_id, author_id)
VALUES(%s, %s, %s, %s, %s);
"""
try:
try:
cur.execute(sql, (
comment_id,
timestamp,
score,
submission_id,
author_id,
))
conn.commit()
# Exception for duplicate record
except psycopg2.IntegrityError:
conn.rollback()
except Exception as e:
print(f"Couldn't insert new comment. Error: {e}")
conn.rollback()
cur.close()
conn.close()