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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
import mysql.connector
connector = mysql.connector.connect(
host="localhost",
user="root",
)
cursor = connector.cursor()
def setup():
cursor.execute("CREATE DATABASE IF NOT EXISTS balalaika;")
cursor.execute("USE balalaika;")
cursor.execute("DROP TABLE IF EXISTS song;")
cursor.execute("DROP TABLE IF EXISTS album;")
cursor.execute("DROP TABLE IF EXISTS artist;")
cursor.execute("""
CREATE TABLE artist (
id int NOT NULL AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
);
""")
cursor.execute("""
CREATE TABLE album (
id int NOT NULL AUTO_INCREMENT,
name varchar(255),
cover varchar(510),
artist_id int,
PRIMARY KEY (id),
FOREIGN KEY (artist_id) REFERENCES artist(id)
);
""")
cursor.execute("""
CREATE TABLE song (
id int NOT NULL AUTO_INCREMENT,
name varchar(255),
lyrics TEXT,
album_id int,
PRIMARY KEY (id),
FOREIGN KEY (album_id) REFERENCES album(id)
);
""")
cursor.execute("ALTER TABLE song CONVERT TO CHARACTER SET utf8")
cursor.execute("ALTER TABLE album CONVERT TO CHARACTER SET utf8")
cursor.execute("ALTER TABLE artist CONVERT TO CHARACTER SET utf8")
def process_albums(album_list):
[process_album(album, album_id)
for album_id, album in enumerate(album_list)]
def process_album(album, album_id):
upload_album(album)
[upload_song(song, album_id+1) for song in album.songs]
def upload_album(album):
cursor.execute("""
INSERT INTO album (
name, cover, artist_id
)
VALUES (
%(name)s, %(cover)s, %(artist_id)s
);
""", {
'name': album.name,
'cover': album.cover,
'artist_id': album.artist
})
def upload_song(song, album_id):
cursor.execute("""
INSERT INTO song (
name, lyrics, album_id
)
VALUES (
%(name)s, %(lyrics)s, %(album_id)s
)
""", {
'name': song.name,
'lyrics': song.lyrics,
'album_id': album_id
})
def process_artists(artist_names):
[process_artist(artist) for artist in artist_names]
def process_artist(artist):
cursor.execute("""
INSERT INTO artist (
name
)
VALUES (
%(name)s
)
""", {
'name': artist,
})
def close():
cursor.close()
connector.commit()
connector.close()
|
