def get_users():
data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
data_connection.row_factory = sqlite3.Row
cursor = data_connection.cursor()
statement = 'SELECT * FROM users'
cursor.execute(statement)
data = cursor.fetchall()
return datautil.get_named_entities(data)
def get_snapshots(name):
data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
data_connection.row_factory = sqlite3.Row
cursor = data_connection.cursor()
statement = 'SELECT * FROM user_snapshots WHERE user_id = (SELECT id FROM users WHERE screen_name COLLATE NOCASE = ?)'
cursor.execute(statement, (name, ))
data = cursor.fetchall()
return datautil.get_named_entities(data)
def get_tweet_snapshots(id):
data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
data_connection.row_factory = sqlite3.Row
cursor = data_connection.cursor()
statement = f"""SELECT * FROM tweet_snapshots
WHERE id= ?
ORDER BY time_of_capture ASC"""
cursor.execute(statement, (id, ))
data = cursor.fetchall()
return datautil.get_named_entities(data)
def get_monthly_tweets(name):
data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
data_connection.row_factory = sqlite3.Row
cursor = data_connection.cursor()
statement = f"""SELECT count(*) total_tweets, strftime("%m-%Y", time_of_tweet) aggregatedate FROM (
SELECT time_of_tweet FROM tweets
WHERE author_id=(SELECT id FROM users WHERE screen_name COLLATE NOCASE = ?)
AND text NOT LIKE 'RT @%'
ORDER BY time_of_tweet DESC)
GROUP BY aggregatedate"""
cursor.execute(statement, (name, ))
data = cursor.fetchall()
return datautil.get_named_entities(data)
def get_like_volume(name):
data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
data_connection.row_factory = sqlite3.Row
cursor = data_connection.cursor()
statement = f"""SELECT sum(likes) likes, case cast (strftime('%w', local_time) as integer)
when 0 then 'Sunday'
when 1 then 'Monday'
when 2 then 'Tuesday'
when 3 then 'Wednesday'
when 4 then 'Thursday'
when 5 then 'Friday'
else 'Saturday' end as day_of_week, strftime('%H:00', local_time) tweet_hour FROM (
SELECT time_of_tweet, likes, datetime(time_of_tweet, 'localtime') local_time FROM tweets
WHERE author_id= (SELECT id FROM users WHERE screen_name COLLATE NOCASE = ?)
AND text NOT LIKE 'RT @%'
ORDER BY likes DESC)
GROUP BY day_of_week, tweet_hour"""
cursor.execute(statement, (name, ))
data = cursor.fetchall()
return datautil.get_named_entities(data)
