NBAStatsWriteToSQL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install nba_api"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from nba_api.stats.static import players\n",
    "# get_playfrom nba_api.stats.static import playersers returns a list of dictionaries, each representing a player.\n",
    "nba_players = players.get_active_players()\n",
    "print('Number of players fetched: {}'.format(len(nba_players)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "name = \"\"\n",
    "fantasyStats = []\n",
    "count = 0\n",
    "from nba_api.stats.endpoints import playercareerstats\n",
    "playerIDs = []\n",
    "#Retieve every single NBA player ID for all past and present NBA players\n",
    "for player in nba_players:\n",
    "    playerIDs.append(str(player.get(\"id\")))\n",
    "len(playerIDs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = {\n",
    "    'Host': 'stats.nba.com',\n",
    "    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:61.0) Gecko/20100101 Firefox/61.0',\n",
    "    'Accept': 'application/json, text/plain, */*',\n",
    "    'Accept-Language': 'en-US,en;q=0.5',\n",
    "    'Referer': 'https://stats.nba.com/',\n",
    "    'Accept-Encoding': 'gzip, deflate, br',\n",
    "    'Connection': 'keep-alive',\n",
    "} #Headers for the player stats api call"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Compiles the player stats for a specific season for each player\n",
    "def stats(player,count):\n",
    "    career = playercareerstats.PlayerCareerStats(player_id=playerIDs[player])\n",
    "    careerdict = career.get_dict()\n",
    "    fantasyStatsTemp = []\n",
    "    Team = \"\"\n",
    "    GP = 0\n",
    "    PPG = 0\n",
    "    APG = 0\n",
    "    RPG = 0\n",
    "    BPG = 0\n",
    "    TOPG = 0\n",
    "    FGPercent = 0\n",
    "    FTPercent = 0\n",
    "    ThreesPG = 0\n",
    "    FER = 0 \n",
    "    #These are weights calculated based on previous fantasy stats data to represent the importance or weight of each stat in fasntasy basketball\n",
    "    APGWeight = 5.02\n",
    "    RPGWeight = 2.7\n",
    "    SPGWeight = 17.41\n",
    "    BPGWeight = 19.19\n",
    "    ThreesPGWeight = 10.27\n",
    "    FGPercentWeight = 30.271\n",
    "    FTPercentWeight = 48.803\n",
    "    TOPGWeight = -10.44\n",
    "    \n",
    "    for key in careerdict:\n",
    "        if key == \"resultSets\":\n",
    "            item = careerdict[\"resultSets\"]\n",
    "        \n",
    "    for key2 in item[0]:\n",
    "        if key2 == \"rowSet\":\n",
    "            newItem = item[0]\n",
    "            seasons = newItem[\"rowSet\"]\n",
    "            for season in seasons:\n",
    "                if season[1] == \"2019-20\": #Can change this to any specific season with format example: 2017-18\n",
    "                    for player in nba_players:\n",
    "                        if player.get(\"id\") == season[0]:\n",
    "                            name = player.get(\"full_name\")\n",
    "                    #Retrieve relevant player data and append into a temporary list which is then appended to a 2D list with other players\n",
    "                    Team = season[4]\n",
    "                    GP = season[6]\n",
    "                    PPG = season[26]/GP\n",
    "                    APG = season[21]/GP\n",
    "                    RPG = season[20]/GP\n",
    "                    SPG = season[22]/GP\n",
    "                    BPG = season[23]/GP\n",
    "                    TOPG = season[24]/GP\n",
    "                    FGPercent = season[11]\n",
    "                    FTPercent = season[17]\n",
    "                    ThreesPG = season[12]/GP\n",
    "                    FER = PPG + APG * APGWeight + RPG * RPGWeight + SPG * SPGWeight + BPG * BPGWeight + ThreesPG * ThreesPGWeight + FGPercent * FGPercentWeight + FTPercent * FTPercentWeight + TOPG * TOPGWeight\n",
    "                    fantasyStatsTemp.append(name)\n",
    "                    fantasyStatsTemp.append(Team)\n",
    "                    fantasyStatsTemp.append(GP)\n",
    "                    fantasyStatsTemp.append(PPG)\n",
    "                    fantasyStatsTemp.append(APG)\n",
    "                    fantasyStatsTemp.append(RPG)\n",
    "                    fantasyStatsTemp.append(SPG)\n",
    "                    fantasyStatsTemp.append(BPG)\n",
    "                    fantasyStatsTemp.append(TOPG)\n",
    "                    fantasyStatsTemp.append(FGPercent)\n",
    "                    fantasyStatsTemp.append(FTPercent)\n",
    "                    fantasyStatsTemp.append(ThreesPG)\n",
    "                    fantasyStatsTemp.append(FER)\n",
    "                    fantasyStats.append(fantasyStatsTemp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for count in range(len(playerIDs)):\n",
    "    stats(count,count) \n",
    "    playerIDs.remove(playerIDs[count])\n",
    "    print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(fantasyStats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(playerIDs) #When this list is empty that means all players from that season were added to the fantasyStats list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count = -1\n",
    "size = len(fantasyStats)-1\n",
    "\n",
    "#Loop through the fantasyStats list and check for players with multiple entries per season due to trade\n",
    "for player in fantasyStats:\n",
    "    count += 1\n",
    "    if player[-12] == \"TOT\":\n",
    "        Totals = []\n",
    "        Totals.append(player[-13])\n",
    "        Totals.append(player[-25])\n",
    "        Totals.append(player[-11])\n",
    "        Totals.append(player[-10])\n",
    "        Totals.append(player[-9])\n",
    "        Totals.append(player[-8])\n",
    "        Totals.append(player[-7])\n",
    "        Totals.append(player[-6])\n",
    "        Totals.append(player[-5])\n",
    "        Totals.append(player[-4])\n",
    "        Totals.append(player[-3])\n",
    "        Totals.append(player[-2])\n",
    "        Totals.append(player[-1])\n",
    "        fantasyStats.remove(fantasyStats[count])\n",
    "        fantasyStats.append(Totals)#Append the players totals with his last team of the season and remove the previous entry\n",
    "        print(Totals)\n",
    "\n",
    "count = 0\n",
    "for count in range(size-1):\n",
    "    if fantasyStats[count][0] == fantasyStats[count+1][0]:\n",
    "        fantasyStats.remove(fantasyStats[count])\n",
    "        \n",
    "len(fantasyStats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def split(arr,low,high): \n",
    "    i = (low-1)         #Index of smaller element \n",
    "    splitPoint = arr[high][-1]     \n",
    "  \n",
    "    for j in range(low,high): \n",
    "        if   arr[j][-1] <= splitPoint: \n",
    "            # increment index of smaller element \n",
    "            i = i+1 \n",
    "            arr[i],arr[j] = arr[j],arr[i] \n",
    "  \n",
    "    arr[i+1],arr[high] = arr[high],arr[i+1] \n",
    "    return (i+1) \n",
    "\n",
    "def quickSort(arr,low,high): \n",
    "    if low < high: \n",
    "  \n",
    "        splitIndex = split(arr,low,high) \n",
    "        quickSort(arr, low, splitIndex-1) \n",
    "        quickSort(arr, splitIndex, high) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quickSort(fantasyStats,0,len(fantasyStats)-1)\n",
    "fantasyStats.reverse()\n",
    "f = open(\"Stats.txt\", \"w\")\n",
    "f.write(str(fantasyStats))\n",
    "f.close()\n",
    "fantasyStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "#Connect to the SQL server and show all databases\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\", #Use localhost for local SQL server\n",
    "  user=\"root\",\n",
    "  passwd=\"\",\n",
    ")\n",
    "\n",
    "print(mydb)\n",
    "cursor = mydb.cursor()\n",
    "cursor.execute(\"SHOW DATABASES\")\n",
    "databases = cursor.fetchall() \n",
    "for database in databases:\n",
    "    print(database)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Insert player data into the desired database and table\n",
    "count = 1\n",
    "#INSERT INTO DatabaseName.TabeleName\n",
    "query = \"INSERT INTO players.nba20192020 (Name,Team,GP,PPG,APG,RPG,SPG,BPG,TOPG,FGPercent,FTPercent,3PMPG,FER,Rank) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)\"\n",
    "for player in fantasyStats:\n",
    "    values = (player[0],player[1],player[2],player[3],player[4],player[5],player[6],player[7],\n",
    "              player[8],player[9],player[10],player[11],player[12],count)\n",
    "    cursor.execute(query, values)\n",
    "    mydb.commit()\n",
    "    count += 1\n",
    "    print(cursor.rowcount, \"record inserted\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}