Why won't my extracted data from Spotify's API store in MySQL database?-CodePudding

I have connected to Spotify's API in Python to extract the top twenty tracks of a searched artist. I am trying to store the data in MySQL Workbench in a database named 'spotify_api', I created called 'spotify'. Before I added my code to connect to MySQL Workbench, my code worked correctly and was able to extract the list of tracks, but I have run into issues in getting my code to connect to my database. Below is the code I have written to both extract the data and store it into my database:


    import spotipy
    from spotipy.oauth2 import SpotifyClientCredentials

    import mysql.connector


    mydb = mysql.connector.connect(
        host = "localhost",
        user = "root",
        password = "(removed for question)",
        database = "spotify_api"
    )

    mycursor = mydb.cursor()

    sql = 'DROP TABLE IF EXISTS spotify_api.spotify;'
    mycursor.execute(sql)

    sp = spotipy.Spotify(auth_manager=SpotifyClientCredentials(client_id="(removed for question)",
                                                           client_secret="(removed for question)"))

    results = sp.search(q='sza', limit=20)

    for idx, track in enumerate(results['tracks']['items']):
        print(idx, track['name'])
        sql = "INSERT INTO spotify_api.spotify (tracks, items) VALUES ("   \
            str(idx)   ", '"   track['name']   "');"
        mycursor.execute(sql)

    mydb.commit()
 
    print(mycursor.rowcount, "record inserted.")

    mycursor.execute("SELECT * FROM spotify_api.spotify;")

    myresult = mycursor.fetchall()

    for x in myresult:
        print(x)

    mycursor.close()

Every time I run my code in the VS Code terminal, I receive an error stating that my table doesn't exist. This is what it states: "mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'spotify_api.spotify' doesn't exist" I'm not sure what I need to fix in my code or in my database in order to eliminate this error and get my data stored into my table. In my table I have created two columns 'tracks' and 'items', but I'm not sure if my issues lie in my database or in my code.

CodePudding user response：

Well, it seems pretty clear. You ran

DROP TABLE IF EXISTS spotify_api.spotify;

...

INSERT INTO spotify_api.spotify (tracks, items) VALUES ...

We won't even raise the spectre of the Chuck Berry track titled little ol' Bobby Tables here.

You DROP'd it, then tried to INSERT into it. That won't work. You'll need to CREATE TABLE prior to the INSERT.