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?