def test_create_table(self): sql_runner = SQLRunner() table = sql_runner.execute_create_file() planets = table.execute( "SELECT name FROM sqlite_master WHERE type='table';").fetchone()[0] results = table.execute("PRAGMA table_info('%s')" % planets).fetchall() columns = [] for el in results: cleaned_col = (el[1], el[2]) columns.append(cleaned_col) # id self.assertEqual(columns[0][0], 'id', 'id not set to Primary Key') self.assertEqual(columns[0][1], 'INTEGER', 'id not set to Primary Key') # name self.assertEqual(columns[1][0], 'name', 'name not set to TEXT') self.assertEqual(columns[1][1], 'TEXT', 'name not set to TEXT') # color self.assertEqual(columns[2][0], 'color', 'color not set to TEXT') self.assertEqual(columns[2][1], 'TEXT', 'color not set to TEXT') # num_of_moons self.assertEqual(columns[3][0], 'num_of_moons', 'num_of_moons not set to INTEGER') self.assertEqual(columns[3][1], 'INTEGER', 'num_of_moons not set to INTEGER') # mass self.assertEqual(columns[4][0], 'mass', 'mass not set to REAL') self.assertEqual(columns[4][1], 'REAL', 'mass not set to REAL')
import unittest from sql_runner import SQLRunner import sys sys.path.insert(0, '..') from select import * sql_runner = SQLRunner() table = sql_runner.execute_create_file() table = sql_runner.execute_seed_file() class TestSQLSelectingData(unittest.TestCase): def test_select_all_columns_and_rows(self): result = [(1, 'Mercury', 'gray', 0, 0.55, 0.0), (2, 'Venus', 'yellow', 0, 0.82, 0.0), (3, 'Earth', 'blue', 1, 1.0, 0.0), (4, 'Mars', 'red', 2, 0.11, 0.0), (5, 'Jupiter', 'orange', 67, 317.9, 0.0), (6, 'Saturn', 'hazel', 62, 95.19, 1.0), (7, 'Uranus', 'light blue', 27, 14.54, 1.0), (8, 'Neptune', 'dark blue', 14, 17.15, 1.0)] self.assertEqual( table.execute(select_all_columns_and_rows()).fetchall(), result) def test_select_name_and_color_of_all_planets(self): result = [('Mercury', 'gray'), ('Venus', 'yellow'), ('Earth', 'blue'), ('Mars', 'red'), ('Jupiter', 'orange'), ('Saturn', 'hazel'), ('Uranus', 'light blue'), ('Neptune', 'dark blue')] self.assertEqual( table.execute(select_name_and_color_of_all_planets()).fetchall(),
import unittest, sys from sql_runner import SQLRunner sys.path.insert(0, '..') from queries import * sql_runner = SQLRunner() cursor = sql_runner.execute_create_file() cursor = sql_runner.execute_seed_file() class TestSQLQueries(unittest.TestCase): def test_select_name_of_player_with_shortest_name(self): result = 'Pavel Bure' self.assertEqual(cursor.execute(select_name_of_player_with_shortest_name()).fetchone()[0], result) def test_select_all_new_york_players_names(self): result = [('Brian Leetch',), ('Jeff Beukeboom',), ('Mark Messier',), ('Adam Graves',), ('Alexei Kovalev',), ('Mike Richter',), ('Patrick Ewing',), ('Charles Oakley',), ('John Starks',), ('Anthony Mason',), ('Derek Harper',)] self.assertEqual(cursor.execute(select_all_new_york_players_names()).fetchall(), result) def test_select_team_name_and_total_goals_scored_for_new_york_rangers(self): result = [('New York Rangers', 21)] self.assertEqual(cursor.execute(select_team_name_and_total_goals_scored_for_new_york_rangers()).fetchall(), result) def test_select_all_games_date_and_info_teams_name_and_score_for_teams_in_nhl(self): result = [('1994-05-31', 'Stanley Cup Finals - Game 1', 'New York Rangers', 2), ('1994-05-31', 'Stanley Cup Finals - Game 1', 'Vancouver Canucks', 3), ('1994-06-02', 'Stanley Cup Finals - Game 2', 'New York Rangers', 3), ('1994-06-02', 'Stanley Cup Finals - Game 2', 'Vancouver Canucks', 1), ('1994-06-04', 'Stanley Cup Finals - Game 3', 'New York Rangers', 5), ('1994-06-04', 'Stanley Cup Finals - Game 3', 'Vancouver Canucks', 1), ('1994-06-07', 'Stanley Cup Finals - Game 4', 'New York Rangers', 4), ('1994-06-07', 'Stanley Cup Finals - Game 4', 'Vancouver Canucks', 2), ('1994-06-09', 'Stanley Cup Finals - Game 5', 'New York Rangers', 3), ('1994-06-09', 'Stanley Cup Finals - Game 5', 'Vancouver Canucks', 6), ('1994-06-11', 'Stanley Cup Finals - Game 6', 'New York Rangers', 1), ('1994-06-11', 'Stanley Cup Finals - Game 6', 'Vancouver Canucks', 4), ('1994-06-14', 'Stanley Cup Finals - Game 7', 'New York Rangers', 3), ('1994-06-14', 'Stanley Cup Finals - Game 7', 'Vancouver Canucks', 2)] self.assertEqual(cursor.execute(select_all_games_date_and_info_teams_name_and_score_for_teams_in_nhl()).fetchall(), result) def test_select_date_info_and_total_points_for_highest_scoring_nba_game(self): result = [('1994-06-12', 'NBA Finals - Game 3', 182)] self.assertEqual(cursor.execute(select_date_info_and_total_points_for_highest_scoring_nba_game()).fetchall(), result)