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 get_query_result(self):
        with open("query.sql", "r") as f:
            query = f.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        return pd.DataFrame(rows, columns=columns)

    def test_columns_present_and_order(self):
        """Check that the output columns are exactly name, capital, continent in the correct order."""
        df = self.get_query_result()
        expected_columns = ['name', 'capital', 'continent']
        condition = list(df.columns) == expected_columns
        _dynamic_test(
            self,
            condition,
            "The output columns are correct and in the right order.",
            f"Expected columns {expected_columns}, but got {list(df.columns)}."
        )

    def test_no_european_countries(self):
        """Ensure that no row in the result has continent='Europe'."""
        df = self.get_query_result()
        has_europe = (df['continent'] == 'Europe').any()
        _dynamic_test(
            self,
            not has_europe,
            "No European countries are present in the result.",
            "There are countries from Europe in your result, but they should be excluded."
        )

    def test_all_non_european_countries_included(self):
        """Check that all non-European countries are included in the result."""
        # Reference data (remove trailing whitespace in "Japan ")
        all_countries = [
            {"name": "Japan", "capital": "Tokyo", "continent": "Asia"},
            {"name": "Mexico", "capital": "Mexico City", "continent": "North America"},
            {"name": "United States", "capital": "Washington", "continent": "North America"},
            {"name": "Thailand", "capital": "Bangkok", "continent": "Asia"},
            {"name": "Paraguay", "capital": "Asunción", "continent": "South America"},
            {"name": "Philippines", "capital": "Manila", "continent": "Asia"},
            {"name": "New Zealand", "capital": "Wellington", "continent": "Oceania"}
        ]
        df = self.get_query_result().copy()
        # Strip whitespace from all string columns for comparison
        for col in ['name', 'capital', 'continent']:
            df[col] = df[col].astype(str).str.strip()
        missing = []
        for country in all_countries:
            mask = (
                (df['name'] == country['name']) &
                (df['capital'] == country['capital']) &
                (df['continent'] == country['continent'])
            )
            if not mask.any():
                missing.append(country)
        condition = len(missing) == 0
        if not condition:
            failure_msg = f"Missing non-European countries: {missing}"
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "All non-European countries are included in the result.",
            failure_msg
        )

    def test_sorted_by_capital(self):
        """Ensure that the result is sorted by capital (ascending)."""
        df = self.get_query_result()
        capitals = df['capital'].tolist()
        condition = capitals == sorted(capitals)
        _dynamic_test(
            self,
            condition,
            "The result is sorted by capital in ascending order.",
            f"The capitals are not sorted: {capitals}"
        )

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):
    import re
    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

SELECTやWHEREを使用したデータの取得やフィルタリング、ORDER BYによる結果の並べ替え、COUNT、AVG、SUMなどの集約関数による集計まで、SQLの基本を学びます。実践的な課題を通じて、実際のデータセットに自信を持ってクエリを実行するスキルを身につけます。

SQLのSELECT文を使用して、テーブルから単一列、複数列、すべての列を取得する方法を学習します。重複しない行の取得や、クエリで返される結果数の制限方法についても解説します。

ORDER BY句を使用してクエリ結果を整理する方法を学びます。1つまたは複数の列でデータを並べ替え、昇順または降順で並べ替え方向を制御し、より明確な出力を実現します。



WHERE句を使用してクエリ結果をフィルタリングする方法を学びます。等価性および非等価性のテスト、値の範囲チェック、データ内のNULL値の識別について解説します。

WHERE句とANDおよびOR演算子を組み合わせて検索条件を絞り込む方法を学びます。NOT演算子を使用して、クエリから特定の結果を除外する方法についても解説します。



SQLの集約関数を使用してテーブルデータを要約する方法を学びます。AVGおよびSUMで平均値や合計値を計算し、COUNTで行数をカウントし、MAXおよびMINで最大値や最小値を取得する方法を習得します。



チャレンジ：ヨーロッパ以外の首都の並べ替え

解答