[FIXED] unsupported value error. trying to insert user data into database, while first selecting the key in one table

Issue

I'm working on my final project for CS50. The idea is to create something like the IMDB but for specialty coffee. I've managed to create a sql database and get the flask app running and let the user add information about a coffee to my database.

The problem is the following: I wand there to be a rating feature where the user can rate a particular coffee. That rating should be send to the ratings table in my database. I tried to have a relational database, where the coffee_id is the FOREIGN KEY in the coffee_ratings table.

Now that's where I'm still doing something wrong. From the terminal window I see that all the SELECT queries seem to work but somehow I still get "unsupported value {id:1} - one being the selected coffee_id from the coffees table here....

traceback (most recent call last):

        response = self.full_dispatch_request()
        rv = self.handle_user_exception(e)
            db.execute("INSERT INTO coffee_ratings (coffee_id, rating) VALUES (?, ?)", coffee_id, rating)
        return f(*args, **kwargs)

I'll inclue my python code here: (I commeted out additional ratings aroma, body ...that I ultimately would like to include as well, so I can fix the rating first)

`@app.route("/score", methods=["GET", "POST"])
def score():
        if request.method =="POST":`

            name = request.form.get("name")
            rating = request.form.get("rating")
            #aroma = request.form.get("aroma")
           # body = request.form.get("body")

            coffee_id = db.execute("SELECT id FROM coffees WHERE name LIKE ?", name)
              # Check if the coffee exists in the database
            if coffee_id is not None:
                coffee_id = coffee_id[0]  # Extracting the ID from the dictionary
            #aroma_id = db.execute("SELECT id FROM aromas WHERE aroma_name = ?", aroma)
           # body_id = db.execute("SELECT id FROM bodies WHERE body_name = ?", body)
           # db.execute("INSERT INTO coffee_ratings (coffee_id, aroma_id, body_id, rating) VALUES (?, ?, ?, ?)", coffee_id, aroma_id, body_id, rating)
                db.execute("INSERT INTO coffee_ratings (coffee_id, rating) VALUES (?, ?)", coffee_id, rating)
                return render_template("score.html")
            else:
                return f"Coffee '{name}' not found in the database."

        else:
            row = db.execute("SELECT * FROM coffee_ratings;")
            return render_template("score.html", score = row)`

here is my score.html:

`<form action="/score" method="post">
    <input id="name" autocomplete="off" autofocus name="name" placeholder="Name" type="text">
    <input id="rating" autocomplete="off" autofocus name="rating" placeholder="rating" type="number" min="1" max="10">
    <button type="submit">score</button>
</form>`

Here the .schema (only coffees and ratings table)

CREATE TABLE coffee_ratings ( id INTEGER PRIMARY KEY AUTOINCREMENT, coffee_id INTEGER, aroma_id INTEGER, taste_id INTEGER, body_id INTEGER, bitterness_id INTEGER, sourness_id INTEGER, rating INTEGER NOT NULL, votes INTEGER NOT NULL, FOREIGN KEY (coffee_id) REFERENCES coffees(id), FOREIGN KEY (aroma_id) REFERENCES aromas(id), FOREIGN KEY (taste_id) REFERENCES tastes(id), FOREIGN KEY (body_id) REFERENCES bodies(id), FOREIGN KEY (bitterness_id) REFERENCES bitterness(id) );

CREATE TABLE coffees ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, roaster TEXT, country TEXT, variety TEXT, farm TEXT ); CREATE TABLE aromas ( id INTEGER PRIMARY KEY AUTOINCREMENT, aroma_name TEXT NOT NULL ); Thank's in advance for your help.

Cheers Nico

Solution

cs50 execute returns a list of dictionaries. This coffee_id = coffee_id[0] is the first row returned by execute, which is {'id': 1} as per the error message. Insert query needs the value of the key id from the first row.

Answered By - DinoCoderSaurus