def getCounts(promotion = '', fighterID = ''):
# get count of events by promotion
if not promotion == '':
return getData("SELECT COUNT(eventID) AS cnt FROM events INNER JOIN library ON events.eventID=library.ID WHERE promotion='%s'" % promotion)
# get count of events by promotion
elif not fighterID == '':
return getData("SELECT COUNT(eventID) AS cnt FROM fights WHERE fighterID='%s'" % fighterID)
def getFighters(searchStr = ''):
# search fighters
if not searchStr == '':
return getData("SELECT DISTINCT fighters.*, COUNT(*) AS cnt FROM fighters INNER JOIN fights ON (fights.fighterID=fighters.fighterID) WHERE (fighters.fighterID LIKE '%s' OR fighters.name LIKE '%s' OR fighters.nickname LIKE '%s' OR fighters.association LIKE '%s' OR fighters.city LIKE '%s' OR fighters.country LIKE '%s') GROUP BY fighters.fighterID ORDER BY fighters.name" % ("%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%"))
# show all fighters
else:
return getData("SELECT DISTINCT fighters.*, COUNT(*) AS cnt FROM fighters INNER JOIN fights ON (fights.fighterID=fighters.fighterID) GROUP BY fighters.fighterID ORDER BY fighters.name")
def getEvents(promotion = '', fighterID = '', searchStr = '', eventID = ''):
# get events by promotion
if not promotion == '':
return getData("SELECT * FROM events INNER JOIN library ON events.eventID=library.ID WHERE promotion='%s' ORDER BY date" % promotion)
# get events by fighter
elif not fighterID == '':
return getData("SELECT events.* FROM events INNER JOIN fights ON events.eventID=fights.eventID WHERE fighterID='%s' ORDER BY date" % fighterID)
# search events
elif not searchStr == '':
return getData("SELECT * FROM events INNER JOIN library ON events.eventID=library.ID WHERE (eventID LIKE '%s' OR title LIKE '%s' OR promotion LIKE '%s' OR date LIKE '%s' OR venue LIKE '%s' OR city LIKE '%s') ORDER BY date" % ("%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%", "%" + searchStr + "%"))
# get single event details
elif not eventID == '':
return getData("SELECT * FROM events INNER JOIN library ON events.eventID=library.ID WHERE eventID='%s'" % eventID)
# show all events
else:
return getData("SELECT * FROM events INNER JOIN library ON events.eventID=library.ID")
def getMissingFighters():
# get list of already scanned fighters
scannedFighterList = getData("SELECT fighterID from fighters")
scannedFighters = []
for x in scannedFighterList:
scannedFighters.append(x['fighterID'])
# get list of all fighters (scanned and unscanned)
allFighterList = getData("SELECT DISTINCT fighterID from fights")
allFighters = []
for x in allFighterList:
allFighters.append(x['fighterID'])
# get list of fighters that need to be scraped
unscannedFighters = []
for fighter in allFighters:
if not fighter in scannedFighters:
unscannedFighters.append(fighter)
## return list of unscanned fighter IDs
return unscannedFighters
def getMissingEvents():
# retrieve list of already scanned events
storedIDList = getData("SELECT DISTINCT eventID FROM events")
storedIDs = []
for x in storedIDList:
storedIDs.append(x['eventID'])
# retrieve list of all events
libraryList = getData("SELECT DISTINCT * FROM library")
libraryIDs = []
for x in libraryList:
libraryIDs.append(x['ID'])
# retrieve list of events that need to be scanned
unscannedEvents = []
for event in libraryIDs:
if not event in storedIDs:
unscannedEvents.append(event)
## return list of IDs of all events which have not yet been scanned
return unscannedEvents
def getPromotions():
# get all promotions
return getData("SELECT DISTINCT promotion FROM events INNER JOIN library ON events.eventID=library.ID ORDER BY promotion")