Spaces:
Sleeping
Sleeping
File size: 2,147 Bytes
9826f0b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
import sqlite3
from typing import List, Tuple
import json
# Constants
DB_PATH = "anime.db"
def get_recommendations(requested_genres: List[str], limit: int = 5) -> str:
# Establish Connection and Cursor
connection = sqlite3.connect(DB_PATH)
cursor = connection.cursor()
# Prepare placeholders for the SQL Query
placeholders = ', '.join(['?'] * len(requested_genres))
# Define SQL Query for the Weighted Ranking Logic
query = f"""
-- Project only Anime's name, score, and synopsis
SELECT
A.name,
A.score,
A.synopsis
-- From Anime Table
FROM Anime A
-- Join Anime Table with AnimeGenre on ids
JOIN AnimeGenre AG ON A.id = AG.anime_id
-- Join Genre Table with AnimeGenre on ids
JOIN Genre G ON AG.genre_id = G.id
-- Filter by only genre_name which belong in the list of requested genres
WHERE G.genre_name IN ({placeholders})
-- Group by Anime id
GROUP BY A.id
-- Primary Sort by Count of Matches in the requested_genres list
-- Secondary Sort by Anime's score
ORDER BY COUNT(G.id) DESC, A.score DESC
-- Return only the top 5 matches
LIMIT ?
"""
# Execute the Query with the requested genres and the limit
cursor.execute(query, requested_genres + [limit])
# Gather the results
results = cursor.fetchall()
# Close the Connection
connection.close()
# Compose a JSON String from the results and return it
return jsonify_recommendations(results)
def jsonify_recommendations(recommendations: List[Tuple[str, float, str]]) -> str:
# Process each anime recommendations into a list of dicts (for easy JSON conversion)
list_of_dicts = []
for anime in recommendations:
list_of_dicts.append({
'name': anime[0],
'score': anime[1],
'description': anime[2]
})
# JSONify and return the list of dicts
return json.dumps(list_of_dicts, indent=4)
# Driver Code
if __name__ == '__main__':
requested_genres = ["Action", "Drama"]
recommendations = get_recommendations(requested_genres)
print(recommendations) |