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]
