def insert_score(p: Print) -> int: """ Vloží záznamy score(name, genre, key, incipit, year) do tabulky.""" score = p.score() score.incipit = "".join( score.incipit) # TODO: Jenom hack –> upravit formát 'incipit' pořádně incipit_lookup = 'incipit IS NULL' if not score.incipit else 'incipit=?' year_lookup = 'year IS NULL' if not score.year else 'year=?' # Vyhledávání v databázi v závislosti na přítomných parametrech get_score_id = f"SELECT id FROM score WHERE name=? AND genre=? AND key=? AND {incipit_lookup} AND {year_lookup}" sql_params = (score.name, score.genre, score.key) if score.incipit: sql_params += (score.incipit, ) if score.year: sql_params += (score.year, ) cursor.execute(get_score_id, sql_params) results = cursor.fetchall() if results: for result in results: if compare_authors(score, result[0]) and compare_voices( score, result[0]): # Existující -> vrátit ID return result[0] # Chybí záznam -> přidat do tabulky cursor.execute( 'INSERT INTO score(name, genre, key, incipit, year) VALUES(?, ?, ?, ?, ?)', (score.name, score.genre, score.key, score.incipit, score.year)) db_connection.commit() score_id = cursor.lastrowid for voice in score.voices: # Vložit hlasy do 'voice' + commit cursor.execute( 'INSERT INTO voice(number, score, range, name) VALUES(?, ?, ?, ?)', ( voice.number, score_id, voice.range, voice.name, )) db_connection.commit() for author in score.authors: # Vložit autory do 'score_authors' + commit if not author.name: # Bezejmenný autor continue cursor.execute('SELECT id FROM person WHERE name=?', (author.name, )) # Najít ID autora result = cursor.fetchone() cursor.execute( 'INSERT INTO score_author(score, composer) VALUES(?, ?)', (score_id, result[0])) # Junction db_connection.commit() return score_id
def insert_edition(p: Print, score_id: int) -> int: """ Vloží záznamy edition(score, name, year) do tabulky.""" edition = p.edition name_lookup = 'name IS NULL' if not edition.name else 'name=?' year_lookup = 'year IS NULL' if not p.score().year else 'year=?' # Vyhledávání v databázi v závislosti na přítomných parametrech get_edition_id = f"SELECT id FROM edition WHERE score=? AND {name_lookup} AND {year_lookup}" sql_params = (score_id, ) if edition.name: sql_params += (edition.name, ) if p.score().year: sql_params += (p.score().year, ) cursor.execute(get_edition_id, sql_params) results = cursor.fetchall() if results: for result in results: if compare_editors(edition, result[0]): # Existující -> vrátit ID return result[0] # Chybí záznam -> přidat do tabulky cursor.execute('INSERT INTO edition(score, name, year) VALUES(?, ?, ?)', (score_id, edition.name, p.score().year)) db_connection.commit() edition_id = cursor.lastrowid for editor in edition.authors: # Vložit autory do 'edition_author' + commit cursor.execute('SELECT id FROM person WHERE name=?', (editor.name, )) result = cursor.fetchone() cursor.execute( 'INSERT INTO edition_author(edition, editor) VALUES(?, ?)', ( edition_id, result[0], )) # Junction db_connection.commit() return edition_id