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)