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_columns_and_order(self):
        """Check that the query selects id, name, population in this exact 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"]
        condition = columns == expected_columns
        _dynamic_test(
            self,
            condition,
            "Query selects columns id, name, population in the correct order.",
            f"Query does not select columns in the correct order. Found: {columns}"
        )

    def test_all_rows_present(self):
        """Check that the query returns all rows from the country table and correct values for the columns."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Get expected result using the correct query
        solution_query = "SELECT id, name, population FROM country;"
        self.cursor.execute(solution_query)
        expected_rows = self.cursor.fetchall()
        self.cursor.execute(query)
        result_rows = self.cursor.fetchall()
        # Convert both to sets for order-insensitive comparison
        expected_set = set(expected_rows)
        result_set = set(result_rows)
        condition = expected_set == result_set and len(result_rows) == len(expected_rows)
        _dynamic_test(
            self,
            condition,
            "Query returns all expected rows and values for id, name, population.",
            f"Returned rows do not match expected rows.\\nExpected: {expected_rows}\\nGot: {result_rows}"
        )

    def test_only_three_columns(self):
        """Ensure that only three columns are returned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        condition = len(columns) == 3
        _dynamic_test(
            self,
            condition,
            "Query returns exactly three columns.",
            f"Query should return 3 columns, but returned {len(columns)} columns: {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

This module 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.

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, Their IDs, and Their Populations

Solution