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

このコースは、SQLの世界を探求したい初心者に最適です。データベース管理を始めたばかりの方や、アプリケーション開発プロジェクトでSQLを活用したい方にも対応しています。本コースでは、SQLの基本から応用までを網羅しており、データのクエリや管理、実際のアプリケーションへの統合方法までを迅速に学ぶことができます。コース修了時には、SQLを用いた実践的な問題解決能力と開発プロセスの向上に必要なスキルと自信が身につきます。

このセクションでは、SQLのSELECT文を使用して、単一のテーブル列、複数のテーブル列、すべてのテーブル列を取得する方法を学びます。また、一意の値を返す方法についても学習します。

このセクションでは、ORDER BY句を使用してクエリによって返されたデータをどのように整理し、優先順位を付けるかを学びます。SELECT文の末尾に配置されるこの句は、1つまたは複数の列で結果を昇順または降順に並べ替えることができ、より意味のある、解釈しやすいデータ出力を可能にします。

このセクションでは、SELECT文のWHERE句を使用して返されるデータをフィルタリングする方法を学習します。等価性、不等価性、値の範囲による大小比較、NULL値のテスト方法について学びます。

このセクションでは、WHERE句をANDおよびOR演算子と組み合わせて検索条件を絞り込む方法について学びます。また、NOT演算子を使用して特定の結果を除外する方法についても説明します。

このセクションでは、SQLの集約関数とは何か、そしてそれらを使用してテーブルデータを要約する方法について学びます。

チャレンジ：ヨーロッパ以外の首都のソート

解答