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_query_returns_all_columns(self):
        """Check that the query returns all columns from transactions with correct names."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        expected_columns = ["transaction_id", "amount", "status", "payment_method"]
        condition = columns == expected_columns
        _dynamic_test(
            self,
            condition,
            "Query returns all columns with correct names.",
            f"Query does not return the expected columns. Got: {columns}"
        )

    def test_null_payment_method_displays_unknown(self):
        """Check that rows with null payment_method display 'Unknown'."""
        # Insert a row with null payment_method
        self.cursor.execute("INSERT INTO transactions (transaction_id, amount, status, payment_method) VALUES (99, 123.45, 'completed', NULL);")
        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]
        df = pd.DataFrame(rows, columns=columns)
        filtered = df[df['transaction_id'] == 99]
        # Should be exactly one row
        condition = (
            len(filtered) == 1 and
            filtered.iloc[0]['payment_method'] == 'Unknown'
        )
        _dynamic_test(
            self,
            condition,
            "Rows with null payment_method display 'Unknown'.",
            "Row with null payment_method does not display 'Unknown'."
        )
        # Clean up
        self.cursor.execute("DELETE FROM transactions WHERE transaction_id = 99;")
        self.connection.commit()

    def test_non_null_payment_method_unchanged(self):
        """Check that rows with non-null payment_method display original value."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        # Select a known row
        row = df[df['transaction_id'] == 1].iloc[0]
        condition = row['payment_method'] == 'credit_card'
        _dynamic_test(
            self,
            condition,
            "Rows with non-null payment_method display original value.",
            f"Row with non-null payment_method does not display original value: got {row['payment_method']}"
        )

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

Master the use of built-in SQL functions to manipulate strings, dates, numbers, logical values, nulls, and JSON data. This course provides a hands-on approach to understanding and applying SQL functions in real-world scenarios, enhancing your data querying and transformation skills.

Explore SQL's powerful string functions for manipulating and analyzing textual data in databases.

Master SQL's date and time functions to work with temporal data effectively.

Learn to use SQL's numeric functions for calculations and data analysis.

Harness SQL's logical functions to control query flow and make decisions based on data.

Master handling of NULL values in SQL using specialized functions.

Learn to work with JSON data in SQL using specialized functions for querying and manipulation.

Challenge: Handle Missing Payment Methods

Solution

Challenge: Handle Missing Payment Methods

Solution