import unittest
import psycopg2
import pandas as pd

class TestTask(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_unique_continents_and_order(self):
        """Check that the query returns all unique continents in ascending order."""
        with open("query.sql", "r") as file:
            query = file.read()

        # Reference solution
        solution_query = """
            SELECT DISTINCT continent FROM country ORDER BY continent;
        """
        self.cursor.execute(solution_query)
        expected_rows = self.cursor.fetchall()
        expected_continents = [row[0] for row in expected_rows]

        # User query
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        user_continents = [row[0] for row in user_rows]

        # Check all expected continents are present
        condition_all_present = user_continents == expected_continents
        if not condition_all_present:
            failure_msg = (
                f"Expected continents in ascending order: {expected_continents}, "
                f"but got: {user_continents}."
            )
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition_all_present,
            "Returned all unique continents in ascending order.",
            failure_msg
        )

    def test_no_duplicates(self):
        """Check that there are no duplicate continents in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        continents = [row[0] for row in rows]
        unique_continents = set(continents)
        condition_no_duplicates = len(continents) == len(unique_continents)
        if not condition_no_duplicates:
            failure_msg = (
                f"Duplicate continents found in result: {continents}"
            )
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition_no_duplicates,
            "No duplicate continents in the result.",
            failure_msg
        )

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 All Continents and Sort Them in Ascending Order

解答