0

I am trying get data from an SQL table, do some math operations on it, and then add that final value to the stock_price column to the SQL table. However the code doesn't actually put the values on the table. It's able to output the results just fine with no errors, but when I actually go to look at the table, the values aren't there. The stock_price column in MySQL is set to float.

Here is the code below:

import mysql.connector
try:
    con = mysql.connector.connect(host='localhost',
                                    database='db',
                                    user='root',
                                    password=database_pw)
    query = "SELECT * from nba_players_stats"
    cursor = con.cursor(dictionary=True)
    cursor.execute(query)
    cursor = cursor.fetchall()

    for stat in cursor:
        try:
            game_rate = stat['games_played']/82
            game_pcnt = game_rate*100
            start_rate = stat['games_started']/stat['games_played']
            start_pcnt = start_rate*100
            mins_points = stat['minutes_played']*0.05
            season_points = stat['points']*0.50
            assist_points = stat['assists']*0.25
            rebound_points = stat['rebounds']*0.25
            d_d_points = stat['double_doubles']*2
            t_d_points = stat['triple_doubles']*3

            points = game_pcnt+start_pcnt+mins_points+d_d_points+t_d_points+stat['fg_pcnt']+stat['3pt_pcnt']+stat['ft_pcnt']+season_points+assist_points+rebound_points

            turnover_penalty = stat['turnovers']*2
            total_penalty = turnover_penalty+stat['personal_fouls']

            final = float(points-total_penalty)

            print(f"""
Name: {stat['name']}
Points: {points}
Penalties: {total_penalty}
Stock Price: {final}
--------------------""")

            update_cursor = con.cursor()
            update = f"""UPDATE nba_players_stats
                         SET stock_price = {final}
                         WHERE playerid = {stat['playerid']};"""
            update_cursor.execute(update)
            print("Stock Price Updated")
        
        except ZeroDivisionError:
            continue

    except mysql.connector.Error as error:
            print("Failed to insert record into table {}".format(error))

    finally:
        if con.is_connected():
            con.close()
            print("MySQL connection is closed")

And here is the output (well some of it, the table has over 600 rows):

Name: Bradley Beal
Points: 483.0054878048781
Penalties: 150.3
Stock Price: 332.7054878048781
--------------------
Stock Price Updated

Name: Otto Porter Jr.
Points: 351.4387921022067
Penalties: 64.6
Stock Price: 286.83879210220675
--------------------
Stock Price Updated

Name: Garrett Temple
Points: 264.6698635799917
Penalties: 68.0
Stock Price: 196.66986357999173
--------------------
Stock Price Updated

Right now all values in the stock_price is "NULL", when it should be updated to the Stock Price. How would I be able to update the table for each player with the stock price?

0 Answers0