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
