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_selects_correct_columns(self):
        """Check if query selects the correct columns in the correct order."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        expected_columns = ["id", "name", "population", "region"]
        condition = columns == expected_columns
        _dynamic_test(
            self,
            condition,
            "Query selects the correct columns in the correct order.",
            f"Query columns are {columns}, but expected {expected_columns}."
        )

    def test_orders_by_region_desc_population_asc(self):
        """Check if query orders by region descending and then by population ascending."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        # Nulls should come last in DESC order for region
        # Create a key for sorting: region DESC (null last), population ASC (null last)
        def region_key(val):
            # Nulls last for DESC
            return (val["region"] is None, val["region"] if val["region"] is None else -ord(val["region"][0]))
        sorted_df = df.copy()
        # For region DESC (null last), then population ASC
        sorted_df["region_sort"] = sorted_df["region"].apply(lambda x: (x is None, x))
        sorted_df["pop_sort"] = sorted_df["population"].apply(lambda x: (x is None, x))
        # Sort using pandas for comparison
        expected_df = df.sort_values(
            by=["region", "population"],
            ascending=[False, True],
            na_position="last"
        ).reset_index(drop=True)
        # Reset index for both
        sorted_df = sorted_df.sort_values(
            by=["region", "population"],
            ascending=[False, True],
            na_position="last"
        ).reset_index(drop=True)
        condition = expected_df[["id","name","population","region"]].equals(sorted_df[["id","name","population","region"]])
        _dynamic_test(
            self,
            condition,
            "Query orders by region descending and population ascending.",
            "Query does not order by region descending and population ascending."
        )

    def test_returns_expected_rows(self):
        """Check if query returns the expected rows and values based on 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)
        # Build expected result
        self.cursor.execute("""
            SELECT id, name, population, region  
            FROM country 
            ORDER BY region DESC, population;
        """)
        expected_rows = self.cursor.fetchall()
        expected_columns = [desc[0] for desc in self.cursor.description]
        expected_df = pd.DataFrame(expected_rows, columns=expected_columns)
        # Compare all rows and values
        condition = result_df.equals(expected_df)
        _dynamic_test(
            self,
            condition,
            "Query returns the expected rows and values in order.",
            "Query does not return the expected rows and values in order."
        )

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

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

Challenge: Find Countries, IDs, Populations, Regions, and Sort Them

解答