Python CSV & MySQLConnector Errors - Getting CSV Data into MySQL

Question

I've got a function which assembles a string from a user form in Tkinter then downloads a CSV.

I can get the data into the data frame/terminal, create the table in SQL, have the connection, all that, but I'm unable to actually get that data over into the columns in my SQL.

The downloaded CSV file is from AlphaVantage, 6 columns (timestamp, open, high, low, close, volume) and I have added two columns for primary key and ticker. Table creation looks like this:

cursor1.execute(""" CREATE TABLE IF NOT EXISTS TIME_SERIES_INTRADAY(
                row_ID INT AUTO_INCREMENT PRIMARY KEY,
                ticker VARCHAR(255)
                time_stamp VARCHAR(255),
                open DECIMAL(10,2),
                high DECIMAL(10,2),
                low DECIMAL(10,2),
                close DECIMAL(10,2),
                volume INT(255)                
                )""")

Function looks like this:

def downloadCSV():
    generateString()
    #https://stackoverflow.com/questions/35371043/use-python-requests-to-download-csv
    with requests.Session() as s:
        downloadFile = s.get(var_downloadString)
        decodedFile = downloadFile.content.decode('utf-8')
        cr = csv.reader(decodedFile.splitlines(), delimiter =",")
        cr2 = csv.reader(decodedFile)
        listedDownloadFile = list(cr)
        for row in listedDownloadFile:
            print (row)

    cursor1.execute("select database();")
    record = cursor1.fetchone()
    print("You're connected to database: ", record)

    #for i, row in listedDownloadFile.iterrows():
    #next(cr, None)

    skipHeader = True
    for row in decodedFile:
        if skipHeader:
            skipHeader = False
            continue
        sqlStatment = """INSERT INTO DB_AF.TIME_SERIES_INTRADAY (time_stamp, open, high, low, close, volume) VALUES ("%s","%s","%s","%s","%s","%s")"""
        cursor1.execute(sqlStatment, tuple(row))
        print("Record Inserted")
        mydb.commit()
        cursor1.close()

Errors In several hours of trouble shooting, I have several, but the two most common are:

IndexError: tuple index out of range

and

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Things I've Tried i. single vs. multi line statements and various combinations about nesting the entire statement rather than using the sqlStatement variable. ii. toggling within the for row inbetween downloadFile, decodedFile, cr, cr2, and listedDownloadFile. iii. It's worth mentioning that when I use cr variable, I have no errors, but it also does not write to the table in MySQL.

Would very much appreciate any input.

Python CSV & MySQLConnector Errors - Getting CSV Data into MySQL

0 Answers