import unittest
import psycopg2
import pandas as pd

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_all_customers_returned(self):
        """Check that all customer rows are returned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        condition = len(rows) == 10
        _dynamic_test(
            self,
            condition,
            "All customers are returned.",
            f"Expected 10 rows, got {len(rows)}."
        )

    def test_domain_column_correct(self):
        """Check that the domain column contains only the part after '@' for each row."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        domains_expected = ["example.com"] * 10
        condition = result_df['domain'].tolist() == domains_expected
        _dynamic_test(
            self,
            condition,
            "Domain column contains correct values.",
            f"Domain column values are incorrect: {result_df['domain'].tolist()}"
        )

    def test_id_name_email_columns_match(self):
        """Check that id, name, and email columns match input data."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        expected_data = [
            (1, 'Alice Johnson', 'alice.johnson@example.com'),
            (2, 'Rajesh Kumar', 'rajesh.kumar@example.com'),
            (3, 'Maria Garcia', 'maria.garcia@example.com'),
            (4, 'Chen Wei', 'chen.wei@example.com'),
            (5, 'Fatima Al-Farsi', 'fatima.alfarsi@example.com'),
            (6, 'John Smith', 'john.smith@example.com'),
            (7, 'Sofia Rossi', 'sofia.rossi@example.com'),
            (8, 'Lucas MÃ¼ller', 'lucas.mueller@example.com'),
            (9, 'Emily Brown', 'emily.brown@example.com'),
            (10, 'Takashi Sato', 'takashi.sato@example.com'),
        ]
        actual_data = list(zip(result_df['id'], result_df['name'], result_df['email']))
        condition = actual_data == expected_data
        _dynamic_test(
            self,
            condition,
            "id, name, and email columns match input data.",
            f"Returned id, name, and email columns do not match input data."
        )

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: Extract Email Domains

Solution

Challenge: Extract Email Domains

Solution