def basic_stats(fake_input_content):
if fake_input_content:
pass
conn = sqlite_connection(get_db_path())
unique_vids = execute_query(
conn, "SELECT count(*) FROM videos "
"WHERE NOT videos.id = 'unknown';")[0][0]
total_opened = execute_query(conn, "SELECT count(*) FROM "
"videos_timestamps;")[0][0]
total_channels = execute_query(conn,
"SELECT count(*) FROM channels;")[0][0]
total_tags = execute_query(conn, "SELECT count(*) FROM videos_tags;")[0][0]
unique_tags = execute_query(conn, "SELECT count(*) FROM tags;")[0][0]
conn.close()
mark_down = dcc.Markdown(
dedent(f'''
**{unique_vids}** unique videos
(and some with no identifying info available), opened/watched
**{total_opened}** times
**{total_tags}** tags across all videos (**{unique_tags}** unique),
an average of **{round(total_tags / unique_vids, 1)}** per video
**{total_channels}** channels
'''))
return mark_down
def top_watched_tracking_graph(rows: list, query_type: str):
entries: pd.DataFrame = getattr(tracking.data_keeper, query_type.lower())
if query_type == 'Videos':
ind = 2
else:
ind = 0
entries: list = entries.iloc[rows, ind].values
conn = sqlite_connection(get_db_path(), types=True)
data = []
df = tracking.selected_history_charts_mass(conn, entries, query_type)
for value in entries:
sub_df = df[df[df.columns[0]] == value]
if query_type == 'Videos':
name = sub_df.iloc[0, 1]
else:
name = value
if len(name) > 20:
name = name[:20] + '...'
sub_df = sub_df.groupby(pd.Grouper(freq='MS'))
sub_df = sub_df.size().reset_index(name='Views')
data.append(
go.Scatter(x=sub_df.Timestamp,
y=sub_df.Views,
mode='lines',
name=name))
conn.close()
layout = go.Layout(yaxis=go.layout.YAxis(fixedrange=True),
margin=dict.fromkeys(list('ltrb'), 30),
hovermode='closest')
return {'data': data, 'layout': layout}
def v_scatter_summary(hover_data: dict):
if hover_data:
point_of_interest = hover_data['points'][0].get('customdata', None)
else:
point_of_interest = None
if point_of_interest:
conn = sqlite_connection(get_db_path())
'''Views, Ratio, Likes, Dislikes, Title, Channel, Upload date'''
query = '''SELECT v.title, c.title, v.published_at, v.view_count,
v.like_count, v.dislike_count, (v.like_count * 1.0 / dislike_count),
v.status, v.last_updated FROM
videos v JOIN channels c ON v.channel_id = c.id
WHERE v.id = ?'''
r = execute_query(conn, query, (point_of_interest, ))[0]
conn.close()
views = add_commas_to_num(r[3])
likes = add_commas_to_num(r[4]) if r[4] else None
dislikes = add_commas_to_num(r[5]) if r[5] else None
status = f'**Status:** {r[7]}'
if r[7] == 'active':
status += f', last checked on {r[8][:10]}'
like_dislike_ratio = round(r[6], 2) if r[6] else 'n/a'
return dcc.Markdown(
dedent(f'''**Title:** {r[0]}
**Channel:** {r[1]}
**Published:** {r[2][:10]}
**Views:** {views}
**Likes:** {likes}
**Dislikes:** {dislikes}
**Ratio:** {like_dislike_ratio}
**ID:** {point_of_interest}
{status}
'''))
def update_history_chart(value):
db_path = join(get_project_dir_path_from_cookie(), DB_NAME)
decl_types = sqlite3.PARSE_DECLTYPES
decl_colnames = sqlite3.PARSE_COLNAMES
conn = sqlite_connection(db_path, detect_types=decl_types | decl_colnames)
data = history_chart.retrieve_watch_data(conn, date_periods_vals[value])
conn.close()
return construct_history_chart(data), 'Videos opened/watched'
def update_db(project_path: str, cutoff: int, logging_verbosity: int):
import sqlite3
progress.clear()
DBProcessState.percent = '0.0'
DBProcessState.stage = 'Updating...'
add_sse_event(DBProcessState.stage, 'stage')
db_path = join(project_path, DB_NAME)
conn = sqlite_connection(db_path)
front_end_data = {
'updated': 0,
'failed_api_requests': 0,
'newly_inactive': 0,
'records_in_db': execute_query(conn,
'SELECT count(*) from videos')[0][0]
}
try:
api_auth = youtube.get_api_auth(
load_file(join(project_path, 'api_key')).strip())
if DBProcessState.exit_thread_check():
return
for record in write_to_sql.update_videos(conn, api_auth, cutoff,
logging_verbosity):
if DBProcessState.exit_thread_check():
break
DBProcessState.percent = str(record[0])
add_sse_event(f'{DBProcessState.percent} {record[1]}')
front_end_data['updated'] = record[2]
front_end_data['newly_inactive'] = record[3]
front_end_data['newly_active'] = record[4]
front_end_data['deleted'] = record[5]
_show_front_end_data(front_end_data, conn)
except youtube.ApiKeyError:
add_sse_event(f'{flash_err} Missing or invalid API key', 'errors')
raise
except youtube.ApiQuotaError:
add_sse_event(
f'API quota/rate limit exceeded, see '
f'<a href="https://console.developers.google.com/apis/'
f'api/youtube.googleapis.com/overview" target="_blank">'
f'here</a>', 'errors')
raise
except (sqlite3.OperationalError, sqlite3.DatabaseError) as e:
add_sse_event(f'{flash_err} Fatal database error - {e!r}', 'errors')
raise
except FileNotFoundError:
add_sse_event(f'{flash_err} Invalid database path', 'errors')
raise
conn.close()
def update_v_scatter(x_axis_type: str, y_axis_type: str, views: list,
num_of_records: int):
if not x_axis_type or not y_axis_type:
return None
num_of_records = v_scatter_slider_recs_nums[num_of_records]
min_views = v_scatter_view_range_nums[views[0]]
max_views = v_scatter_view_range_nums[views[1]]
db_path = join(get_project_dir_path_from_cookie(), DB_NAME)
conn = sqlite_connection(db_path)
data = videos_scatter_graph.get_data(conn,
x_axis_type,
y_axis_type,
min_views=min_views,
max_views=max_views,
number_of_records=num_of_records)
conn.close()
if x_axis_type in ['LikeRatioAsc', 'LikeRatioDesc']:
x_axis_type = 'Ratio'
return construct_v_scatter(data, x_axis_type, y_axis_type)
def history_chart_date_summary(data, date_period):
if data:
date = data['points'][0]['x']
if data['points'][0]['y'] == 0:
return f'No videos for the period of {date}'
if date_period == 0:
date = date[:4]
elif date_period == 1:
date = date[:7]
elif date_period == 2:
date = date[:10]
else:
date = date[:13]
db_path = join(get_project_dir_path_from_cookie(), DB_NAME)
conn = sqlite_connection(db_path)
summary_tables = history_chart.make_summary_tables_for_time_period(
conn, date)
conn.close()
return [*summary_tables]
else:
return history_date_period_summary_msg
def top_watched_tracking(tracking_type):
conn = sqlite_connection(get_db_path())
results = tracking.get_top_results(conn, tracking_type)
conn.close()
return results
def populate_db(takeout_path: str, project_path: str, logging_verbosity: int):
if DBProcessState.exit_thread_check():
return
progress.clear()
DBProcessState.percent = '0'
DBProcessState.stage = 'Processing watch-history.html file(s)...'
add_sse_event(DBProcessState.stage, 'stage')
records = {}
try:
for f in get_all_records(takeout_path, project_path):
if DBProcessState.exit_thread_check():
return
if isinstance(f, tuple):
DBProcessState.percent = f'{f[0]} {f[1]}'
add_sse_event(DBProcessState.percent, 'takeout_progress')
else:
try:
records = f['videos']
if len(records) == 1: # 1 because of the empty unknown rec
add_sse_event(
'No records found in the provided '
'watch-history.html file(s). '
'Something is very wrong.', 'errors')
return
except KeyError:
add_sse_event(
f'No watch-history.html files found in '
f'{takeout_path!r}', 'errors')
return
failed_entries = f['failed_entries']
if failed_entries:
add_sse_event(
f'Couldn\'t parse {len(failed_entries)} '
f'entries; dumped to parse_fails.json '
f'in project directory', 'warnings')
failed_files = f['failed_files']
if failed_files:
add_sse_event(
'The following files could not be '
'processed:', 'warnings')
for ff in failed_files:
add_sse_event(ff, 'warnings')
total_ts = f['total_timestamps']
total_v = f['total_videos']
add_sse_event(
f'Videos / timestamps found: '
f'{total_v} / {total_ts}', 'info')
except FileNotFoundError:
add_sse_event(
f'Invalid/non-existent path for watch-history.html files',
'errors')
raise
if DBProcessState.exit_thread_check():
return
db_path = join(project_path, DB_NAME)
conn = sqlite_connection(db_path, types=True)
front_end_data = {'updated': 0}
try:
api_auth = youtube.get_api_auth(
load_file(join(project_path, 'api_key')).strip())
write_to_sql.setup_tables(conn, api_auth)
records_at_start = execute_query(conn,
'SELECT count(*) from videos')[0][0]
if not records_at_start:
front_end_data['at_start'] = 0
else:
front_end_data['at_start'] = records_at_start
DBProcessState.percent = '0.0'
add_sse_event(f'{DBProcessState.percent} 1')
DBProcessState.stage = ('Inserting video records/timestamps from '
'Takeout...')
add_sse_event(DBProcessState.stage, 'stage')
for record in write_to_sql.insert_videos(conn, records, api_auth,
logging_verbosity):
if DBProcessState.exit_thread_check():
break
DBProcessState.percent = str(record[0])
add_sse_event(f'{DBProcessState.percent} {record[1]}')
front_end_data['updated'] = record[2]
_show_front_end_data(front_end_data, conn)
if DBProcessState.stage:
add_sse_event(event='stop')
add_sse_event(json.dumps(front_end_data), 'stats')
conn.close()
except youtube.ApiKeyError:
add_sse_event(f'Missing or invalid API key', 'errors')
raise
except youtube.ApiQuotaError:
add_sse_event(
f'API quota/rate limit exceeded, see '
f'<a href="https://console.developers.google.com/apis/'
f'api/youtube.googleapis.com/overview" target="_blank">'
f'here</a>', 'errors')
raise
except (sqlite3.OperationalError, sqlite3.DatabaseError) as e:
add_sse_event(f'Fatal database error - {e!r}', 'errors')
raise
except FileNotFoundError:
add_sse_event(f'Invalid database path', 'errors')
raise
conn.close()