<filename>tests/integration/murachs_mysql/test_murachs_mysql.py
"""Test solutions to exercises in Murach's MySQL.

###############################################################################
# test_murachs_mysql.py
#
# Revision:     1.00
# Date:         8/2/2021
# Author:       Alex
#
# Purpose:      Runs functional tests on exercises from "Murach's MySQL", 3rd
#               edition by <NAME>.  Queries a MySQL server running in a
#               separate Docker container.
#
###############################################################################
"""

# %% Imports
# Standard system imports

# Related third party imports

# Local application/library specific imports
from murachs_mysql import murachs_mysql as soln


# %% Murach's MySQL Exercises
def test_chapter3_solutions(mysql_connection):
    """Test solutions to chapter 3 exercises in Murach's MySQL."""
    chap3 = soln.chap3_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((122, 3),   # Exercise 3.8, (123 rows, 3 cols) in result
                    (41, 1),    # Exercise 3.9, (41 rows, 1 col) in result
                    (12, 4),    # Exercise 3.10
                    (5, 4),     # Exercise 3.11
                    (11, 4),    # Exercise 3.12
                    (1, 1),     # Exercise 3.13
                    (1, 3)      # Exercise 3.14
                    )
    for solution, (rows, cols) in zip(chap3, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter4_solutions(mysql_connection):
    """Test solutions to chapter 4 exercises in Murach's MySQL."""
    chap4 = soln.chap4_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((114, 22),  # Exercise 4.1, (114 rows, 22 cols) in result
                    (11, 4),    # Exercise 4.2, (11 rows, 4 cols) in result
                    (122, 3),   # Exercise 4.3
                    (118, 5),   # Exercise 4.4
                    (2, 3),     # Exercise 4.5
                    (54, 2),    # Exercise 4.6
                    (122, 2)    # Exercise 4.7
                    )
    for solution, (rows, cols) in zip(chap4, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter5_solutions(mysql_connection):
    """Test solutions to chapter 5 exercises in Murach's MySQL."""
    chap5 = soln.chap5_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    exp_results = (1,           # Exercise 5.1, 1 affected row
                   1,           # Exercise 5.2, 1 affected row
                   1,           # Exercise 5.3
                   1,           # Exercise 5.4
                   2,           # Exercise 5.5
                   1,           # Exercise 5.6
                   1,           # Exercise 5.7
                   0,           # Exercise 5.8
                   2,           # Exercise 5.9, statement 1
                   1            # Exercise 5.9, statement 2
                   )
    for solution, num_rows in zip(chap5, exp_results):
        cursor.execute(solution)            # Execute solution query
        assert cursor.rowcount == num_rows  # Verify number of affected rows


def test_chapter6_solutions(mysql_connection):
    """Test solutions to chapter 6 exercises in Murach's MySQL."""
    chap6 = soln.chap6_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((34, 2),    # Exercise 6.1, (34 rows, 2 cols) in result
                    (34, 2),    # Exercise 6.2, (34 rows, 2 cols) in result
                    (34, 3),    # Exercise 6.3
                    (10, 3),    # Exercise 6.4
                    (10, 3),    # Exercise 6.5
                    (22, 2),    # Exercise 6.6
                    (2, 2),     # Exercise 6.7
                    (40, 4),    # Exercise 6.8
                    (11, 4),    # Exercise 6.9
                    (11, 5),    # Exercise 6.10
                    (5, 3)      # Exercise 6.11
                    )
    for solution, (rows, cols) in zip(chap6, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter7_solutions(mysql_connection):
    """Test solutions to chapter 7 exercises in Murach's MySQL."""
    chap7 = soln.chap7_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((34, 1),    # Exercise 7.1, (34 rows, 1 col) in result
                    (20, 2),    # Exercise 7.2, (20 rows, 2 cols) in result
                    (54, 2),    # Exercise 7.3
                    (6, 4),     # Exercise 7.4
                    (1, 1),     # Exercise 7.5
                    (38, 3),    # Exercise 7.6
                    (34, 4),    # Exercise 7.7
                    (34, 4),    # Exercise 7.8
                    (1, 1)      # Exercise 7.9
                    )
    for solution, (rows, cols) in zip(chap7, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter8_solutions(mysql_connection):
    """Test solutions to chapter 8 exercises in Murach's MySQL."""
    chap8 = soln.chap8_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((114, 4),   # Exercise 8.1, (114 rows, 4 cols) in result
                    (114, 3)    # Exercise 8.2, (114 rows, 3 cols) in result
                    )
    for solution, (rows, cols) in zip(chap8, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter9_solutions(mysql_connection):
    """Test solutions to chapter 9 exercises in Murach's MySQL."""
    chap9 = soln.chap9_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((114, 4),   # Exercise 9.1, (114 rows, 4 cols) in result
                    (0, 0),     # Exercise 9.2, USE ex database
                    (6, 4),     # Exercise 9.2
                    (0, 0),     # Exercise 9.2, USE ap database
                    (122, 6),   # Exercise 9.3
                    (29, 7),    # Exercise 9.4
                    (0, 0),     # Exercise 9.5, USE ap database
                    (5, 3),     # Exercise 9.5
                    (0, 0),     # Exercise 9.5, USE ap database
                    (11, 3)     # Exercise 9.6
                    )
    for solution, (rows, cols) in zip(chap9, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result

<filename>tests/integration/murachs_mysql/test_murachs_mysql.py
"""Test solutions to exercises in Murach's MySQL.

###############################################################################
# test_murachs_mysql.py
#
# Revision:     1.00
# Date:         8/2/2021
# Author:       Alex
#
# Purpose:      Runs functional tests on exercises from "Murach's MySQL", 3rd
#               edition by <NAME>.  Queries a MySQL server running in a
#               separate Docker container.
#
###############################################################################
"""

# %% Imports
# Standard system imports

# Related third party imports

# Local application/library specific imports
from murachs_mysql import murachs_mysql as soln


# %% Murach's MySQL Exercises
def test_chapter3_solutions(mysql_connection):
    """Test solutions to chapter 3 exercises in Murach's MySQL."""
    chap3 = soln.chap3_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((122, 3),   # Exercise 3.8, (123 rows, 3 cols) in result
                    (41, 1),    # Exercise 3.9, (41 rows, 1 col) in result
                    (12, 4),    # Exercise 3.10
                    (5, 4),     # Exercise 3.11
                    (11, 4),    # Exercise 3.12
                    (1, 1),     # Exercise 3.13
                    (1, 3)      # Exercise 3.14
                    )
    for solution, (rows, cols) in zip(chap3, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter4_solutions(mysql_connection):
    """Test solutions to chapter 4 exercises in Murach's MySQL."""
    chap4 = soln.chap4_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((114, 22),  # Exercise 4.1, (114 rows, 22 cols) in result
                    (11, 4),    # Exercise 4.2, (11 rows, 4 cols) in result
                    (122, 3),   # Exercise 4.3
                    (118, 5),   # Exercise 4.4
                    (2, 3),     # Exercise 4.5
                    (54, 2),    # Exercise 4.6
                    (122, 2)    # Exercise 4.7
                    )
    for solution, (rows, cols) in zip(chap4, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter5_solutions(mysql_connection):
    """Test solutions to chapter 5 exercises in Murach's MySQL."""
    chap5 = soln.chap5_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    exp_results = (1,           # Exercise 5.1, 1 affected row
                   1,           # Exercise 5.2, 1 affected row
                   1,           # Exercise 5.3
                   1,           # Exercise 5.4
                   2,           # Exercise 5.5
                   1,           # Exercise 5.6
                   1,           # Exercise 5.7
                   0,           # Exercise 5.8
                   2,           # Exercise 5.9, statement 1
                   1            # Exercise 5.9, statement 2
                   )
    for solution, num_rows in zip(chap5, exp_results):
        cursor.execute(solution)            # Execute solution query
        assert cursor.rowcount == num_rows  # Verify number of affected rows


def test_chapter6_solutions(mysql_connection):
    """Test solutions to chapter 6 exercises in Murach's MySQL."""
    chap6 = soln.chap6_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((34, 2),    # Exercise 6.1, (34 rows, 2 cols) in result
                    (34, 2),    # Exercise 6.2, (34 rows, 2 cols) in result
                    (34, 3),    # Exercise 6.3
                    (10, 3),    # Exercise 6.4
                    (10, 3),    # Exercise 6.5
                    (22, 2),    # Exercise 6.6
                    (2, 2),     # Exercise 6.7
                    (40, 4),    # Exercise 6.8
                    (11, 4),    # Exercise 6.9
                    (11, 5),    # Exercise 6.10
                    (5, 3)      # Exercise 6.11
                    )
    for solution, (rows, cols) in zip(chap6, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter7_solutions(mysql_connection):
    """Test solutions to chapter 7 exercises in Murach's MySQL."""
    chap7 = soln.chap7_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((34, 1),    # Exercise 7.1, (34 rows, 1 col) in result
                    (20, 2),    # Exercise 7.2, (20 rows, 2 cols) in result
                    (54, 2),    # Exercise 7.3
                    (6, 4),     # Exercise 7.4
                    (1, 1),     # Exercise 7.5
                    (38, 3),    # Exercise 7.6
                    (34, 4),    # Exercise 7.7
                    (34, 4),    # Exercise 7.8
                    (1, 1)      # Exercise 7.9
                    )
    for solution, (rows, cols) in zip(chap7, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter8_solutions(mysql_connection):
    """Test solutions to chapter 8 exercises in Murach's MySQL."""
    chap8 = soln.chap8_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((114, 4),   # Exercise 8.1, (114 rows, 4 cols) in result
                    (114, 3)    # Exercise 8.2, (114 rows, 3 cols) in result
                    )
    for solution, (rows, cols) in zip(chap8, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result


def test_chapter9_solutions(mysql_connection):
    """Test solutions to chapter 9 exercises in Murach's MySQL."""
    chap9 = soln.chap9_solutions()          # List of queries
    cursor = mysql_connection.cursor()
    results_size = ((114, 4),   # Exercise 9.1, (114 rows, 4 cols) in result
                    (0, 0),     # Exercise 9.2, USE ex database
                    (6, 4),     # Exercise 9.2
                    (0, 0),     # Exercise 9.2, USE ap database
                    (122, 6),   # Exercise 9.3
                    (29, 7),    # Exercise 9.4
                    (0, 0),     # Exercise 9.5, USE ap database
                    (5, 3),     # Exercise 9.5
                    (0, 0),     # Exercise 9.5, USE ap database
                    (11, 3)     # Exercise 9.6
                    )
    for solution, (rows, cols) in zip(chap9, results_size):
        cursor.execute(solution)            # Execute solution query
        cursor.fetchall()                   # Fetch all results of query
        col_names = cursor.column_names
        assert cursor.rowcount == rows      # Check number of rows in result
        assert len(col_names) == cols       # Check number columns in result

Test solutions to exercises in Murach's MySQL.  ############################################################################### # test_murachs_mysql.py # # Revision:     1.00 # Date:         8/2/2021 # Author:       Alex # # Purpose:      Runs functional tests on exercises from "Murach's MySQL", 3rd #               edition by <NAME>.  Queries a MySQL server running in a #               separate Docker container. # ############################################################################### # %% Imports # Standard system imports # Related third party imports # Local application/library specific imports # %% Murach's MySQL Exercises Test solutions to chapter 3 exercises in Murach's MySQL. # List of queries # Exercise 3.8, (123 rows, 3 cols) in result # Exercise 3.9, (41 rows, 1 col) in result # Exercise 3.10 # Exercise 3.11 # Exercise 3.12 # Exercise 3.13 # Exercise 3.14 # Execute solution query # Fetch all results of query # Check number of rows in result # Check number columns in result Test solutions to chapter 4 exercises in Murach's MySQL. # List of queries # Exercise 4.1, (114 rows, 22 cols) in result # Exercise 4.2, (11 rows, 4 cols) in result # Exercise 4.3 # Exercise 4.4 # Exercise 4.5 # Exercise 4.6 # Exercise 4.7 # Execute solution query # Fetch all results of query # Check number of rows in result # Check number columns in result Test solutions to chapter 5 exercises in Murach's MySQL. # List of queries # Exercise 5.1, 1 affected row # Exercise 5.2, 1 affected row # Exercise 5.3 # Exercise 5.4 # Exercise 5.5 # Exercise 5.6 # Exercise 5.7 # Exercise 5.8 # Exercise 5.9, statement 1 # Exercise 5.9, statement 2 # Execute solution query # Verify number of affected rows Test solutions to chapter 6 exercises in Murach's MySQL. # List of queries # Exercise 6.1, (34 rows, 2 cols) in result # Exercise 6.2, (34 rows, 2 cols) in result # Exercise 6.3 # Exercise 6.4 # Exercise 6.5 # Exercise 6.6 # Exercise 6.7 # Exercise 6.8 # Exercise 6.9 # Exercise 6.10 # Exercise 6.11 # Execute solution query # Fetch all results of query # Check number of rows in result # Check number columns in result Test solutions to chapter 7 exercises in Murach's MySQL. # List of queries # Exercise 7.1, (34 rows, 1 col) in result # Exercise 7.2, (20 rows, 2 cols) in result # Exercise 7.3 # Exercise 7.4 # Exercise 7.5 # Exercise 7.6 # Exercise 7.7 # Exercise 7.8 # Exercise 7.9 # Execute solution query # Fetch all results of query # Check number of rows in result # Check number columns in result Test solutions to chapter 8 exercises in Murach's MySQL. # List of queries # Exercise 8.1, (114 rows, 4 cols) in result # Exercise 8.2, (114 rows, 3 cols) in result # Execute solution query # Fetch all results of query # Check number of rows in result # Check number columns in result Test solutions to chapter 9 exercises in Murach's MySQL. # List of queries # Exercise 9.1, (114 rows, 4 cols) in result # Exercise 9.2, USE ex database # Exercise 9.2 # Exercise 9.2, USE ap database # Exercise 9.3 # Exercise 9.4 # Exercise 9.5, USE ap database # Exercise 9.5 # Exercise 9.5, USE ap database # Exercise 9.6 # Execute solution query # Fetch all results of query # Check number of rows in result # Check number columns in result