def account():
form = UpdateAccountForm()
if form.validate_on_submit():
if form.picture.data:
picture_file = save_picture(form.picture.data)
logged_in_user[0].image_file = picture_file
logged_in_user[0].username = form.username.data
logged_in_user[0].email = form.email.data
# current_user.image_file = picture_file
# current_user.username = form.username.data
# current_user.email = form.email.data
cursor.execute(
f"update users set user_name='{logged_in_user[0].username}', user_email='{logged_in_user[0].email}' where user_id='{logged_in_user[0].id}'"
)
# cursor.execute("update users set user_name=%s, user_email=%s where user_id=%s",
# (current_user.username, current_user.email, current_user.id))
db.commit()
flash('Your account has been updated!', 'success')
return redirect(url_for('account'))
elif request.method == 'GET':
form.username.data = logged_in_user[0].username
form.email.data = logged_in_user[0].email
# form.username.data = current_user.username
# form.email.data = current_user.email
image_file = url_for('static',
filename='profile_pics/' +
logged_in_user[0].image_file)
# image_file = url_for('static', filename='profile_pics/' + current_user.image_file)
return render_template('account.html',
title='Account',
image_file=image_file,
form=form)
def post(post_id):
cursor.execute(f"select * from posts where post_id='{post_id}'")
p = cursor.fetchone()
if p is not None:
post = Post(p[0], p[1], p[2], p[3], p[4])
return render_template('post.html', title=post.title, post=post)
else:
abort(404)
def home():
cursor.execute("select * from posts")
results = cursor.fetchall()
posts = []
for p in results:
post = Post(p[0], p[1], p[2], p[3], p[4])
posts.append(post)
posts.sort(key=lambda post: post.date_posted, reverse=True)
return render_template("home.html", posts=posts)
def delete_post(post_id):
cursor.execute(f"select * from posts where post_id='{post_id}'")
p = cursor.fetchone()
if p is not None:
if p[4] != current_user.id:
abort(403)
cursor.execute(f"delete from posts where post_id='{p[0]}'")
db.commit()
flash('Your post has been deleted!', 'success')
return redirect(url_for('home'))
def save_picture(form_picture):
random_hex = secrets.token_hex(8)
_, f_ext = os.path.splitext(form_picture.filename)
picture_fn = random_hex + f_ext
picture_path = os.path.join(app.root_path, 'static\profile_pics',
picture_fn)
output_size = (125, 125)
i = Image.open(form_picture)
i.thumbnail = output_size
i.save(picture_path)
cursor.execute(
f"update users set user_img='{picture_fn}' where user_id='{logged_in_user[0].id}'"
)
return picture_fn
def new_post():
form = PostForm()
if form.validate_on_submit():
cursor.execute(
"insert into posts(post_title, post_date, post_content, user_id)"
"values(%s,%s,%s,%s)",
(form.title.data, utc_to_local(
datetime.utcnow()), form.content.data, current_user.id))
db.commit()
flash('Your post has been created!', 'success')
return redirect(url_for('home'))
return render_template('create_post.html',
title='New Post',
form=form,
legend='Create Post')
def season(season):
init_res = [("All", )]
if season == "All":
init_res = init_res
elif season == "Current":
s = datetime.now()
season = s.year
cursor.execute(f"select round from races where year={int(season)}")
res = cursor.fetchall()
init_res += res
else:
cursor.execute(f"select round from races where year={int(season)}")
res = cursor.fetchall()
init_res += res
rounds = [(str(r[0]), str(r[0])) for r in init_res]
return jsonify({"rounds": rounds})
def register():
if current_user.is_authenticated:
return redirect(url_for('home'))
form = RegistrationForm()
if form.validate_on_submit():
hashed_password = bcrypt.generate_password_hash(
form.password.data).decode('utf-8')
user_name = form.username.data
user_email = form.email.data
user_password = hashed_password
cursor.execute(
"insert into users(user_name, user_email, user_password)"
"values(%s,%s,%s)", (user_name, user_email, user_password))
db.commit()
flash('Your account has been created! You are able to log in now.',
'success')
return redirect(url_for('login'))
return render_template("register.html", title='Register', form=form)
def login():
if current_user.is_authenticated:
return redirect(url_for('home'))
form = LoginForm()
if form.validate_on_submit():
cursor.execute(
f"select * from users where user_email = '{form.email.data}'")
user_data = cursor.fetchone()
user = User()
user.id, user.username, user.email, user.password, user.type, user.points_of_week, user.week, user.image_file \
= user_data[0], user_data[1], user_data[2], user_data[3], user_data[4], user_data[5], user_data[6], user_data[7]
if user and bcrypt.check_password_hash(user.password,
form.password.data):
login_user(user, remember=form.remember.data)
logged_in_user.append(user)
next_page = request.args.get('next')
return redirect(next_page) if next_page else redirect(
url_for('home'))
else:
flash('Login Unsuccessful. Please check email and password',
'danger')
return render_template('login.html', title='Login', form=form)
def update_post(post_id):
cursor.execute("select * from posts where post_id=%s", (post_id, ))
p = cursor.fetchone()
if p is not None:
if p[4] != current_user.id:
abort(403)
form = PostForm()
if form.validate_on_submit():
cursor.execute(
f"update posts set post_title='{form.title.data}', post_content='{form.content.data}' where post_id='{p[0]}'"
)
db.commit()
flash('Your post has been updated!', 'success')
return redirect(url_for('post', post_id=p[0]))
elif request.method == 'GET':
form.title.data = p[1]
form.content.data = p[3]
return render_template('create_post.html',
title='Update Post',
form=form,
legend='Update Post')
else:
abort(404)
def race_and_results_queries():
form = form_list[0]
res_type = form.res_type.data
season = form.season.data
round = form.round.data
driver = form.driver.data
const = form.constructor.data
fin_pos = form.fin_pos.data
grid = form.grid.data
fast_lap_rank = form.fast_lap_rank.data
circuit = form.circuit.data
status = form.status.data
res_per = form.res_per.data
page = form.page.data
table = ""
select_c = "select distinct "
from_c = " from "
join_c = ""
where_c = ""
if res_type == "CiI":
table += "Circuit Table"
select_c += "circuits.name, circuits.location, circuits.country, circuits.url "
headings = ["Circuit", "City", "Country", "Info"]
from_c += "circuits "
join_races = "join races on circuits.circuitId = races.circuitId "
is_races_joined = False
join_results = "join results on races.raceId = results.raceId "
is_results_joined = False
join_drivers = "join drivers on results.driverId = drivers.driverId "
join_constructors = "join constructors on results.constructorId = constructors.constructorId "
join_status = "join status on results.statusId = status.statusId "
if season != "All":
join_c += join_races
is_races_joined = True
where_c += f"where races.year = {season} "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if is_races_joined:
join_c += join_results + join_drivers
is_results_joined = True
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
join_c += join_races + join_results + join_drivers
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if is_races_joined:
if is_results_joined:
join_c += join_constructors
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
else:
join_c += join_results + join_constructors
is_results_joined = True
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
else:
join_c += join_races + join_results + join_constructors
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if is_races_joined:
if is_results_joined:
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_races + join_results
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if is_races_joined:
if is_results_joined:
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_races + join_results
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if is_races_joined:
if is_results_joined:
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_races + join_results
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if is_races_joined:
if is_results_joined:
join_c += join_status
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_results + join_status
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_races + join_results + join_status
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
query = select_c + from_c + join_c + where_c
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "CoI":
table += "Constructor Table"
select_c += "constructors.name, constructors.nationality, constructors.url "
headings = ["Constructor", "Nationality", "Info"]
from_c += "constructors "
join_results = "join results on constructors.constructorId = results.constructorId "
is_results_joined = False
join_races = "join races on races.raceId = results.raceId "
is_races_joined = False
join_drivers = "join drivers on results.driverId = drivers.driverId "
join_status = "join status on results.statusId = status.statusId "
join_circuits = "join circuits on races.circuitId = circuits.circuitId "
if season != "All":
join_c += join_results + join_races
is_results_joined = True
is_races_joined = True
where_c += f"where races.year = {season} "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if is_results_joined:
join_c += join_drivers
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
join_c += join_results + join_drivers
is_results_joined = True
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
if is_results_joined:
if is_races_joined:
join_c += join_circuits
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
else:
join_c += join_races + join_circuits
is_races_joined = True
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
else:
join_c += join_results + join_races + join_circuits
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if is_results_joined:
join_c += join_status
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_results + join_status
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
query = select_c + from_c + join_c + where_c
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "D":
table += "Driver Table"
select_c += "concat_ws(' ', drivers.forename, drivers.surname) as name, drivers.number, drivers.nationality, drivers.dob, drivers.url "
headings = ["Name", "Starting Number", "Nationality", "DOB", "Info"]
from_c += "drivers "
join_results = "join results on drivers.driverId = results.driverId "
is_results_joined = False
join_races = "join races on races.raceId = results.raceId "
is_races_joined = False
join_constructors = "join constructors on results.constructorId = constructors.constructorId "
join_status = "join status on results.statusId = status.statusId "
join_circuits = "join circuits on races.circuitId = circuits.circuitId "
if season != "All":
join_c += join_results + join_races
is_results_joined = True
is_races_joined = True
where_c += f"where races.year = {season} "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if is_results_joined:
join_c += join_constructors
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
else:
join_c += join_results + join_constructors
is_results_joined = True
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
if is_results_joined:
if is_races_joined:
join_c += join_circuits
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
else:
join_c += join_races + join_circuits
is_races_joined = True
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
else:
join_c += join_results + join_races + join_circuits
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if is_results_joined:
join_c += join_status
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_results + join_status
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
query = select_c + from_c + join_c + where_c
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "QR":
table += "Qualifying Table"
select_c += "concat_ws(' ', races.year, races.name) as race, qualifying.position, concat_ws(' ', drivers.forename, drivers.surname) as name, constructors.name, qualifying.q1, qualifying.q2, qualifying.q3 "
headings = ["Race", "Pos", "Driver", "Constructor", "Q1", "Q2", "Q3"]
from_c += "qualifying "
join_results = "join results on drivers.driverId = results.driverId "
is_results_joined = False
join_races = "join races on races.raceId = qualifying.raceId "
is_races_joined = False
join_constructors = "join constructors on qualifying.constructorId = constructors.constructorId "
join_status = "join status on results.statusId = status.statusId "
join_circuits = "join circuits on races.circuitId = circuits.circuitId "
join_drivers = "join drivers on qualifying.driverId = drivers.driverId "
join_c += join_drivers + join_constructors + join_races
if season != "All":
where_c += f"where races.year = {season} "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
join_c += join_circuits
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if is_results_joined:
join_c += join_status
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_results + join_status
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
query = select_c + from_c + join_c + where_c + " order by races.year, races.round, qualifying.position "
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "RR":
table += "Results Table"
select_c += "concat_ws(' ', races.year, races.name) as race, results.positionText, concat_ws(' ', drivers.forename, drivers.surname) as name," \
" constructors.name, results.laps, results.grid, results.time, status.status, results.points "
headings = [
"Race", "Pos", "Driver", "Constructor", "Laps", "Grid", "Time",
"Status", "Points"
]
from_c += "results "
join_races = "join races on races.raceId = results.raceId "
join_constructors = "join constructors on results.constructorId = constructors.constructorId "
join_status = "join status on results.statusId = status.statusId "
join_drivers = "join drivers on results.driverId = drivers.driverId "
join_circuits = "join circuits on races.circuitId = circuits.circuitId "
join_c += join_drivers + join_constructors + join_races + join_status
if season != "All":
where_c += f"where races.year = {season} "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
join_c += join_circuits
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
query = select_c + from_c + join_c + where_c + " order by races.year, races.round, results.positionOrder "
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "RS":
table += "Race Table"
select_c += "races.year, races.round, races.name, races.date, circuits.name, circuits.location, circuits.country, races.url "
headings = [
"Season", "Round", "Name", "Date", "Circuit", "City", "Country",
"Info"
]
from_c += "races "
join_c += "join circuits on races.circuitId = circuits.circuitId "
join_results = "join results on races.raceId = results.raceId "
is_results_joined = False
join_constructors = "join constructors on results.constructorId = constructors.constructorId "
join_drivers = "join drivers on results.driverId = drivers.driverId "
join_status = "join status on results.statusId = status.statusId "
order = ""
if season != "All":
where_c += f"where races.year = {season} "
else:
order += " order by races.year, races.round "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if is_results_joined:
join_c += join_drivers
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
join_c += join_results + join_drivers
is_results_joined = True
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if is_results_joined:
join_c += join_constructors
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
else:
join_c += join_results + join_constructors
is_results_joined = True
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if is_results_joined:
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if is_results_joined:
join_c += join_status
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_results + join_status
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
if order == "":
order += " order by races.round "
query = select_c + from_c + join_c + where_c + order
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "SL":
table += "Season Table"
select_c += "seasons.year, seasons.url "
headings = ["Year", "Info"]
from_c += "seasons "
join_races = "join races on seasons.year = races.year "
is_races_joined = False
join_results = "join results on races.raceId = results.raceId "
is_results_joined = False
join_drivers = "join drivers on results.driverId = drivers.driverId "
join_constructors = "join constructors on results.constructorId = constructors.constructorId "
join_circuits = "join circuits on races.circuitId = circuits.circuitId "
join_status = "join status on results.statusId = status.statusId "
order = ""
if season != "All":
where_c += f"where seasons.year = {season} "
if round != "All":
join_c += join_races
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
if is_races_joined:
join_c += join_results + join_drivers
is_results_joined = True
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
join_c += join_races + join_results + join_drivers
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
if is_races_joined:
if is_results_joined:
join_c += join_constructors
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
else:
join_c += join_results + join_constructors
is_results_joined = True
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
else:
join_c += join_races + join_results + join_constructors
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if is_races_joined:
if is_results_joined:
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
else:
join_c += join_races + join_results
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if is_races_joined:
if is_results_joined:
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
else:
join_c += join_races + join_results
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if is_races_joined:
if is_results_joined:
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_results
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
else:
join_c += join_races + join_results
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
if is_races_joined:
join_c += join_circuits
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
else:
join_c += join_races + join_circuits
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if is_races_joined:
if is_results_joined:
join_c += join_status
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_results + join_status
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
else:
join_c += join_races + join_results + join_status
is_races_joined = True
is_results_joined = True
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
if order == "":
order += " order by seasons.year "
query = select_c + from_c + join_c + where_c + order
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings
elif res_type == "FS":
table += "Status Table"
select_c += "status.status, count(results.statusId) as count "
headings = ["Status", "Count"]
from_c += "results "
join_c += "join status on results.statusId = status.statusId "
join_races = "join races on results.raceId = races.raceId "
is_races_joined = False
join_constructors = "join constructors on results.constructorId = constructors.constructorId "
join_drivers = "join drivers on results.driverId = drivers.driverId "
join_circuits = "join circuits on races.circuitId = circuits.circuitId "
order = ""
if season != "All":
join_c += join_races
is_races_joined = True
where_c += f"where races.year = {season} "
if round != "All":
where_c += f"and races.round = {round} "
if driver != "All":
driver_forename, driver_surname = drivers_dict[driver][
0], drivers_dict[driver][1]
join_c += join_drivers
if where_c != "":
where_c += f"and drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
else:
where_c += f"where drivers.forename = '{driver_forename}' and drivers.surname = '{driver_surname}' "
if const != "All":
join_c += join_constructors
if where_c != "":
where_c += f"and constructors.name = '{const}' "
else:
where_c += f"where constructors.name = '{const}' "
if fin_pos != "All":
if where_c != "":
where_c += f"and results.positionText = '{fin_pos}' "
else:
where_c += f"where results.positionText = '{fin_pos}' "
if grid != "All":
if where_c != "":
where_c += f"and results.grid = '{grid}' "
else:
where_c += f"where results.grid = '{grid}' "
if fast_lap_rank != "All":
if where_c != "":
where_c += f"and results.rank = '{fast_lap_rank}' "
else:
where_c += f"where results.rank = '{fast_lap_rank}' "
if circuit != "All":
if is_races_joined:
join_c += join_circuits
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
else:
join_c += join_races + join_circuits
is_races_joined = True
if where_c != "":
where_c += f"and circuits.name = '{circuit}' "
else:
where_c += f"where circuits.name = '{circuit}' "
if status != "All":
if where_c != "":
where_c += f"and status.status = '{status}' "
else:
where_c += f"where status.status = '{status}' "
if order == "":
order += " order by status.statusId "
query = select_c + from_c + join_c + where_c + " group by status.status " + order
cursor.execute(query)
result = cursor.fetchall()
return table, result, headings