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

Dieser Kurs ist ideal für Anfänger, die bereit sind, die Welt von SQL zu erkunden. Ob Sie gerade erst mit dem Datenbankmanagement beginnen oder SQL für Ihre Anwendungsentwicklungsprojekte nutzen möchten, dieser Kurs behandelt die Grundlagen. Sie lernen schnell, das volle Potenzial von SQL zu nutzen – von der Abfrage und Verwaltung von Daten bis hin zur nahtlosen Integration in reale Anwendungen. Am Ende des Kurses verfügen Sie über das nötige Selbstvertrauen und die Fähigkeiten, um praktische Probleme mit SQL zu lösen und Ihren Entwicklungsprozess zu optimieren.

In diesem Abschnitt erfahren Sie, wie die SQL-SELECT-Anweisung verwendet wird, um eine einzelne Tabellenspalte, mehrere Tabellenspalten und alle Tabellenspalten abzurufen. Zudem wird erläutert, wie eindeutige Werte zurückgegeben werden.

In diesem Abschnitt untersuchen wir, wie Daten, die von einer Abfrage zurückgegeben werden, mithilfe der ORDER BY-Klausel organisiert und priorisiert werden können. Diese Klausel wird am Ende einer SELECT-Anweisung platziert und ermöglicht es, die Ergebnisse nach einer oder mehreren Spalten entweder aufsteigend oder absteigend zu sortieren, was eine aussagekräftigere und leichter interpretierbare Datenausgabe ermöglicht.

In diesem Abschnitt lernen wir, wie man mit der WHERE-Klausel der SELECT-Anweisung zurückgegebene Daten filtert. Wir behandeln das Testen auf Gleichheit, Ungleichheit, Wertebereiche größer und kleiner sowie NULL-Werte.

In diesem Abschnitt untersuchen wir, wie Sie Ihre Suchbedingungen durch die Kombination von WHERE-Klauseln mit den Operatoren AND und OR verfeinern können. Außerdem behandeln wir, wie der NOT-Operator verwendet werden kann, um bestimmte Ergebnisse auszuschließen.

In diesem Abschnitt erfahren Sie, was SQL-Aggregatfunktionen sind und wie sie verwendet werden, um Tabellendaten zusammenzufassen.

Herausforderung: Nicht-Europäische Hauptstädte Sortieren

Lösung