84 lines
2.1 KiB
Python
84 lines
2.1 KiB
Python
import sqlite3
|
|
import os
|
|
import pyexcel_odsr
|
|
|
|
try:
|
|
os.remove('songbook.sqlite')
|
|
except FileNotFoundError:
|
|
pass
|
|
|
|
db = sqlite3.connect("songbook.sqlite")
|
|
|
|
cur = db.cursor()
|
|
|
|
with open('db.sql') as file:
|
|
db.executescript(file.read())
|
|
|
|
data = pyexcel_odsr.get_data('reference.ods', skip_empty_rows=True)
|
|
clipdata = pyexcel_odsr.get_data('reference_clips.ods', skip_empty_rows=True)
|
|
|
|
# import song
|
|
cur.executemany(
|
|
'insert or ignore into song(id,name,name_jp,location) values(?, ?, ?, ?)',
|
|
((row[0],row[3],row[4],'' if len(row)<11 else row[10]) for row in data['Song'][1:])
|
|
)
|
|
|
|
# import album
|
|
cur.executemany(
|
|
'insert into album(id,name,date,code,description,updates) values (?, ?, ?, ?, ?, ?)',
|
|
data['Album'][1:]
|
|
)
|
|
|
|
# import artist
|
|
cur.executemany(
|
|
'insert into artist(id,name_rm,name) values(?, ?, ?)',
|
|
data['Artist'][1:]
|
|
)
|
|
|
|
# import credit
|
|
cur.executemany(
|
|
'insert into credit(id,name) values(?, ?)',
|
|
data['Credit'][1:]
|
|
)
|
|
|
|
# import motif
|
|
cur.executemany(
|
|
'insert into motif(id,name,category) values(?, ?, ?)',
|
|
((row[0],row[1],'' if len(row)<3 else row[2]) for row in clipdata['Motif'][1:])
|
|
)
|
|
|
|
# import clip
|
|
cur.executemany(
|
|
'insert into clip(start_ms,duration_ms,song_id,motif_id,feature) values (?, ?, ?, ?, ?)',
|
|
((row[0],row[1],row[2],row[3],0 if len(row)<5 else row[4]) for row in clipdata['Clip'][1:])
|
|
)
|
|
|
|
# import category
|
|
cur.executemany(
|
|
'insert into category(id,name) values (?, ?)',
|
|
clipdata['Category'][1:]
|
|
)
|
|
|
|
# import song x album
|
|
cur.executemany(
|
|
"insert into song_album(song_id,album_id,track) values (?, ?, ?)",
|
|
(row[:3] for row in data['Song'][1:])
|
|
)
|
|
|
|
# import song x artist
|
|
def creditparams(rows):
|
|
for row in rows[1:]:
|
|
for index, artist in enumerate(row[5:], 5):
|
|
if len(artist)>0:
|
|
yield (row[0],artist,rows[0][index])
|
|
|
|
cur.executemany(
|
|
'''insert or ignore into song_artist(song_id,artist_id,credit_id)
|
|
values (
|
|
?,
|
|
(select id from artist where coalesce(nullif(name_rm,''),name) = ?),
|
|
(select id from credit where name = ?))''',
|
|
creditparams(data['Song'])
|
|
)
|
|
|
|
db.commit() |