def test_existing_2(self):
"""Q2: Function called with table ids existing (2)"""
self.cur.execute('CREATE TABLE ids (name TEXT)')
self.cur.execute("INSERT INTO ids VALUES ('hello')")
self.con.commit()
SQLLab.populate_table()
self.test_empty()
def test_empty(self):
"""Q2: Function called on an empty database"""
SQLLab.populate_table()
self.cur.execute("""SELECT * FROM ids""")
self.assertListEqual(self.cur.fetchall(), [(1, 'Oski'), (2, 'Is'),
(3, 'The'), (4, 'Best')],
'Entries do not equal to expected')
def test_q4(self):
"""Q4: Main function"""
database_util.testDatabase()
self.assertEqual(SQLLab.field_popularity(), [(1, 'Engineering'),
(1, 'Investments'),
(2, 'HR'), (2, 'IT'),
(2, 'Sales')])
def test_q5(self):
"""Q5: Main function"""
database_util.testDatabase()
self.assertListEqual(SQLLab.max_salary_title(),
[(4, 'John', 'Cena', 'Sales', 100.01),
(2, 'Rachel', 'Green', 'HR', 50.0),
(3, 'Ross', 'Geller', 'IT', 49.99),
(7, 'John', 'Watson', 'Investments', 39.0),
(0, 'Harry', 'Truman', 'Engineering', 17.1)])
def test_q6a(self):
"""Q6a: Main function"""
self.cur.execute(SQLLab.full_outer_join())
self.assertListEqual(self.cur.fetchall(), [
(0, 'Harry', 'Truman', None, None), (1, 'John', 'Doe', None, None),
(2, 'Rachel', 'Green', None, None),
(3, 'Ross', 'Geller', None, None),
(4, 'John', 'Cena', 'Cant C.', 'Mei'),
(5, 'Jonathan', 'Joestar', 'Will', 'Zeppeli'),
(6, 'Sherlock', 'Holmes', 'Inspector', 'Lestrade'),
(7, 'John', 'Watson', 'Sherlock', 'Holmes'),
(8, None, None, 'Oski', 'Bear'),
(9, None, None, 'John', 'Hammond'),
(10, None, None, 'Richard', 'Feynman'),
(11, None, None, 'George', 'Lucas')
])
def test_existing_1(self):
"""Q2: Function called with table ids existing (1)"""
SQLLab.populate_table()
self.test_empty()
def test_q3(self):
"""Q3: Main function"""
database_util.testDatabase()
self.assertEqual(SQLLab.find_john(),
[(1, 'John', 'Doe'), (4, 'John', 'Cena'), (7, 'John', 'Watson')])
def setUp(self):
self.con = sqlite.connect("database.db")
self.cur = self.con.cursor()
SQLLab.hello_world()
def test_cursor_uses_same_connection(self):
"""PreQ1: Cursor uses the same connection"""
con, cur = SQLLab.open_new_connection(':memory:')
self.assertEqual(con, cur.connection,
"Cursor doesn't share the same connection")
def test_q6b(self):
"""Q6b: Main function"""
self.assertTrue(SQLLab.full_outer_join_df().equals(
pd.read_sql(SQLLab.full_outer_join(), self.con)))
def test_errors(self):
"""Q1: Table doesn't error after multiple calls"""
try:
SQLLab.create_table()
except sqlite.OperationalError:
self.fail('Threw an error after calling a multiple time')
def setUp(self):
self.con = sqlite.connect('database.db')
self.cur = self.con.cursor()
SQLLab.create_table()