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_only_name_column(self):
        """Check that only the 'name' column is selected."""
        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 == ["name"]
        _dynamic_test(
            self,
            condition,
            "Query selects only the 'name' column.",
            f"Query does not select only the 'name' column. Columns selected: {columns}"
        )

    def test_returns_all_country_names(self):
        """Check that all country names are returned and match the initial data."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        result_rows = self.cursor.fetchall()
        result_names = sorted([row[0].strip() for row in result_rows])
        # Expected country names from init script
        expected_names = sorted([
            'Japan', 'Latvia', 'Mexico', 'Netherlands', 'Portugal', 'Sweden', 'Ukraine',
            'United States', 'Thailand', 'Paraguay', 'Philippines', 'New Zealand',
            'Norway', 'Monaco', 'Malta'
        ])
        # Remove trailing spaces from DB values, since 'name' is char(52)
        result_names_norm = [name.rstrip() for name in result_names]
        expected_names_norm = [name.rstrip() for name in expected_names]
        condition = result_names_norm == expected_names_norm
        _dynamic_test(
            self,
            condition,
            "Query returns all country names as expected.",
            f"Query did not return expected country names.\\nExpected: {expected_names_norm}\\nGot: {result_names_norm}"
        )

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 Countries

解答