import unittest
import psycopg2
import pandas as pd
import re

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_row_count(self):
        """Check that the query returns one row for each customer."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT COUNT(*) FROM customers;")
        expected_count = self.cursor.fetchone()[0]
        self.cursor.execute(query)
        result = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(result) == expected_count,
            f"Query returns {expected_count} rows as expected.",
            f"Query should return {expected_count} rows, but returned {len(result)}."
        )

    def test_uppercase_and_length(self):
        """Check that the uppercase_name and name_length columns are correct for each customer."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT name FROM customers;")
        customer_names = [r[0] for r in self.cursor.fetchall()]
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        for i, name in enumerate(customer_names):
            if i >= len(rows):
                _dynamic_test(
                    self,
                    False,
                    "",
                    f"Missing row for customer: {name}"
                )
                continue
            uppercase_name, name_length = rows[i]
            expected_upper = name.upper()
            expected_length = len(name)
            cond_upper = uppercase_name == expected_upper
            cond_length = name_length == expected_length
            _dynamic_test(
                self,
                cond_upper,
                f"Uppercase name is correct for '{name}'.",
                f"Uppercase name incorrect for '{name}': expected '{expected_upper}', got '{uppercase_name}'."
            )
            _dynamic_test(
                self,
                cond_length,
                f"Name length is correct for '{name}'.",
                f"Length incorrect for '{name}': expected {expected_length}, got {name_length}."
            )

    def test_column_names(self):
        """Check that the column names are 'uppercase_name' and 'name_length'."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        cond = (normalize_text(columns[0]) == "uppercase_name" and normalize_text(columns[1]) == "name_length")
        _dynamic_test(
            self,
            cond,
            "Column names are correct.",
            f"Column names should be 'uppercase_name' and 'name_length', but got {columns}."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\s{2,}", " ", text)
    text = re.sub(r"\s*([,:?])\s*", r"\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

Master the use of built-in SQL functions to manipulate strings, dates, numbers, logical values, nulls, and JSON data. This course provides a hands-on approach to understanding and applying SQL functions in real-world scenarios, enhancing your data querying and transformation skills.

Explore SQL's powerful string functions for manipulating and analyzing textual data in databases.

Master SQL's date and time functions to work with temporal data effectively.

Learn to use SQL's numeric functions for calculations and data analysis.

Harness SQL's logical functions to control query flow and make decisions based on data.

Master handling of NULL values in SQL using specialized functions.

Learn to work with JSON data in SQL using specialized functions for querying and manipulation.

Challenge: Standardize Customer Names

Solution