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_returns_customers_from_ny_la_with_orders(self):
        """Returns all customers from 'New York' or 'Los Angeles' who have at least one order."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        expected = [
            {'customer_id': 1, 'name': 'Alice Johnson', 'city': 'New York'},
            {'customer_id': 2, 'name': 'Bob Smith', 'city': 'Los Angeles'}
        ]
        for exp in expected:
            mask = (
                (result_df['customer_id'] == exp['customer_id']) &
                (result_df['name'] == exp['name']) &
                (result_df['city'] == exp['city'])
            )
            _dynamic_test(
                self,
                mask.any(),
                f"Customer {exp['name']} from {exp['city']} with orders is returned.",
                f"Customer {exp['name']} from {exp['city']} with orders is missing from result."
            )

    def test_does_not_return_customers_from_other_cities(self):
        """Does not return customers from other cities, even if they have orders."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        other_city_customers = [3, 4, 5]
        for cid in other_city_customers:
            mask = (result_df['customer_id'] == cid)
            _dynamic_test(
                self,
                not mask.any(),
                f"Customer from city other than NY/LA (customer_id={cid}) not returned.",
                f"Customer from city other than NY/LA (customer_id={cid}) was incorrectly returned."
            )

    def test_does_not_return_ny_la_customers_with_no_orders(self):
        """Does not return customers from 'New York' or 'Los Angeles' if they have no orders."""
        # All NY/LA customers in data have orders, so let's insert a new one without orders
        self.cursor.execute("""
            INSERT INTO customers (customer_id, name, city) VALUES (6, 'Test User', 'New York')
        """)
        self.connection.commit()
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        mask = (result_df['customer_id'] == 6)
        _dynamic_test(
            self,
            not mask.any(),
            "Customer from NY with no orders is not returned.",
            "Customer from NY with no orders was incorrectly returned."
        )
        self.cursor.execute("DELETE FROM customers WHERE customer_id = 6;")
        self.connection.commit()

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

実際のシナリオを通じて、SQLにおけるサブクエリの記述と応用の技術を習得します。本モジュールでは、サブクエリの理論と実践、SELECT、WHERE、FROM句での利用、相関サブクエリについて解説します。

注文、顧客、商品に焦点を当てたeコマースデータセットを使用してサブクエリを探求します。サブクエリを活用してインサイトを抽出し、ビジネス上の課題を解決する方法を学びます。

チャレンジ：特定の都市からの注文がある顧客

解答