印象語(cool, excited, relax, sad, fierce)からその印象に合った曲を検索します
構成図はこんな感じです

今回はadd_table.pyの部分を記事にしますが、impressionのテーブルの定義は次回にします。

add_table.pyの中身はこうなっています
シェルスクリプトで関数を実行するために以下の記事を参考にしました

add_table.py

import argparse
import create_table
import save_db
import music_info

# 実行文
# python add_table.py add_table -i '{id}'

#music tableの作成
def add_table(id):
    create_table.create_music_table()
    print("create table")
    
    ids = imusic_info.getTrackIDs({ユーザーID}, '{}'.format(id))
    print("get API")

    save_db.save_df(music_info.make_df(ids))
    print("save db")

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument('function_name',
                        type=str,
                        help='set fuction name in this file')
    parser.add_argument('-i', '--func_args',
                        nargs='*',
                        help='args in function',
                        default=[])
    args = parser.parse_args()

    # このファイル内の関数を取得
    func_dict = {k: v for k, v in locals().items() if callable(v)}
    # 引数のうち，数値として解釈できる要素はfloatにcastする
    func_args = [float(x) if x.isnumeric() else x for x in args.func_args]
    # 関数実行
    ret = func_dict[args.function_name](*func_args)

手順としては
1,　DB作成(create_table)
2,　楽曲情報を取得(imusic_info)
3,　データフレームに変換(imusic_info)
4,　DBに保存(save_db)
です

実行環境
macOS Catlina 10.15.7
Python 3.7.5
また今回は全ての作業をpythonで行いたかったのでpsycopg2を用いました

1,　DB作成

create_table.py

import psycopg2
#テーブル作成
def create_music_table():
    conn = psycopg2.connect("host=" + "localhost" +
                           " dbname=" + {データベース名} +
                           " user=" + {自分のmacのユーザーネーム} +
                           " password=" + "")
    cur = conn.cursor()
    cur.execute('CREATE TABLE IF NOT EXISTS music (name TEXT NOT NULL, artist TEXT NOT NULL, danceability FLOAT8 NOT NULL,acousticness FLOAT8 NOT NULL,energy FLOAT8 NOT NULL,liveness FLOAT8 NOT NULL,loudness FLOAT8 NOT NULL,speechiness FLOAT8 NOT NULL,tempo FLOAT8 NOT NULL,valence FLOAT8 NOT NULL);')
    cur.execute('CREATE TABLE IF NOT EXISTS music_normalization (name TEXT NOT NULL, artist TEXT NOT NULL, danceability FLOAT8 NOT NULL,acousticness FLOAT8 NOT NULL,energy FLOAT8 NOT NULL,liveness FLOAT8 NOT NULL,loudness FLOAT8 NOT NULL,speechiness FLOAT8 NOT NULL,tempo FLOAT8 NOT NULL,valence FLOAT8 NOT NULL);')
    conn.commit()
    cur.close()
    conn.close()
    return

2,　楽曲情報を取得

Spotify APIの取得は以下のサイトを参考にしました
https://knowledge.insight-lab.co.jp/sisense/information/spotify_music_data_1

music_info.py

import spotipy
from spotipy.oauth2 import SpotifyClientCredentials
import pandas as pd
import time

def getTrackIDs(user, playlist_id): #自分のユーザーID/取得したいプレイリストID
    ids = []
    client_id = {自分の情報}
    client_secret = {自分の情報}

    client_credentials_manager = spotipy.oauth2.SpotifyClientCredentials(client_id, client_secret)
    spotify = spotipy.Spotify(client_credentials_manager=client_credentials_manager)
    client_credentials_manager = SpotifyClientCredentials(client_id, client_secret)
    sp = spotipy.Spotify(client_credentials_manager=client_credentials_manager)

    playlist = sp.user_playlist(user, playlist_id)
    while playlist['tracks']['next']:
        for item in playlist['tracks']['items']:
            track = item['track']
            ids.append(track['id'])
        playlist['tracks'] = sp.next(playlist['tracks'])
    else:
        for item in playlist['tracks']['items']:
            track = item['track']
            ids.append(track['id'])
    return ids


def getTrackFeatures(id):
    client_id = {自分の情報}
    client_secret = {自分の情報}
    client_credentials_manager = SpotifyClientCredentials(client_id, client_secret)
    sp = spotipy.Spotify(client_credentials_manager=client_credentials_manager)

    meta = sp.track(id)
    features = sp.audio_features(id)

    # data
    name = meta['name']
    artist = meta['album']['artists'][0]['name']
    acousticness = features[0]['acousticness']
    danceability = features[0]['danceability']
    energy = features[0]['energy']
    liveness = features[0]['liveness']
    loudness = features[0]['loudness']
    speechiness = features[0]['speechiness']
    tempo = features[0]['tempo']
    valence = features[0]['valence']

    track = [name, artist, danceability, acousticness, energy, liveness, loudness, speechiness, tempo, valence]
    return track

getTrackIDs = 曲のIDを取得
getTrackFeatures　= 曲の情報を取得
みたいな感じです

3,　データフレームに変換

後々正規化などをする予定なのでdfに変換しておく

music_info.py

def make_df(ids):#getTrackIDsで返ってきたID
    tracks = []
    for i in range(len(ids)):
        time.sleep(.5)
        track = getTrackFeatures(ids[i])
        tracks.append(track)

    df = pd.DataFrame(tracks, columns = ['name', 'artist', 'danceability', 'acousticness', 'energy', 'liveness', 'loudness', 'speechiness', 'tempo', 'valence'])
    

    return df

4,　DBに保存

楽曲名とアーティスト名が重複したら保存しないようにしました

save_db.py

import psycopg2

#重複を避けてDBに保存
def save_df(df):
    conn = psycopg2.connect("host=" + "localhost" +
                           " dbname=" + {データベース名} +
                           " user=" + {自分のmacのユーザーネーム} +
                           " password=" + "")
    cur = conn.cursor()
    for text in df.values:
        values = (text[0], text[1], text[2], text[3], text[4], text[5], text[6], text[7], text[8], text[9])
        
        cur.execute('SELECT * FROM music WHERE name = %s AND artist = %s', (text[0], text[1]))
        record = cur.fetchone()
        if record is not None:
            continue
        else:
            try:
                cur.execute ('INSERT INTO music (name, artist, danceability, acousticness, energy, liveness, loudness, speechiness, tempo, valence)' 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' ,values)
            except Exception as err:
                print("Encountered exception. {}".format(err))

        conn.commit()

    cur.execute("SELECT * FROM music")
    m = cur.fetchall()
    conn.commit()
    print("The number of music is ", len(m))
    cur.close()
    conn.close()

実行結果

~/music2imp$ python add_table.py add_table -i '5gNu9eJF7yg4xHQBXWVVaN'                
create table
get API
The number of music is  2849
save db

musicテーブルの中身のはこんな感じです(一部)

fc=# select * from music;
                                                                     name                                                                      |                                                             artist                                                              | danceability | acousticness | energy  | liveness | loudness | speechiness |  tempo  | valence
-----------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+----------+----------+-------------+---------+---------
 道                                                                                                                                            | EXILE                                                                                                                           |        0.478 |      0.00209 |   0.592 |    0.245 |   -5.975 |      0.0265 | 139.607 |   0.271
 桜                                                                                                                                            | KOBUKURO                                                                                                                        |        0.462 |        0.531 |   0.414 |    0.119 |   -7.392 |      0.0251 |  83.474 |   0.342
 桜                                                                                                                                            | FUNKY MONKEY BABYS                                                                                                              |         0.65 |        0.291 |   0.734 |    0.153 |   -3.746 |      0.0302 |  81.993 |   0.726
 蕾(つぼみ)                                                                                                                                    | KOBUKURO                                                                                                                        |        0.355 |        0.203 |   0.441 |    0.155 |   -5.785 |      0.0253 |  75.894 |   0.283
 流星                                                                                                                                          | KOBUKURO                                                                                                                        |        0.464 |         0.49 |   0.553 |     0.32 |   -5.314 |      0.0272 |  77.022 |   0.254
 遥か                                                                                                                                          | GReeeeN                                                                                                                         |        0.516 |        0.258 |   0.428 |    0.213 |    -5.53 |      0.0242 |  84.005 |   0.368
 ここにしか咲かない花                                                                                                                          | KOBUKURO

続きは下にあります

1, DB作成

2, 楽曲情報を取得

3, データフレームに変換

4, DBに保存

実行結果

1,　DB作成

2,　楽曲情報を取得

3,　データフレームに変換

4,　DBに保存