SQLite OperationalError: no such table: songdata despite table creation in Python script

Question

I'm encountering an issue with an SQLite database in my Python code. Despite what appears to be correct code for table creation, I'm getting the following error message: sqlite3.OperationalError: no such table: songdata

I'm building a music quiz program that interacts with an SQLite database to retrieve data for questions as my final cs50p project.

I have a class Question1 that inherits from a base class Question. Question1 is responsible for generating questions about songs and artists. The Question class has methods for retrieving data from an SQLite database and taking user input for answering questions.

The error occurs when trying to execute a query on the "songdata" table, which should have been created if it didn't exist.

con = sqlite3.connect("../music.db")
cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS songdata(artist TEXT, album TEXT, song TEXT, date INTEGER)")

This is the method during execution of which the error occurs:

def get_line_from_db(self):
    con = sqlite3.connect("music.db")
    cur = con.cursor()
    cur.execute('SELECT * FROM songdata ORDER BY RANDOM() LIMIT 1')
    # res = cur.execute("SELECT * FROM songdata ORDER BY RANDOM() LIMIT 1")
    results = cur.fetchall()
    con.commit()
    con.close()
    # format result data
    for result in results:
        self.key_data = list(result)
    artist, album, song, date = self.key_data
    return artist, album, song, date

The entire code is here: https://github.com/MichaelHorak/MyFirstProjects/blob/049f8dbc7dafe39fdf468a25871d5eea03b38bce/project_1.py

Any insights or suggestions on what might be causing this issue would be greatly appreciated. Thank you! I am getting desperate as I have been working on this for over a month and it just stopped working and I cannot figure it out.

I have tried playing around with the path but to no avail. I also tried to delete the db that is being created in the project folder but no improvements. I wrote different versions of this code from full procedural to full oop with no luck. I just want to be able to be able to retrieve data from the database that I am creating based on the itunes api.

I am posting here as a last resort. I did not find any solution for my problem in questions previously asked here nor in google search. I also watched a bunch of videos of people working with sqlite in python but I just cannot figure this out.

Answer 1

Your code refers to both "../music.db" and "music.db", two completely separate files.

I'd recommend making the file path a constant, e.g.

DATABASE_FILE = "./music.db"

around the top of your program, and using that in all of your sqlite3.connect() calls, or better yet:

def connect_database():
    return sqlite3.connect(DATABASE_FILE)

and use that instead of separate sqlite3.connect(DATABASE_FILE) calls.