SQL database query - return records which have all values from a list (Python)

Question

I've managed to query and get results for values from a list. But say i have the genres ["Action", "Comedy", "Romance"] in the list I am using to query. My code below returns all the records which are action, romance and comedy whereas I want it to return only the records that meet all three genres. How can I do this?

The database has a field called genre in which there are multiple genres for each record.

Code:

with sqlite3.connect("animeData.db") as  db: #anime database
            c = db.cursor()
            c.execute("select * from t where genre in %s" % repr(self.genreResults).replace('[','(').replace(']',')') ) #self.genreResults is the list containing the genres i want to query
        results = c.fetchall()
        print(results)
        db.commit()
        db.close()

Output:

Database:

It's returning all animes which have genres of action and adventure whereas I only want it to return animes which have both. Any suggestions?

Answer 1

If I understand correctly, 'like' clauses meet your requirements but you don't know how to convert list to 'like' clauses.

So the codes will be like below:

import sqlite3

with sqlite3.connect("animeData.db") as  db: #anime database
    c = db.cursor()
    #self.genreResults is the list containing the genres i want to query
    user_input = self.genreResults  # for example: ['Action','Drama']
    convert_to_like_conditions = ' and '.join(list(map(lambda item : "genre like '%{0}%'".format(item), user_input)))
    print(convert_to_like_conditions) #Output: genre like '%Action%' and genre like '%Drama%'
    c.execute("select * from t where %s" % convert_to_like_conditions ) 
    results = c.fetchall()
    print(results)
    db.commit()

You can refer to the documents on map, string join.

And you probably notice I removed the last line (db.close) of your codes, you can refer to Understanding Python's "with" statement.