fetchTracks.py 文件源码

def save_sqlite(track_info):
    [track, trackInfoEchoNest, trackInfoLastFM] = track_info
    convert_type_dict = {float:'REAL', unicode:'TEXT', int:'INTEGER', str:'TEXT'}

    EN_key_list = trackInfoEchoNest.keys()
    LF_key_list = trackInfoLastFM.keys()

    table_columns = []
    column_names = []
    question_mark_sign = []
    for key in EN_key_list:
        if type(trackInfoEchoNest[key]) != types.NoneType:
            column_names.append('EN_' + key)
            table_columns1 = ' '.join(['EN_' + key, convert_type_dict[type(trackInfoEchoNest[key])]])
            table_columns.append(table_columns1)
            question_mark_sign.append('?')
        else:
            trackInfoEchoNest.pop(key)

    for key in LF_key_list:
        if type(trackInfoLastFM[key]) != types.NoneType:
            column_names.append('LF_' + key)
            table_columns1 = ' '.join(['LF_' + key, convert_type_dict[type(trackInfoLastFM[key])]])
            table_columns.append(table_columns1)
            question_mark_sign.append('?')
        else:
            trackInfoLastFM.pop(key)

    con = lite.connect('musicdata.db')
    with con:
        cur = con.cursor()
        #cur.execute("DROP TABLE IF EXISTS BoardEntries")
        cur.execute("SELECT ROWID FROM TrackEntries WHERE EN_title = ? and EN_artist_name = ?", (trackInfoEchoNest['title'],trackInfoEchoNest['artist_name']))
        existingEntry=cur.fetchone()
        if existingEntry is None:
            print('saving into database %s, %s'%(trackInfoEchoNest['title'], trackInfoEchoNest['artist_name']))
            single_entry = tuple([str(time.strftime("%Y-%m-%d %H:%M:%S")), track['title'], track['artist']] + trackInfoEchoNest.values() + trackInfoLastFM.values())
            cur.execute('INSERT OR IGNORE INTO TrackEntries(ID, EntryDate, SearchTitle, SearchArtist, ' + ', '.join(column_names) + ') VALUES (NULL, ?,?,?, ' + ', '.join(question_mark_sign)+')', single_entry)
        else:
            print('found in database %s, %s'%(trackInfoEchoNest['title'], trackInfoEchoNest['artist_name']))
    con.close()