Python SQLite printing from a table where ID is equal set variable-CodePudding

This code gets the category from users input in film_list table and gets IDs of all the movies with that category:

kategorija=input("Enter the category: ")
c.execute("SELECT DISTINCT FID FROM film_list WHERE category=?", (kategorija,))
filmid = c.fetchall()
print(filmid)

I'm trying to get a name and the release year of the film with the ID that we got in a previous code fragment.

result = []
for a in filmid:
    c.execute("SELECT title,release_year FROM film WHERE film_id = 'a'")
    result.append(c.fetchone())
    print(result)

When I enter any number, for example 1, it returns what I need, so I suppose there's something wrong in the declaration of film_id, but I don't know how I can solve this.

Full code:

import sqlite3

#Connectin to DB
conn = sqlite3.connect('sakila.db')
c = conn.cursor()

#Checking if the connection to the DB is successful
if (conn):
    print("Connection successful")
else:
    print ("Connection unsuccessful")

kategorija=input("Enter the category: ")
c.execute("SELECT DISTINCT FID FROM film_list WHERE category=?", (kategorija,))
filmid = c.fetchall()
print(filmid)

result = []
for a in filmid:
    c.execute("SELECT title,release_year FROM film WHERE film_id = 'a'")
    result.append(c.fetchone())
    print(result)

CodePudding user response：

You may use the following single query:

SELECT f.title, f.release_year
FROM film f
INNER JOIN film_list fl ON fl.fid = f.film_id
WHERE fl.category = ?

Your updated Python code:

sql = '''SELECT f.title, f.release_year
FROM film f
INNER JOIN film_list fl ON fl.fid = f.film_id
WHERE fl.category = ?'''

kategorija = input("Enter the category: ")
result = []
c.execute(sql, (kategorija,))
result.append(c.fetchAll())
print(result)