· 7 years ago · Jan 06, 2019, 08:32 PM
1import sqlite3
2from sqlite3 import Error
3
4
5class DBManager:
6
7 sql_create_releases_table = """ CREATE TABLE IF NOT EXISTS releases (
8 id integer PRIMARY KEY,
9 name text NOT NULL,
10 label text,
11 producer text,
12 reference_url text,
13 search_query text,
14 release_date text,
15 spotify_id text,
16 genre_skip bit DEFAULT 0,
17 saved_on_spotify bit DEFAULT 0,
18 save_date DATE DEFAULT (datetime('now','localtime'))
19 ); """
20
21 sql_create_artists_table = """ CREATE TABLE IF NOT EXISTS artists (
22 id integer PRIMARY KEY,
23 name text NOT NULL,
24 spotify_id text
25 ); """
26
27 sql_create_artist_list_table = """ CREATE TABLE IF NOT EXISTS artist_list (
28 release_id INTEGER,
29 artist_id INTEGER,
30 FOREIGN KEY(release_id) REFERENCES releases(id),
31 FOREIGN KEY(artist_id) REFERENCES artists(id)
32 ); """
33
34 sql_create_artist_genres_table = """ CREATE TABLE IF NOT EXISTS artist_genres (
35 genre_id INTEGER,
36 artist_id INTEGER,
37 FOREIGN KEY(genre_id) REFERENCES genres(id),
38 FOREIGN KEY(artist_id) REFERENCES artists(id)
39 ); """
40
41 sql_create_genres_table = """ CREATE TABLE IF NOT EXISTS genres (
42 id integer PRIMARY KEY,
43 name text NOT NULL
44 ); """
45
46 sql_create_releases2_table = """ CREATE TABLE IF NOT EXISTS releases2 (
47 id integer PRIMARY KEY,
48 name text NOT NULL,
49 artist text NOT NULL,
50 search_query text,
51 release_date text,
52 spotify_id text,
53 found_on_spotify bit DEFAULT 0,
54 genre_skip bit DEFAULT 0,
55 saved_on_spotify bit DEFAULT 0,
56 save_date DATE DEFAULT (datetime('now','localtime'))
57 ); """
58
59 def create_release(self, conn, release):
60 """
61 Create a new project into the projects table
62 :param conn:
63 :param release:
64 :return: release id
65 """
66 sql = ''' INSERT INTO releases(name,label,producer,reference_url,search_query,release_date,spotify_id,genre_skip,saved_on_spotify)
67 VALUES(?,?,?,?,?,?,?,?,?)'''
68 cur = conn.cursor()
69 cur.execute(sql, release)
70 return cur.lastrowid
71
72 def create_release2(self, conn, release):
73 """
74 Create a new project into the projects table
75 :param conn:
76 :param release:
77 :return: release id
78 """
79 sql = ''' INSERT INTO releases2(name,artist,search_query,release_date,spotify_id,found_on_spotify,genre_skip,saved_on_spotify)
80 VALUES(?,?,?,?,?,?,?,?)'''
81 cur = conn.cursor()
82 cur.execute(sql, release)
83 return cur.lastrowid
84
85 def create_artist(self, conn, artist):
86 """
87 Create a new project into the projects table
88 :param conn:
89 :param artist:
90 :return: artist id
91 """
92 sql = ''' INSERT INTO artists(name,spotify_id)
93 VALUES(?,?)'''
94 cur = conn.cursor()
95 cur.execute(sql, artist)
96 return cur.lastrowid
97
98 def create_artist_genre(self, conn, artist_genre):
99 """
100 Create a new project into the projects table
101 :param conn:
102 :param artist_genre:
103 :return: artist_genre id
104 """
105 sql = ''' INSERT INTO artist_genres(genre_id, artist_id)
106 VALUES(?,?)'''
107 cur = conn.cursor()
108 cur.execute(sql, artist_genre)
109 return cur.lastrowid
110
111 def create_artist_list(self, conn, artist_list):
112 """
113 Create a new project into the projects table
114 :param conn:
115 :param artist_list:
116 :return: artist_list id
117 """
118 sql = ''' INSERT INTO artist_list(release_id, artist_id)
119 VALUES(?,?)'''
120 cur = conn.cursor()
121 cur.execute(sql, artist_list)
122 return cur.lastrowid
123
124 def create_genre(self, conn, genre):
125 """
126 Create a new project into the projects table
127 :param conn:
128 :param genre:
129 :return: genre id
130 """
131 sql = ''' INSERT INTO genres(name)
132 VALUES(?)'''
133 cur = conn.cursor()
134 cur.execute(sql, genre)
135 return cur.lastrowid
136
137 def create_connection(self, db_file):
138 """ create a database connection to the SQLite database
139 specified by db_file
140 :param db_file: database file
141 :return: Connection object or None
142 """
143 try:
144 conn = sqlite3.connect(db_file)
145 return conn
146 except Error as e:
147 print(e)
148
149 return None
150
151 def create_table(self, create_table_sql):
152 """ create a table from the create_table_sql statement
153 :param conn: Connection object
154 :param create_table_sql: a CREATE TABLE statement
155 :return:
156 """
157 try:
158 cur = self.conn.cursor()
159 cur.execute(create_table_sql)
160 except Error as e:
161 print(e)
162
163 database = 'MusicDatabase.db'
164 conn = sqlite3.Connection
165
166 def get_releases(self):
167 releases_rows = []
168 cur = self.conn.cursor()
169 cur.execute("SELECT * FROM releases2")
170 releases_rows = cur.fetchall()
171 return releases_rows
172
173 def get_artists(self):
174 artists_rows = []
175 cur = self.conn.cursor()
176 cur.execute("SELECT * FROM artists")
177 artists_rows = cur.fetchall()
178 return artists_rows
179
180 def get_genres(self):
181 genres_rows = []
182 cur = self.conn.cursor()
183 cur.execute("SELECT * FROM genres")
184 genres_rows = cur.fetchall()
185 return genres_rows
186
187 def get_conn(self):
188 return self.conn
189
190 def __init__(self):
191
192 self.conn = self.create_connection(self.database)
193 if self.conn is not None:
194 # create projects table
195 self.create_table(self.sql_create_releases2_table)
196 self.create_table(self.sql_create_releases_table)
197 self.create_table(self.sql_create_artists_table)
198 self.create_table(self.sql_create_genres_table)
199 self.create_table(self.sql_create_artist_list_table)
200 self.create_table(self.sql_create_artist_genres_table)
201 else:
202 print("Error! cannot create the database connection.")