def show_golf_course_par5_holes(database_name):
"""
Show a list of golf course names with the total par
and the hole number and par for each hole where the
par is equal to 5:
"""
print("\nShow the Par 5 Hole Numbers For Each Golf Course\n")
# Get a cursor for the connection
dbhelper = GolfTourDatabaseHelper(database_name)
database_connection = dbhelper.get_connection(database_name)
c = database_connection.cursor()
# Create SQL query
sql = '''
select course_name, course_total_par,
hole_number, hole_par
from GolfCourse join Hole
on course_id = hole_course_id
where hole_par = 5
'''
# Execute query and display results
for row in c.execute(sql):
print(row)
print()
# Close the cursor and the database connection
c.close()
database_connection.close()
def show_tournaments_for_golfer_Jo(database_name):
"""
Show a list of tournaments and golfer names played by golfers
whose name begins with Jo:
"""
print("\nTournaments Played by Golfer's Whose Name Begins With Jo\n")
# Get a cursor for the connection
dbhelper = GolfTourDatabaseHelper(database_name)
database_connection = dbhelper.get_connection(database_name)
c = database_connection.cursor()
# Create SQL query
sql = '''
select golfer_name, tourn_name
from TournGolfer
join Tournament on
tg_tourn_id = tourn_id
join Golfer on
tg_golfer_id = golfer_id
where golfer_name like 'Jo%'
order by golfer_name
'''
# Execute query and display results
for row in c.execute(sql):
print(row)
print()
# Close the cursor and the database connection
c.close()
database_connection.close()
def show_my_query(database_name):
"""
Show data from the golf tour database
"""
print("\nData from my query\n")
# Get a cursor for the connection
dbhelper = GolfTourDatabaseHelper(database_name)
database_connection = dbhelper.get_connection(database_name)
c = database_connection.cursor()
# Create SQL query
sql = '''
select tourn_name, golfer_name, golfer_city
from Tournament
join TournGolfer on tourn_id = tg_tourn_id
join Golfer on tg_golfer_id = golfer_id
where golfer_city != 'Raleigh' and tourn_name = 'Raleigh 1'
'''
# Execute query and display results
for row in c.execute(sql):
print(row)
print()
# Close the cursor and the database connection
c.close()
database_connection.close()
def show_my_query(database_name):
"""
Show data from the golf tour database
"""
print("\nData from my query\n")
# Please provide your code here from Discussion Board Assignment
dbhelper = GolfTourDatabaseHelper(database_name)
database_connection = dbhelper.get_connection(database_name)
c = database_connection.cursor()
sql = '''
select distinct golfer_name, tourn_name
from GolferRoundScores
join TournGolfer on
grs_tourn_golfer_id = tg_id
join Golfer on
tg_golfer_id = golfer_id
join Tournament on
tg_tourn_id = tourn_id
where tourn_name LIKE 'WTCC%'
order by tourn_name
'''
for row in c.execute(sql):
print(row)
print()
# Close the cursor and the database connection
c.close()
database_connection.close()
def show_tourn_scores_top5_Apex3(database_name):
"""
Show the total tournament scores for the top
five golfers, who played the 'Apex 3' tournament
"""
print("\nTotal Scores For Top 5 Golfers in Apex 3 Tournament\n")
# Get a cursor for the connection
dbhelper = GolfTourDatabaseHelper(database_name)
database_connection = dbhelper.get_connection(database_name)
c = database_connection.cursor()
# Create SQL query
sql = '''
select golfer_name, tourn_name, sum (grs_total_score) as total
from GolferRoundScores
join TournGolfer on
grs_tourn_golfer_id = tg_id
join Golfer on
tg_golfer_id = golfer_id
join Tournament on
tg_tourn_id = tourn_id
where tourn_name = 'Apex 3'
group by grs_tourn_golfer_id
order by tourn_name, total
limit 5
'''
# Execute query and display results
for row in c.execute(sql):
print(row)
print()
# Close the cursor and the database connection
c.close()
database_connection.close()
def main():
"""
Algorithm:
1. Initialize the input and output file names
2. Initialize the database and table names for output
3. Call create_golf_courses function, passing in the the input
file name, and retrieving the returned golf_course_list,
a list of GolfCourse objects containing information for 5 golf courses
4. Call create_holes function, passing in the golf_course_holes_dict
and retrieving the returned holes_list,
a list of Hole objects containing information for 90 golf course holes
5. Call create_golfers function, passing in the input
file name, and retrieving the returned golfer_list,
a list of Golfer objects containing information for 30 golfers
6. Write out the class objects to files from:
crate_golf_courses, create_holes, create_golfers
---------------------------------------------------------
7. Call create_tournaments function, passing in the input
file name, and retrieving the returned tournament_list,
a list of Tournament objects containing information for 15 tournaments
and a tournament golfers dictionary, tournament_golfers_dict,
whose key is the tourn_id and the value is the list of golfers
playing in that tournament
8. Call create_rounds function, passing in the tournament_list from above,
and retrieving the returned rounds_list,
a list of Round objects containing information for 45 tournament rounds
9. Call create_tourn_golfers function, using the tournament_golfers_dict
and the golfer_list from above, retrieving the returned tourn_golfers_list
a list of TournGolfer objects containing information for 225 tournament golfers
10. Write out the class objects to files from:
create_tournaments, create_rounds, create_tourn_golfers
---------------------------------------------------------
11. Call create_golfer_scores function, passing in the round scores input
file name, golfer_list returned from the create_golfers
function, tournament_list returned from the create_tournaments
function, and tourn_golfers_list returned from the
create_tourn_golfers function, and retrieving the
returned golfer_scores_list
12. Write out the class objects to files from:
create_golfer_scores
---------------------------------------------------------
13. Remove the database, if it already exists
14. Create a GolfTourDatabaseHelper object
15. Use the GolfTourDatabaseHelper object to create
the database and to create the tables.
16. Use the GolfTourDatabaseHelper object to populate database tables
17. Close the database connection
---------------------------------------------------------
18. Write the following functions to query the database
show_golf_course_last3_holes (database_name)
show_tourn_scores_top5_Apex3 (database_name)
show_golf_course_par5_holes (database_name)
show_tournaments_for_golfer_Jo (database_name)
"""
print("Wake Golf Tour Project 1")
# 1. Initialize the input and output file names
golf_courses_infile = "golfCoursesInput.csv"
golfers_infile = "golfersInput.csv"
tournaments_infile = "tournamentsInput.csv"
golfer_scores_infile = "roundScoresInput.csv"
golf_courses_file = "golfCourses.csv"
holes_file = "holes.csv"
golfers_file = "golfers.csv"
tournaments_file = "tournaments.csv"
tourn_golfers_file = "tournGolfers.csv"
rounds_file = "rounds.csv"
golfer_scores_file = "golferRoundScores.csv"
# 2. Initialize the database and table names for output
database_name = "WakeGolfTour.db"
golf_courses_table = "GolfCourse"
holes_table = "Hole"
golfers_table = "Golfer"
tournaments_table = "Tournament"
tourn_golfers_table = "TournGolfer"
rounds_table = "Round"
golfer_scores_table = "GolferRoundScores"
# 3. Call create_golf_courses function, passing in the input
# file name, and retrieving the returned golf_courses_list,
# a list of 5 golf course objects and the golf_course_holes_dict,
# containing information about the holes for each of the golf courses
golf_course_list, golf_course_holes_dict = create_golf_courses(
golf_courses_infile)
# 4. Call create_holes function, passing in the golf_course_holes_dict
# and retrieving the returned holes_list,
# a list of Hole objects containing information for 90 golf course holes
holes_list = create_holes(golf_course_holes_dict)
# 5. Call create_golfers function, passing in the input
# file name, and retrieving the returned golfer_list,
# a list of 30 golfer objects
golfer_list = create_golfers(golfers_infile)
# 6. Write out the lists returned from the create functions:
# create_golf_courses, create_golfers, create_tournaments
write_objs_to_file(golf_courses_file, golf_course_list)
write_objs_to_file(holes_file, holes_list)
write_objs_to_file(golfers_file, golfer_list)
# 7. Call create_tournaments function, passing in the input
# file name and golf_course_list, retrieving the returned tournament_list,
# a list of 15 tournament objects, and a dictionary with the tourn_id as the key
# and a list of golfers for that tournament as the value
tournament_list, tourn_golfers_dict = create_tournaments(
tournaments_infile, golf_course_list)
# 8. Call create_rounds function, passing in the input
# file name and the tournament_list, retrieving the returned rounds_list,
# a list of Round objects
rounds_list = create_rounds(tournament_list)
# 9. Call create_tourn_golfers function, using tourn_golfers_dict
# and the golfers_list, retrieving the returned tourn_golfers_list,
# a list of TournGolfer objects
tourn_golfers_list = create_tourn_golfers(tourn_golfers_dict, golfer_list)
# 10. Write out the lists returned from the create functions:
# create_holes, create_rounds, create_tourn_golfers
write_objs_to_file(tournaments_file, tournament_list)
write_objs_to_file(rounds_file, rounds_list)
write_objs_to_file(tourn_golfers_file, tourn_golfers_list)
# 11. Call create_golfer_scores function, passing in the
# golfer_scores_list returned from the read_golfer_scores
# function, golfers_list returned from the read_golfers
# function, tourns_list returned from the create_tournaments
# function, rounds_list returned from the create_rounds
# function, and the tourn_golfers_list returned from the
# create_tourn_golfers function, and retrieving the
# returned golfer_scores_list
golfer_scores_list = create_golfer_scores(golfer_scores_infile,
golfer_list, tournament_list,
rounds_list, tourn_golfers_list)
# 12. Write out the class objects to a file from:
# create_golfer_scores
write_objs_to_file(golfer_scores_file, golfer_scores_list)
print()
# 13. Remove the database, if it already exists
if os.path.exists(database_name):
os.remove(database_name)
# 14. Create a GolfTourDatabaseHelper object
db_helper = GolfTourDatabaseHelper(database_name)
# 15. Use the GolfTourDatabaseHelper object to create
# the database and to create the tables.
db_helper.create_database()
# 16. Use the GolfTourDatabaseHelper object to populate database tables
db_helper.save_to_database(golf_courses_table, golf_courses_file)
db_helper.save_to_database(holes_table, holes_file)
db_helper.save_to_database(golfers_table, golfers_file)
db_helper.save_to_database(tournaments_table, tournaments_file)
db_helper.save_to_database(tourn_golfers_table, tourn_golfers_file)
db_helper.save_to_database(rounds_table, rounds_file)
db_helper.save_to_database(golfer_scores_table, golfer_scores_file)
# 17. Close the database connection
db_helper.close_connection()
# 18. Write the following functions to query the database
show_golf_course_last3_holes(database_name)
show_tourn_scores_top5_Apex3(database_name)
show_golf_course_par5_holes(database_name)
show_tournaments_for_golfer_Jo(database_name)