from crawler import Location, Meal from collections import namedtuple import sqlite3 Schedule = namedtuple('Schedule', 'time, day_week, location, meal, user_id, created_at') connection = sqlite3.connect("db", isolation_level=None, check_same_thread=False) # Create tables with connection: connection.execute(''' CREATE TABLE IF NOT EXISTS schedule ( time TEXT NOT NULL, day_week INT NOT NULL, location TEXT NOT NULL, meal TEXT NOT NULL, user_id INT NOT NULL, created_at DATETIME NOT NULL ) ''') def get_schedules_for_user(user_id): cur = connection.execute(''' SELECT time, day_week, location, meal, user_id, created_at FROM schedule WHERE user_id = ? ''', (user_id,)) rows = cur.fetchall() return [Schedule( row[0], row[1], Location[row[2]], Meal[row[3]], row[4], row[5] ) for row in rows] def insert_schedule(schedule): connection.execute(''' INSERT INTO schedule (time, day_week, location, meal, user_id, created_at) VALUES (?, ?, ?, ?, ?, ?) ''', ( schedule.time, schedule.day_week, schedule.location.name, schedule.meal.name, schedule.user_id, schedule.created_at )) def get_schedules_matching_time(time): cur = connection.execute(''' SELECT time, day_week, location, meal, user_id, created_at FROM schedule WHERE time = ? and day_week = ? ''', (time.strftime('%H:%M'), time.weekday())) rows = cur.fetchall() return [Schedule( row[0], row[1], Location[row[2]], Meal[row[3]], row[4], row[5] ) for row in rows] def delete_all_schedules_from_user(user_id): connection.execute(''' DELETE FROM schedule WHERE user_id = ? ''', (user_id,))