· 7 years ago · Dec 22, 2018, 01:34 AM
1{
2 "cells": [
3 {
4 "cell_type": "code",
5 "execution_count": 1,
6 "metadata": {},
7 "outputs": [],
8 "source": [
9 "import sqlite3\n",
10 "import os\n"
11 ]
12 },
13 {
14 "cell_type": "code",
15 "execution_count": 5,
16 "metadata": {},
17 "outputs": [],
18 "source": [
19 "os.getcwd()\n",
20 "os.chdir('/home/zeski/Documents/Data_Science/SQL/SQL_DBS/')"
21 ]
22 },
23 {
24 "cell_type": "code",
25 "execution_count": 8,
26 "metadata": {},
27 "outputs": [],
28 "source": [
29 "conn = sqlite3.connect('Artist.sqlite')\n",
30 "c = conn.cursor()"
31 ]
32 },
33 {
34 "cell_type": "markdown",
35 "metadata": {},
36 "source": [
37 "# Creating the Tables for Database: "
38 ]
39 },
40 {
41 "cell_type": "code",
42 "execution_count": 9,
43 "metadata": {},
44 "outputs": [
45 {
46 "data": {
47 "text/plain": [
48 "<sqlite3.Cursor at 0x7fd4a030f180>"
49 ]
50 },
51 "execution_count": 9,
52 "metadata": {},
53 "output_type": "execute_result"
54 }
55 ],
56 "source": [
57 "c.execute('''\n",
58 " CREATE TABLE IF NOT EXISTS Artist(\n",
59 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
60 " name TEXT\n",
61 " )\n",
62 "\n",
63 "\n",
64 "''')"
65 ]
66 },
67 {
68 "cell_type": "code",
69 "execution_count": 10,
70 "metadata": {},
71 "outputs": [
72 {
73 "data": {
74 "text/plain": [
75 "<sqlite3.Cursor at 0x7fd4a030f180>"
76 ]
77 },
78 "execution_count": 10,
79 "metadata": {},
80 "output_type": "execute_result"
81 }
82 ],
83 "source": [
84 "c.execute('''\n",
85 " CREATE TABLE IF NOT EXISTS Genre(\n",
86 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
87 " name TEXT\n",
88 " )\n",
89 "''')"
90 ]
91 },
92 {
93 "cell_type": "code",
94 "execution_count": 11,
95 "metadata": {},
96 "outputs": [
97 {
98 "data": {
99 "text/plain": [
100 "<sqlite3.Cursor at 0x7fd4a030f180>"
101 ]
102 },
103 "execution_count": 11,
104 "metadata": {},
105 "output_type": "execute_result"
106 }
107 ],
108 "source": [
109 "c.execute('''\n",
110 " CREATE TABLE IF NOT EXISTS Album(\n",
111 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
112 " artist_id INTEGER,\n",
113 " title TEXT\n",
114 " )\n",
115 "''')"
116 ]
117 },
118 {
119 "cell_type": "code",
120 "execution_count": 12,
121 "metadata": {},
122 "outputs": [
123 {
124 "data": {
125 "text/plain": [
126 "<sqlite3.Cursor at 0x7fd4a030f180>"
127 ]
128 },
129 "execution_count": 12,
130 "metadata": {},
131 "output_type": "execute_result"
132 }
133 ],
134 "source": [
135 "c.execute('''\n",
136 " CREATE TABLE IF NOT EXISTS Track(\n",
137 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
138 " title TEXT,\n",
139 " album_id INTEGER,\n",
140 " genre_id INTEGER, \n",
141 " len INTEGER,\n",
142 " rating INTEGER,\n",
143 " count INTEGER\n",
144 " )\n",
145 "''')"
146 ]
147 },
148 {
149 "cell_type": "code",
150 "execution_count": 13,
151 "metadata": {},
152 "outputs": [],
153 "source": [
154 "conn.commit()"
155 ]
156 },
157 {
158 "cell_type": "markdown",
159 "metadata": {},
160 "source": [
161 "# Inserting Data into the various tables: "
162 ]
163 },
164 {
165 "cell_type": "code",
166 "execution_count": 14,
167 "metadata": {},
168 "outputs": [
169 {
170 "data": {
171 "text/plain": [
172 "<sqlite3.Cursor at 0x7fd4a030f180>"
173 ]
174 },
175 "execution_count": 14,
176 "metadata": {},
177 "output_type": "execute_result"
178 }
179 ],
180 "source": [
181 "c.execute('''\n",
182 " INSERT INTO Artist(name) VALUES ('Led Zeppelin')\n",
183 "''')\n",
184 "c.execute('''\n",
185 " INSERT INTO Artist(name) VALUES ('AC/DC')\n",
186 "''')\n",
187 "conn.commit()"
188 ]
189 },
190 {
191 "cell_type": "code",
192 "execution_count": 17,
193 "metadata": {},
194 "outputs": [],
195 "source": [
196 "c.execute('''\n",
197 " INSERT INTO Genre(name) VALUES ('Rock')\n",
198 "''')\n",
199 "c.execute('''\n",
200 " INSERT INTO Genre(name) VALUES ('Metal')\n",
201 "''')\n",
202 "conn.commit()"
203 ]
204 },
205 {
206 "cell_type": "code",
207 "execution_count": 20,
208 "metadata": {},
209 "outputs": [],
210 "source": [
211 "c.execute('''\n",
212 " INSERT INTO Album(title,artist_id) VALUES ('Who Made Who',2);\n",
213 "''')\n",
214 "c.execute('''\n",
215 " INSERT INTO Album(title, artist_id) VALUES ('IV', 1)\n",
216 "''')\n",
217 "\n",
218 "conn.commit()"
219 ]
220 },
221 {
222 "cell_type": "code",
223 "execution_count": 21,
224 "metadata": {},
225 "outputs": [],
226 "source": [
227 "c.execute('''\n",
228 " INSERT INTO Track(title, rating, len,count,album_id, genre_id) VALUES ('Black Dog', 5,297,0,2,1)\n",
229 "''')\n",
230 "c.execute('''\n",
231 " INSERT INTO Track(title, rating, len,count,album_id, genre_id) VALUES ('Stairway', 5, 482, 0, 2, 1)\n",
232 "''')\n",
233 "c.execute('''\n",
234 " INSERT INTO Track(title, rating, len,count,album_id, genre_id) VALUES ('About to Rock', 5,313,0,1,2)\n",
235 "''')\n",
236 "c.execute('''\n",
237 " INSERT INTO Track(title, rating, len,count,album_id, genre_id) VALUES ('Who Made Who', 5,207, 0,1,2)\n",
238 "''')\n",
239 "conn.commit()"
240 ]
241 },
242 {
243 "cell_type": "markdown",
244 "metadata": {},
245 "source": [
246 "## Selecting Data"
247 ]
248 },
249 {
250 "cell_type": "code",
251 "execution_count": 23,
252 "metadata": {},
253 "outputs": [
254 {
255 "name": "stdout",
256 "output_type": "stream",
257 "text": [
258 "[('Who Made Who', 'Led Zeppelin'), ('Who Made Who', 'AC/DC'), ('IV', 'Led Zeppelin'), ('IV', 'AC/DC')]\n"
259 ]
260 }
261 ],
262 "source": [
263 "c.execute('''\n",
264 " SELECT Album.title, Artist.name FROM Album JOIN Artist ON Album.artist_id = Artist_id\n",
265 "''')\n",
266 "print(c.fetchall())"
267 ]
268 },
269 {
270 "cell_type": "code",
271 "execution_count": 25,
272 "metadata": {},
273 "outputs": [
274 {
275 "name": "stdout",
276 "output_type": "stream",
277 "text": [
278 "[('Who Made Who', 2, 2, 'AC/DC'), ('IV', 1, 1, 'Led Zeppelin')]\n"
279 ]
280 }
281 ],
282 "source": [
283 "c.execute('''\n",
284 " SELECT Album.title, Album.artist_id, Artist_id, Artist.name FROM Album join Artist ON Album.artist_id = Artist.id\n",
285 "''')\n",
286 "print(c.fetchall())"
287 ]
288 },
289 {
290 "cell_type": "code",
291 "execution_count": 28,
292 "metadata": {},
293 "outputs": [
294 {
295 "name": "stdout",
296 "output_type": "stream",
297 "text": [
298 "[('Black Dog', 'Rock'), ('Stairway', 'Rock'), ('About to Rock', 'Metal'), ('Who Made Who', 'Metal')]\n"
299 ]
300 }
301 ],
302 "source": [
303 "c.execute('''\n",
304 " SELECT Track.title, Genre.name FROM Track JOIN Genre ON Track.genre_id = Genre.id\n",
305 "''')\n",
306 "print(c.fetchall())"
307 ]
308 },
309 {
310 "cell_type": "code",
311 "execution_count": 30,
312 "metadata": {},
313 "outputs": [
314 {
315 "name": "stdout",
316 "output_type": "stream",
317 "text": [
318 "[('Black Dog', 'Led Zeppelin', 'IV', 'Rock'), ('Stairway', 'Led Zeppelin', 'IV', 'Rock'), ('About to Rock', 'AC/DC', 'Who Made Who', 'Metal'), ('Who Made Who', 'AC/DC', 'Who Made Who', 'Metal')]\n"
319 ]
320 }
321 ],
322 "source": [
323 "c.execute('''\n",
324 " SELECT Track.title, Artist.name, Album.title, Genre.name FROM Track JOIN Genre JOIN Album JOIN Artist ON Track.genre_id = Genre.id AND Track.album_id = Album.id AND Album.artist_id = Artist.id\n",
325 "''')\n",
326 "print(c.fetchall())"
327 ]
328 },
329 {
330 "cell_type": "code",
331 "execution_count": null,
332 "metadata": {},
333 "outputs": [],
334 "source": []
335 },
336 {
337 "cell_type": "code",
338 "execution_count": null,
339 "metadata": {},
340 "outputs": [],
341 "source": []
342 },
343 {
344 "cell_type": "code",
345 "execution_count": null,
346 "metadata": {},
347 "outputs": [],
348 "source": []
349 },
350 {
351 "cell_type": "code",
352 "execution_count": null,
353 "metadata": {},
354 "outputs": [],
355 "source": []
356 },
357 {
358 "cell_type": "code",
359 "execution_count": null,
360 "metadata": {},
361 "outputs": [],
362 "source": []
363 },
364 {
365 "cell_type": "code",
366 "execution_count": null,
367 "metadata": {},
368 "outputs": [],
369 "source": []
370 }
371 ],
372 "metadata": {
373 "kernelspec": {
374 "display_name": "Python 3",
375 "language": "python",
376 "name": "python3"
377 },
378 "language_info": {
379 "codemirror_mode": {
380 "name": "ipython",
381 "version": 3
382 },
383 "file_extension": ".py",
384 "mimetype": "text/x-python",
385 "name": "python",
386 "nbconvert_exporter": "python",
387 "pygments_lexer": "ipython3",
388 "version": "3.6.5"
389 }
390 },
391 "nbformat": 4,
392 "nbformat_minor": 2
393}