================================================================================
PRACTICAL PYTHON SCRIPTING - CHALLENGE 1 SOLUTION
Chapter 4: Building db_utils.py
Challenge: Test db_utils.py Connection
================================================================================

PROBLEM:
Create a script that:
1. Imports DatabaseConnection from db_utils
2. Attempts to connect using credentials from .env
3. Shows connection status and database info
4. Safely closes the connection

SOLUTION:
================================================================================

import sys
from pathlib import Path

# Add db_utils.py to Python path
db_utils_path = Path(__file__).parent / "db_utils.py"
sys.path.insert(0, str(db_utils_path.parent))

from db_utils import DatabaseConnection

def test_connection():
    """Test database connection using db_utils."""

    print("=" * 60)
    print("TESTING db_utils.py DATABASE CONNECTION")
    print("=" * 60 + "\n")

    # Create connection instance
    print("📋 Initializing DatabaseConnection...\n")
    db = DatabaseConnection()

    # Print configuration (without password)
    print("📋 Configuration:")
    print(f"  Host: {db.config['host']}")
    print(f"  Port: {db.config['port']}")
    print(f"  Database: {db.config['database']}")
    print(f"  User: {db.config['user']}")
    print(f"  Charset: {db.config['charset']}\n")

    # Attempt connection
    print("🔌 Attempting connection...\n")
    if not db.connect():
        print("❌ Connection failed!")
        return False

    # Connection succeeded, get database info
    print("\n📊 Database Information:")

    try:
        cursor = db.connection.cursor()

        # Get MySQL version
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()[0]
        print(f"  MySQL Version: {version}")

        # Get current database
        cursor.execute("SELECT DATABASE()")
        current_db = cursor.fetchone()[0]
        print(f"  Current Database: {current_db}")

        # Get table count
        cursor.execute(
            "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()"
        )
        table_count = cursor.fetchone()[0]
        print(f"  Tables: {table_count}")

        # List tables
        cursor.execute(
            "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME"
        )
        tables = cursor.fetchall()

        print(f"\n  Table List:")
        for table in tables:
            table_name = table[0]

            # Get row count for each table
            try:
                cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
                row_count = cursor.fetchone()[0]
                print(f"    - {table_name:20} ({row_count:6,} rows)")
            except:
                print(f"    - {table_name:20} (error reading count)")

        cursor.close()

        print("\n" + "=" * 60)
        print("✅ DATABASE CONNECTION TEST SUCCESSFUL!")
        print("=" * 60)
        return True

    except Exception as e:
        print(f"❌ Error reading database info: {e}")
        return False

    finally:
        # Always disconnect
        db.disconnect()


if __name__ == "__main__":
    success = test_connection()
    sys.exit(0 if success else 1)

================================================================================

HOW IT WORKS:
================================================================================

1. PATH SETUP
   db_utils_path = Path(__file__).parent / "db_utils.py"
   sys.path.insert(0, str(db_utils_path.parent))

   - Find where db_utils.py is located
   - Add it to Python's import path
   - Allows importing from db_utils module

2. IMPORT DatabaseConnection
   from db_utils import DatabaseConnection

   - Imports the class we created in db_utils.py
   - Can now use it in our test script

3. CREATE INSTANCE
   db = DatabaseConnection()

   - Creates a DatabaseConnection object
   - __init__ loads config from .env automatically

4. SHOW CONFIGURATION
   print(f"Host: {db.config['host']}")

   - Access the config dictionary stored in __init__
   - Shows all settings (except password for security)

5. ATTEMPT CONNECTION
   if not db.connect():
       return False

   - Calls db.connect() method
   - Returns True/False
   - Prints success/failure message automatically

6. GET DATABASE INFO
   cursor = db.connection.cursor()

   - If connected, get a cursor for queries
   - Execute queries to get MySQL info
   - Display results

7. CLEANUP
   finally:
       db.disconnect()

   - finally block ensures disconnect always runs
   - Closes cursor and connection

================================================================================

TESTING THE SOLUTION:
================================================================================

Assuming you have db_utils.py in the same directory:

python test_db_connection.py

Output:
============================================================
TESTING db_utils.py DATABASE CONNECTION
============================================================

📋 Initializing DatabaseConnection...

📋 Configuration:
  Host: localhost
  Port: 3306
  Database: learning_blog
  User: philip
  Charset: utf8mb4

🔌 Attempting connection...

✅ Database connection established

📊 Database Information:
  MySQL Version: 5.7.23-21-log
  Current Database: learning_blog
  Tables: 4

  Table List:
    - page_content        (    12 rows)
    - pages               (    12 rows)
    - subtopics           (     8 rows)
    - subjects            (    13 rows)

============================================================
✅ DATABASE CONNECTION TEST SUCCESSFUL!
============================================================

================================================================================

UNDERSTANDING THE CODE:
================================================================================

Why sys.path?
- Python needs to know where to find db_utils.py
- sys.path is a list of directories Python searches for imports
- insert(0, ...) adds it at the beginning (highest priority)

DatabaseConnection attributes:
- db.config: Dictionary with connection parameters
- db.connection: The actual mysql.connector connection object
- db.connect(): Method to establish connection
- db.disconnect(): Method to close connection

Cursor queries used:
- VERSION(): Returns MySQL server version
- DATABASE(): Returns current database name
- SELECT COUNT(*) FROM information_schema.TABLES: Get table count
- TABLE_NAME FROM information_schema.TABLES: List all tables

try/except in cursor loop:
- Some tables might cause errors
- Wrap each table query in try/except
- Prevents one bad table from stopping the whole script

================================================================================

WHAT THIS TELLS YOU:
================================================================================

If the output shows:
✅ All information displays → db_utils.py is working correctly
❌ Connection failed → Check .env credentials
❌ Error reading database info → Database exists but might be empty

This test verifies:
- .env is being read correctly ✅
- MySQL/MariaDB is running ✅
- Credentials are correct ✅
- Database exists ✅
- Tables exist ✅
- db_utils.py module is working ✅

If any of these fail, this test helps diagnose which part broke.

================================================================================

REAL-WORLD USAGE:
================================================================================

In production, you'd run this as a diagnostic tool:

$ python test_db_connection.py
(check output to verify database is accessible)

Then run your actual database scripts:

$ python rebuild_typescript_course.py

This test script is helpful for:
- Initial setup verification
- Troubleshooting connection issues
- Verifying database state before running operations
- Monitoring database health

================================================================================

KEY TAKEAWAYS:
================================================================================

✅ How to import from db_utils module
✅ How to use DatabaseConnection class
✅ How to handle configuration from .env
✅ How to safely query database info
✅ How to use try/except/finally for cleanup
✅ How to handle queries that might fail

The pattern you've learned here:
1. Create DatabaseConnection instance
2. Connection parameters come from .env (automatic)
3. Call .connect() to establish connection
4. Use the connection to execute queries
5. Always call .disconnect() in finally block

This is the pattern used in all your database scripts!

================================================================================