finished web scrapper

1 files changed, 117 insertions, 0 deletions

diff --git a/scrap/database.py b/scrap/database.py
new file mode 100644
index 0000000..3934361
--- /dev/null
+++ b/scrap/database.py
@@ -0,0 +1,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()