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')
def test_insert_into(self):
sql_runner = SQLRunner()
table = sql_runner.execute_alter_file()
table_values = sql_runner.execute_insert_file()
test = table_values.execute("SELECT * FROM planets").fetchall()
self.assertEqual(len(test), 9)
def test_insert_into():
sql_runner = SQLRunner()
table = sql_runner.execute_alter_file()
table_values = sql_runner.execute_insert_file()
test = table_values.execute("SELECT * FROM planets").fetchall()
assert len(test) == 9
def test_select_all_above_average_sales(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
result = [
(3, 3, '2018-04-22', 1, 60), (7, 7, '2018-04-23', 1, 80),
(8, 8, '2018-04-21', 2, 90), (9, 9, '2018-04-22', 2, 80),
(10, 10, '2018-04-22', 2, 80), (11, 11, '2018-04-22', 2, 70),
(12, 12, '2018-04-23', 2, 90), (13, 13, '2018-04-23', 2, 80),
(24, 24, '2018-04-21', 5, 100), (26, 26, '2018-04-22', 5, 75),
(28, 8, '2018-04-21', 2, 90), (29, 8, '2018-04-21', 2, 90),
(30, 8, '2018-04-21', 2, 90), (32, 24, '2018-04-21', 5, 100),
(33, 24, '2018-04-21', 5, 100), (34, 24, '2018-04-21', 5, 100)
]
self.assertEqual(
cursor.execute(select_all_above_average_sales()).fetchall(),
result)
def test_cte_name(fn):
query_str = fn()
if "average_sales" in query_str:
return True
else:
return False
self.assertTrue(test_cte_name(use_cte_to_determine_average_sale),
"CTE should be called 'average_sales'")
def test_delete_from(self):
sql_runner = SQLRunner()
table = sql_runner.execute_alter_file()
table_values = sql_runner.execute_insert_file()
deletion = sql_runner.execute_delete_file()
test_delete = deletion.execute("SELECT * FROM planets").fetchall()
self.assertEqual(len(test_delete), 8, "Delete Pluto!")
def test_cte_deletes_duplicates(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
cursor = sql_runner.execute_cte_deletes_duplicates()
result = [(27, )]
self.assertEqual(
cursor.execute("SELECT COUNT(*) FROM sales;").fetchall(), result)
def test_alter_table(self):
sql_runner = SQLRunner()
altered_table = sql_runner.execute_alter_file()
planets = altered_table.execute(
"SELECT name FROM sqlite_master WHERE type='table';").fetchone()[0]
results = altered_table.execute("PRAGMA table_info('%s')" %
planets).fetchall()
self.assertEqual(results[-1][1], 'rings', 'rings not set to BOOLEAN')
self.assertEqual(results[-1][2], 'BOOLEAN', 'rings not set to BOOLEAN')
def test_update_jupiter():
sql_runner = SQLRunner()
table = sql_runner.execute_alter_file()
table_values = sql_runner.execute_insert_file()
update = sql_runner.execute_update_file()
result = 68
assert table_values.execute(
"SELECT num_of_moons FROM planets WHERE name = 'Jupiter';").fetchone(
)[0] == result
def test_correct_above_avg_sales(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
cursor = sql_runner.execute_cte_deletes_duplicates()
result = [
('Manhattan', '2018-04-22', 60), ('Manhattan', '2018-04-23', 80),
('Brooklyn', '2018-04-21', 90), ('Brooklyn', '2018-04-22', 80),
('Brooklyn', '2018-04-22', 80), ('Brooklyn', '2018-04-22', 70),
('Brooklyn', '2018-04-23', 90), ('Brooklyn', '2018-04-23', 80),
('London', '2018-04-21', 100), ('London', '2018-04-22', 75)
]
self.assertEqual(
cursor.execute(correct_above_avg_sales()).fetchall(), result)
def test_use_cte_to_determine_average_sale(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
def test_range(result, val1, val2):
if val1 < result < val2:
return True
else:
return False
result = cursor.execute(
use_cte_to_determine_average_sale()).fetchall()[0][0]
self.assertTrue(test_range(result, 57.2, 57.3))
def test_cte_name(fn):
query_str = fn()
if "average_sales" in query_str:
return True
else:
return False
self.assertTrue(test_cte_name(use_cte_to_determine_average_sale),
"CTE should be called 'average_sales'")
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)
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_insert_file()
cursor = sql_runner.execute_update_file()
class TestUpdateTables(unittest.TestCase):
def test_metta_world_peace(self):
self.assertEqual(
cursor.execute(
"SELECT name FROM players WHERE id = 1;").fetchone()[0],
"Metta World Peace")
def test_chad_ochocinco(self):
self.assertEqual(
cursor.execute(
"SELECT name FROM players WHERE id = 4;").fetchone()[0],
"Chad OchoCinco")