def createDefenseTable(statPage, nameOfTable): #Get all the column info that will be used for this table with all fixing #to be in usable SQL formats statTypeList = ScrapingFunctions.getCorrectPositions(statPage, "Defense") players = ScrapingFunctions.parseToString(statPage) oldStatTypes = ScrapingFunctions.discoverStatTypes(players, str(1), "Defense") ScrapingFunctions.removeStatTypes(players, oldStatTypes) ScrapingFunctions.fixAllDataPlayer(players) allPlayers = players listOfLists = [] listOfLists = listToListOfLists(allPlayers, statTypeList, listOfLists, "Defense") SQLString = getSQLStr(statTypeList) tableHeaders = "CREATE TABLE " + nameOfTable + "(" dbFileName = "ESPN.db" conn = lite.connect('ESPN.db') conn.text_factory = str # set sqlite3 connection to use unicode instead of 8-bit byte strings. #Added to resolve an error with the c.executemany line below. with conn: c = conn.cursor() # Defines cursor dropTableCommand = "DROP TABLE IF EXISTS " + nameOfTable c.execute(dropTableCommand) # Recreate table so we don't have to keep deleting the .db file commandString = addHeadersToTable(statTypeList, tableHeaders) c.execute(commandString) # Create the table commandString = "INSERT INTO " + nameOfTable + " VALUES(" + SQLString + ")" c.executemany(commandString, listOfLists) # Add all values for each player into table print nameOfTable + " Table Created" conn.commit() conn.close()
def createESPNTable(statPage, nameOfTable,year=""): #Get all the column info that will be used for this table with all fixing #to be in userable SQL formats if nameOfTable == "MiscScoring_"+year: statTypeList = ScrapingFunctions.getCorrectPositions(statPage, "MiscScorers") elif nameOfTable == "Kicking_"+year: statTypeList = ScrapingFunctions.getCorrectPositions(statPage, "Kicking") else: statTypeList = ScrapingFunctions.getCorrectPositions(statPage) if nameOfTable == "MiscScoring_"+year: allPlayers = ScrapingFunctions.getAllPages(statPage, "MiscScorers") elif nameOfTable == "Kicking_"+year: allPlayers = ScrapingFunctions.getAllPages(statPage, "Kicking") else: allPlayers = ScrapingFunctions.getAllPages(statPage) listOfLists = [] listOfLists = listToListOfLists(allPlayers, statTypeList, listOfLists) SQLString = getSQLStr(statTypeList) tableHeaders = "CREATE TABLE " + nameOfTable + "(" dbFileName = "ESPN.db" print allPlayers print statTypeList conn = lite.connect('ESPN.db') conn.text_factory = str # set sqlite3 connection to use unicode instead of 8-bit byte strings. #Added to resolve an error with the c.executemany line below. with conn: c = conn.cursor() # Defines cursor dropTableCommand = "DROP TABLE IF EXISTS " + nameOfTable c.execute(dropTableCommand) # Recreate table so we don't have to keep deleting the .db file c.execute(addHeadersToTable(statTypeList, tableHeaders)) # Create the table commandString = "INSERT INTO " + nameOfTable + " VALUES(" + SQLString + ")" print "commandString " + commandString print "listOfLists " + str(listOfLists) c.executemany(commandString, listOfLists) # Add all values for each player into table commandString = "UPDATE " + nameOfTable + " SET POS = LTRIM(RTRIM(POS));" c.execute(commandString) commandString = "UPDATE " + nameOfTable + " SET POS = 'RB' WHERE POS = 'FB';" c.execute(commandString) print nameOfTable + " Table Created" conn.commit() conn.close()
def getDefensiveFantasyPoints(page, team, year): fantasyPoints = ScrapingFunctions.getDefensivePtsYds(page) fantasyPoints = fantasyPoints + getMiscDefenseFantasyPoints(team[1], year) print team[1] + " " + str(fantasyPoints) return fantasyPoints