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_selects_population_column_only(self):
        """Check that only the 'population' column is selected from country table."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        condition = columns == ["population"]
        _dynamic_test(
            self,
            condition,
            "Query selects only the 'population' column.",
            f"Query must select only the 'population' column, but columns returned are: {columns}"
        )

    def test_population_values_correct(self):
        """Check that all returned population values match those in the table."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        self.cursor.execute("SELECT population FROM country;")
        expected_rows = self.cursor.fetchall()
        user_df = pd.DataFrame(user_rows, columns=["population"])
        expected_df = pd.DataFrame(expected_rows, columns=["population"])
        user_sorted = user_df.sort_values(by=["population"], na_position='last').reset_index(drop=True)
        expected_sorted = expected_df.sort_values(by=["population"], na_position='last').reset_index(drop=True)
        condition = user_sorted.equals(expected_sorted)
        if not condition:
            diff = pd.concat([expected_sorted, user_sorted]).drop_duplicates(keep=False)
            failure_msg = f"Returned population values do not match expected values. Difference:\\n{diff}"
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "All population values returned correctly.",
            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 the Population of the Countries

解答