NrhTcQmL

1import sqlite3
2
3def create_database():
4
5    with sqlite3.connect('all_playlists.db') as conn:
6        c = conn.cursor()
7        c.execute("DROP TABLE playlists") # Gets messy if you re-run the code otherwise
8
9        c.execute("""
10                  CREATE TABLE IF NOT EXISTS playlists(
11                      playlist_name TEXT,
12                      username TEXT,
13                      song_title TEXT)
14                  """)
15
16
17def add_playlist(playlist_name, username, songs):
18    with sqlite3.connect('all_playlists.db') as conn:
19        c = conn.cursor()
20        for song in songs:
21            c.execute("""
22                      INSERT INTO playlists VALUES (?, ?, ?)
23                      """, (playlist_name, username, song))
24
25
26def get_all_playlists(username):
27    with sqlite3.connect('all_playlists.db') as conn:
28        c = conn.cursor()
29        c.execute("""
30                  SELECT DISTINCT playlist_name
31                  FROM playlists
32                  WHERE username = ?
33                  """, (username,))
34        return c.fetchall()
35
36
37def get_songs_in_playlist(playlist, username):
38    with sqlite3.connect('all_playlists.db') as conn:
39        c = conn.cursor()
40        c.execute("""
41                  SELECT song_title
42                  FROM playlists
43                  WHERE playlist_name = ?
44                  AND username = ?
45                  """, (playlist, username))
46        return c.fetchall()
47
48if __name__ == '__main__':
49    create_database()
50    # Add some users and songs
51    add_playlist('happy times', 'John Smith', ['Song A', 'Song B'])
52    add_playlist('sad times', 'John Smith', ['Song C', 'Song D'])
53    add_playlist('something else', 'John Smith', ['Song E', 'Song F', 
54                                                  'Song G'])
55    add_playlist('running out of ideas', 'Janet', ['Song F', 'Song G', 
56                                                  'Song H'])
57
58    # Get playlists of John Smith
59    print("John Smith's playlists are: ", get_all_playlists('John Smith'))
60
61    # Get Janet's
62    print("Janet's playlists are: ", get_all_playlists('Janet'))
63
64    # Get all songs in happy times
65    print("All songs in happy times playlist by John are: ",
66          get_songs_in_playlist('happy times', 'John Smith'))