def a_year_page(year_title): now = datetime.datetime.now() fn = Func(app.config['dsn']) rc = Raceinfos(app.config['dsn']) year_id = fn.get_id("years", year_title) if year_id is None: return render_template('404.html', current_time = now.ctime()) rclist = rc.get_raceinfolist(year_title = year_title) return render_template('a_year.html', YearTitle = year_title, RaceList = rclist, current_time = now.ctime())
def a_nation_page(nat_title): now = datetime.datetime.now() fn = Func(app.config['dsn']) nt = Nations(app.config['dsn']) rc = Raceinfos(app.config['dsn']) nat_id = fn.get_id("nations", nat_title) #will be null if unknown title entered nat = nt.get_a_nation(nat_id) if nat is None: return render_template('404.html', current_time = now.ctime()) rclist = rc.get_raceinfolist(nat_id = nat_id) trlist = nt.get_trackfornation(nat_id) return render_template('a_nation.html', Nation = nat, RaceList = rclist, TrackInfoList = trlist, current_time = now.ctime())
class Raceinfos: def __init__(self, cp): self.cp = cp self.fn = Func(cp) return def get_raceinfolist(self, nat_id = None, year_title = None): with dbapi2.connect(self.cp) as connection: cursor = connection.cursor() query = """ SELECT tr.title AS Track, yr.title AS Year, dr1.name AS First, dr2.name AS Second, dr3.name AS Third, nat.title AS Nation, fdr.name AS FastestDr, rc.fastest_time AS FastestLap FROM """ if nat_id is None: query += "raceinfos rc" else: query += "(SELECT * FROM raceinfos WHERE nation_id = '%s') rc" %(str(nat_id)) query +=""" JOIN tracks tr ON tr.id = rc.track_id """ if year_title is None: query += "JOIN years yr ON yr.id = rc.year_id" else: query += "JOIN (SELECT * FROM years WHERE title = '%s') yr ON yr.id = rc.year_id" %(year_title) query +=""" JOIN drivers dr1 ON dr1.id = rc.dr1_id JOIN drivers dr2 ON dr2.id = rc.dr2_id JOIN drivers dr3 ON dr3.id = rc.dr3_id JOIN nations nat ON nat.id = rc.nation_id JOIN drivers fdr ON fdr.id = rc.fastestdr_id ORDER BY rc.track_id ASC, rc.year_id ASC; """ cursor.execute(query) rows = cursor.fetchall() return rows def delete_raceinfo(self, n_raceinfo): raceinfo = n_raceinfo.split(":") with dbapi2.connect(self.cp) as connection: cursor = connection.cursor() query = "SELECT * FROM tracks WHERE title = '%s'" % (raceinfo[0]) cursor.execute(query) track_id = cursor.fetchone()[0] query = "SELECT * FROM years WHERE title = '%s'" % (raceinfo[1]) cursor.execute(query) year_id = cursor.fetchone()[0] query = "DELETE FROM raceinfos WHERE (track_id = '%s' AND year_id = '%s')" % (str(track_id), str(year_id)) cursor.execute(query) connection.commit() return def add_raceinfo(self, form): track_id = self.fn.get_id("tracks", form.get('Track')) year_id = self.fn.get_id("years", form.get('Year')) dr1_id = self.fn.get_id("drivers", form.get('First')) dr2_id = self.fn.get_id("drivers", form.get('Second')) dr3_id = self.fn.get_id("drivers", form.get('Third')) nation_id = self.fn.get_id("nations", form.get('Nation')) fastestdr_id = self.fn.get_id("drivers", form.get('FastestDr')) fastest_time = form['FastestLap'] if (dr1_id == dr2_id) or (dr1_id == dr3_id) or (dr2_id == dr3_id): return with dbapi2.connect(self.cp) as connection: cursor = connection.cursor() query = "INSERT INTO raceinfos VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s' ,'%s')" % (track_id, year_id, dr1_id, dr2_id, dr3_id, nation_id, fastestdr_id, fastest_time) cursor.execute(query) connection.commit() return def update_raceinfo(self, form): track_id = self.get_id("tracks", form.get('Track')) year_id = self.get_id("years", form.get('Year')) dr1_id = self.get_id("drivers", form.get('First')) dr2_id = self.get_id("drivers", form.get('Second')) dr3_id = self.get_id("drivers", form.get('Third')) nation_id = self.get_id("nations", form.get('Nation')) fastestdr_id = self.get_id("drivers", form.get('FastestDr')) fastest_time = form['FastestLap'] if (dr1_id == dr2_id) or (dr1_id == dr3_id) or (dr2_id == dr3_id): return with dbapi2.connect(self.cp) as connection: cursor = connection.cursor() query = "UPDATE raceinfos SET dr1_id = '{0}', dr2_id = '{1}', dr3_id = '{2}', nation_id = '{3}', fastestdr_id = '{4}', fastest_time = '{5}' WHERE track_id = '{6}' AND year_id = '{7}'".format(dr1_id, dr2_id, dr3_id, nation_id, fastestdr_id, fastest_time, track_id, year_id) cursor.execute(query) connection.commit() return def search_raceinfolist(self, searchtype, form): with dbapi2.connect(self.cp) as connection: cursor = connection.cursor() query = """SELECT tr.title AS Track, yr.title AS Year, dr1.name AS First, dr2.name AS Second, dr3.name AS Third, nat.title AS Nation, fdr.name AS FastestDr, rc.fastest_time AS FastestLap FROM raceinfos rc JOIN tracks tr ON tr.id = rc.track_id JOIN years yr ON yr.id = rc.year_id JOIN drivers dr1 ON dr1.id = rc.dr1_id JOIN drivers dr2 ON dr2.id = rc.dr2_id JOIN drivers dr3 ON dr3.id = rc.dr3_id JOIN nations nat ON nat.id = rc.nation_id JOIN drivers fdr ON fdr.id = rc.fastestdr_id ORDER BY rc.track_id ASC, rc.year_id ASC """ if searchtype == 'winner': query = "SELECT * FROM (" + query + ") AS Derived WHERE Derived.First ILIKE '%%%s%%'" % (form['SearchWinner']) if searchtype == 'track': query = "SELECT * FROM (" + query + ") AS Derived WHERE Derived.Track ILIKE '%%%s%%'" % (form['SearchTrack']) cursor.execute(query) rows = cursor.fetchall() return rows