import sqlite3

def create_table():
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("DELETE FROM code;")
    with open('code.txt', 'r') as f:
        c = 0
        txt = ""
        id = 1
        for l in f :
            txt += l
            if l == ";;;\n":
                c += 1
            if c == 3 :
                tab = txt.split(";;;\n")
                txt = ""
                c = 0
                cursor.execute("INSERT INTO code (id, titre, enonce, test)VALUES (?, ?, ?, ?)", (id, tab[0], tab[1], tab[2]))
                id += 1
        conn.commit()
        conn.close()

def return_title():
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("SELECT id, titre FROM code")
    rows = cursor.fetchall()
    conn.close()
    return [{"id" : v[0], "title" : v[1].replace("\n","")} for v in rows]
   


def return_exercise(id):
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM code WHERE id = ?", (id,))
    rows = cursor.fetchall()
    return rows[0]


create_table()