def count_likes(user: dict, from_date: date, to_date: date):
"""
Count the number of likes relating to the tweets written on a particular day within the
specified period. If the date is not in the database it adds 0 to the list 'likes_number'.
number_of_likes - tweet day & number of likes
"""
date_likes = []
likes_number = []
while from_date <= to_date:
cur.execute('''SELECT tweet_date, SUM(no_likes) FROM tweets
WHERE tweet_date = %s and user_id = %s GROUP BY tweet_date''', (from_date, user['id']))
number_of_likes = cur.fetchone()
if not number_of_likes:
date_likes.append(from_date)
likes_number.append(0)
else:
date_likes.append(number_of_likes[0])
likes_number.append(number_of_likes[1])
from_date = from_date + timedelta(days=1)
likes_info = {
'info_date_when': date_likes,
'info_likes_number': likes_number,
}
return likes_info
def get_followers_sum(user: dict, when: date = None):
"""
Get a record about followers at given day
"""
if not when:
when = date.today()
cur.execute("""SELECT * FROM followers_sum WHERE who = %s and checked_at = %s""", (user['id'], when))
return cur.fetchone()
def add_new_user(user: dict, do_check: bool = False) -> dict:
"""
Verify, if the user is in the database. If not, then it will do so.
"""
cur.execute('''SELECT * FROM twitter_user WHERE id = %s;''', (user['id'],))
row = cur.fetchone()
if not row:
cur.execute('''INSERT INTO twitter_user(id, screen_name, do_check) VALUES (%s, %s, %s);''',
(user['id'], user['screen_name'], do_check))
conn.commit()
row = {
'id': user['id'],
'screen_name': user['screen_name'],
'do_check': do_check,
}
return row
def get_user(screen_name: str = None, user_id: int = None) -> dict:
"""
Check if the user is in the database. If not, she inserts him to the 'twitter_user' table.
The do_check column has the value True for the followed person.
"""
if screen_name:
cur.execute('''SELECT * FROM twitter_user where screen_name = %s''', (screen_name,))
elif user_id:
cur.execute('''SELECT * FROM twitter_user where id = %s''', (user_id,))
else:
raise ValueError("missing user lookup identifier")
user = cur.fetchone()
if not user:
user = download_user_details(screen_name)
user = add_new_user(user)
return user
def count_tweets(user: dict, from_date: date, to_date: date):
"""
Count all tweets for the particular user and for each day within the specified period.
If the date is not in the database (it means there are no tweets this date) it adds 0 to the list 'tweets_per_day'.
"""
date_tweet = []
tweets_per_day = []
while from_date <= to_date:
cur.execute('''SELECT tweet_date, COUNT(*) FROM tweets
WHERE tweet_date = %s and user_id = %s GROUP BY tweet_date''', (from_date, user['id']))
number_of_tweets = cur.fetchone()
if not number_of_tweets:
date_tweet.append(from_date)
tweets_per_day.append(0)
else:
date_tweet.append(number_of_tweets[0])
tweets_per_day.append(number_of_tweets[1])
from_date = from_date + timedelta(days=1)
tweets_info = {
'info_date_when': date_tweet,
'info_tweets_per_day': tweets_per_day,
}
return tweets_info