def prepare_history_db(start_week, end_week): conn = sqlite3.connect('data/week_%s/listened_history_week_%s_%s.db' % (end_week, start_week, end_week)) cursor = conn.cursor() cursor.executescript(""" create table if not exists history ( user, track, artist, streamable, album, url, loved, datetime ); create table if not exists meta_info ( name, left_time, right_time, target ); """) cursor.execute("select count(*) from meta_info;") count1 = cursor.fetchone()[0] cursor.execute("select count(*) from history;") count2 = cursor.fetchone()[0] # no record, it means run from scratch if count1 == 0 and count2 == 0: left_time = timestamp_of_nth_week(start_week) right_time = timestamp_of_nth_week(end_week) targets = get_targets() for target_name in targets: cursor.execute("insert into meta_info values (?, ?, ?, ?)", (target_name, left_time, right_time, 1)) conn.commit() History.conn = conn History.cursor = cursor return (cursor, conn)
def initialize_friend_listeners_table(week, db): targets_id = build_targets_hash().values() tracks_id = build_tracks_hash().values() for target_id in targets_id: for track_id in tracks_id: db.execute("insert into friend_listeners values (?, ?, ?)", (target_id, track_id, 0)) friends = build_revert_relation().keys() start_time = timestamp_of_nth_week(week - 1) for friend in friends: db.execute("insert into meta_info values (?, ?)", (friend, start_time)) db.commit()
def load_progress(db, week): end_time = timestamp_of_nth_week(week) cur = db.execute("select friend, timestamp, %d from meta_info;" % end_time ) return [row for row in cur]