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_selects_name_column(self):
        """Check if only the 'name' column is selected."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        condition = columns == ["name"]
        _dynamic_test(
            self,
            condition,
            "Query selects only the 'name' column.",
            f"Query should select only the 'name' column. Columns found: {columns}"
        )

    def test_orders_by_name_ascending(self):
        """Check if results are ordered by name ascending."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        names = [row[0] for row in rows]
        sorted_names = sorted(names, key=lambda x: x.strip())
        condition = names == sorted_names
        _dynamic_test(
            self,
            condition,
            "Results are ordered by name ascending.",
            f"Results are not ordered by name ascending. Got: {names}"
        )

    def test_operates_on_country_table(self):
        """Check if query uses only rows from the country table by comparing count."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT COUNT(*) FROM country;")
        expected_count = self.cursor.fetchone()[0]
        self.cursor.execute(query)
        result_count = len(self.cursor.fetchall())
        condition = result_count == expected_count
        _dynamic_test(
            self,
            condition,
            "Query returns the correct number of rows from country table.",
            f"Query should return {expected_count} rows, got {result_count}."
        )

    def test_no_syntax_error(self):
        """Check that the query executes without syntax errors."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            _dynamic_test(
                self,
                True,
                "Query runs without syntax errors.",
                "Query raised a syntax error."
            )
        except Exception as e:
            _dynamic_test(
                self,
                False,
                "",
                f"Query raised a syntax error: {e}"
            )

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

This module is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications.

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

Challenge: Sort Countries in Ascending Order

Solution