You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
100 lines
2.5 KiB
100 lines
2.5 KiB
#!/usr/bin/env python |
|
# -*- encoding: utf-8 -*- |
|
|
|
import yaml |
|
import sys |
|
import sqlite3 |
|
import os |
|
import os.path |
|
import datetime |
|
|
|
result = None |
|
with open('../lyrics.yaml') as content: |
|
result = yaml.load(content) |
|
|
|
if not result: |
|
print 'Error opening file.' |
|
sys.exit(1) |
|
|
|
if os.path.exists('lyrics.sq3'): |
|
print 'Removing old database...' |
|
os.remove('lyrics.sq3') |
|
|
|
print 'Connecting...' |
|
connection = sqlite3.connect('lyrics.sq3') |
|
|
|
print 'Creating tables...' |
|
cursor = connection.cursor() |
|
|
|
print '\tArtists...' |
|
cursor.execute('''CREATE TABLE artists ( |
|
id INTEGER PRIMARY KEY, |
|
name TEXT CONSTRAINT artist_name UNIQUE |
|
)''') |
|
|
|
print '\tSongs...' |
|
cursor.execute('''CREATE TABLE songs ( |
|
id INTEGER PRIMARY KEY, |
|
title TEXT, |
|
artist INTEGER CONSTRAINT artist_song REFERENCES artists (id), |
|
done_in TEXT, |
|
CONSTRAINT no_repeats UNIQUE (artist, title) ON CONFLICT ABORT |
|
)''') |
|
|
|
print '\tLyrics...' |
|
cursor.execute('''CREATE TABLE lyrics ( |
|
id INTEGER PRIMARY KEY, |
|
song INTEGER CONSTRAINT song_lyrics REFERENCES songs (id), |
|
lyrics TEXT NOT NULL |
|
)''') |
|
|
|
print '\tTips...' |
|
cursor.execute('''CREATE TABLE tips ( |
|
id INTEGER PRIMARY KEY, |
|
title TEXT NOT NULL, |
|
value TEXT NOT NULL |
|
)''') |
|
|
|
print 'Converting songs...' |
|
today = datetime.date.today() |
|
|
|
for song in result: |
|
done = song['done'] |
|
lyrics = song['lyrics'] |
|
|
|
# fucking "Yes" |
|
artist = song['artist'] |
|
if isinstance(artist, basestring): |
|
artist = song['artist'].lower() |
|
else: |
|
artist = 'yes' |
|
|
|
song = unicode(song['song']).lower() |
|
|
|
print '\t', artist, '-', song |
|
|
|
cursor.execute('SELECT id FROM artists WHERE name=?', |
|
(artist, )) |
|
artist_row = cursor.fetchone() |
|
if not artist_row: |
|
print '\t\tNot in db, adding...' |
|
cursor.execute('INSERT INTO artists (id, name) VALUES (NULL, ?)', |
|
(artist, )) |
|
artist_id = cursor.lastrowid |
|
else: |
|
print '\t\tAlready in db with id', artist_row[0] |
|
artist_id = artist_row[0] |
|
|
|
cursor.execute('INSERT INTO songs (id, title, artist, done_in) VALUES ' |
|
'(NULL, ?, ?, ?)', |
|
(song, artist_id, today if done else None)) |
|
song = cursor.lastrowid |
|
|
|
if lyrics: |
|
# some songs do not have lyrics, as they were in the db just to mark |
|
# them as "done" |
|
cursor.execute('INSERT INTO lyrics (id, song, lyrics) VALUES (NULL, ?, ?)', |
|
(song, lyrics)) |
|
connection.commit() |
|
|
|
connection.close()
|
|
|