#!/usr/bin/env python3 """ Python program to access a database Start at the bottom of this file under main I can give more advise on making your database or queries to get data if you need help More information on using the built in sqlite module can be found here: https://docs.python.org/3/library/sqlite3.html """ # Obviously we need the sqlite3 library import sqlite3 def setup_db(): # Need to excplicitly enable primary keys # This is only necessary if you have multiple table and they rely on eachother conn.execute("PRAGMA foreign_keys = ON;") # Delete the table if it exists conn.execute("drop table if exists game;") conn.execute("drop table if exists games;") conn.execute("drop table if exists teams;") # Create a table to store teams conn.execute(""" create table teams( id integer primary key autoincrement, name text ); """) # Create a table for a game # This requires setting up relationships conn.execute(""" create table games( game_id integer primary key autoincrement, h_team_id integer, o_team_id integer, h_points integer, o_points integer, date text, FOREIGN KEY(h_team_id) REFERENCES teams(id), FOREIGN KEY(o_team_id) REFERENCES teams(id) ); """) # When inserting into a table pass null if you have an autoincrementing column # Seed with some sample data # Note that sql requires single quotes surrounding strings conn.execute("insert into teams values(null, 'Boston Celtics');") conn.execute("insert into teams values(null, 'Houston Rockets');") conn.execute("insert into teams values(null, 'Indiana Pacers');") conn.execute("insert into teams values(null, 'Memphis Grizzlies');") conn.execute("insert into teams values(null, 'Miami Heat');") conn.execute("insert into teams values(null, 'Milwaukee Bucks');") # Insert sample game conn.execute("insert into games values(null, 1, 2, 80, 87, '12-01-2019')") conn.execute("insert into games values(null, 3, 4, 99, 80, '12-01-2019')") conn.execute("insert into games values(null, 5, 6, 101, 92, '12-01-2019')") def print_all_teams(): """ This function prints each team id and name. """ print('Teams:') result = conn.execute("select * from teams") for row in result: print(row) def print_all_games(): """ This function prints every game, but the data is not very useful without looking up team names. """ print('Games:') result = conn.execute("select * from games") for row in result: print(row) def print_all_games_with_names(): """ Same as before but also looking up related data using joins. This is why I would recommend keeping a simple database with as few tables as possible. There is probably more than I can cover in one week so talk to me before you get too adventurous. """ print('Games:') result = conn.execute(""" select home.name, h_points, opp.name, o_points, date from games inner join teams home on games.h_team_id = home.id inner join teams opp on games.o_team_id = opp.id; """) for row in result: print(row) def get_game(date): result = conn.execute("select * from games where date = ?", (date,)) for row in result: print(row) def insert_team(name): conn.execute("insert into teams values(?, ?)", (None, name)) if __name__ == '__main__': DB_PATH = 'example.db' # This shows an example of tables contain related data which would probably be best for you to avoid. # I would recommend trying to keep one or a small number of tables. # First we need to open the database, this will create it if it doesn't exist conn = sqlite3.connect(DB_PATH) # Create the db and insert some sample data # Unless changed it will delete all data in the database each time you run this. setup_db() # Printing the data print_all_teams() print_all_games() insert_team('Orlando Magic') # You should always close a database connection when you are done. conn.close()