================================================================================
PRACTICAL PYTHON SCRIPTING - CHALLENGE 1 SOLUTION
Chapter 3: Database Connections
Challenge: Test Database Connection
================================================================================

PROBLEM:
Create a script that:
1. Loads configuration from .env
2. Attempts to connect to the database
3. Shows success/failure message with error details
4. Safely closes the connection

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

from dotenv import load_dotenv
import os
import sys
import mysql.connector
from mysql.connector import Error

# Load environment variables
load_dotenv()

print("=" * 60)
print("DATABASE CONNECTION TEST")
print("=" * 60 + "\n")

# Get configuration from environment
print("📋 Configuration:")
db_host = os.environ.get('DB_HOST')
db_name = os.environ.get('DB_NAME')
db_user = os.environ.get('DB_USER')
db_pass = os.environ.get('DB_PASS')
db_port = int(os.environ.get('DB_PORT', '3306'))

print(f"  Host: {db_host}:{db_port}")
print(f"  Database: {db_name}")
print(f"  User: {db_user}\n")

# Validate that credentials exist
if not all([db_host, db_name, db_user, db_pass]):
    print("❌ ERROR: Missing database credentials in .env")
    sys.exit(1)

# Attempt connection
print("🔌 Attempting connection...\n")

try:
    connection = mysql.connector.connect(
        host=db_host,
        user=db_user,
        password=db_pass,
        database=db_name,
        port=db_port,
        charset='utf8mb4'
    )

    if connection.is_connected():
        print("✅ DATABASE CONNECTION SUCCESSFUL!\n")

        # Show connection details
        db_info = connection.get_server_info()
        print(f"📊 Server Information:")
        print(f"  MySQL Version: {db_info}")

        # Try a simple query to verify everything works
        cursor = connection.cursor()
        cursor.execute("SELECT DATABASE()")
        current_db = cursor.fetchone()[0]
        print(f"  Current Database: {current_db}")

        # Get row count from a table (if it exists)
        try:
            cursor.execute("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s", (db_name,))
            table_count = cursor.fetchone()[0]
            print(f"  Tables in Database: {table_count}")
        except:
            pass

        cursor.close()
        print()

except Error as e:
    print(f"❌ CONNECTION FAILED!\n")
    print(f"📌 Error Details:")
    print(f"  Error Code: {e.errno}")
    print(f"  Error Message: {e.msg}\n")

    # Provide helpful troubleshooting based on error code
    if e.errno == 1045:
        print("💡 Troubleshooting:")
        print("  - Wrong username or password")
        print("  - Check DB_USER and DB_PASS in .env")
        print("  - Verify the user has access to this database")

    elif e.errno == 1049:
        print("💡 Troubleshooting:")
        print("  - Database doesn't exist")
        print("  - Check DB_NAME in .env")
        print("  - Verify the database was created")

    elif e.errno == 2003:
        print("💡 Troubleshooting:")
        print("  - Cannot connect to server")
        print("  - Check DB_HOST and DB_PORT")
        print("  - Verify MySQL/MariaDB is running")
        print("  - Try: mysql -h localhost -u philip -p")

    elif e.errno == 2006:
        print("💡 Troubleshooting:")
        print("  - Connection timed out")
        print("  - Server may be offline or very slow")
        print("  - Try again in a moment")

    sys.exit(1)

finally:
    # Always close the connection, even if an error occurred
    if 'connection' in locals() and connection.is_connected():
        connection.close()
        print("=" * 60)
        print("✅ Connection closed safely")
        print("=" * 60)

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

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

1. LOAD CONFIGURATION
   load_dotenv()
   db_host = os.environ.get('DB_HOST')
   ...

   - Load .env file
   - Extract all database credentials

2. VALIDATE CREDENTIALS
   if not all([db_host, db_name, db_user, db_pass]):
       sys.exit(1)

   - Check that no required credential is missing
   - Exit early if any are None

3. ATTEMPT CONNECTION
   connection = mysql.connector.connect(...)

   - Use mysql.connector to connect
   - Provide all required parameters
   - charset='utf8mb4' for proper Unicode support

4. CHECK IF CONNECTED
   if connection.is_connected():

   - Verify the connection actually worked
   - is_connected() returns True/False

5. SHOW CONNECTION DETAILS
   db_info = connection.get_server_info()

   - Get MySQL version
   - Execute a test query to verify everything works
   - Show table count in the database

6. ERROR HANDLING
   except Error as e:
       print(f"Error Code: {e.errno}")

   - Catch mysql.connector.Error (not generic Exception)
   - Show error code and message
   - Provide helpful troubleshooting based on error code

7. CLEANUP WITH FINALLY
   finally:
       if 'connection' in locals() and connection.is_connected():
           connection.close()

   - finally block ALWAYS runs, even if errors occurred
   - Check if connection exists and is connected
   - Close it safely

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

UNDERSTANDING ERROR CODES:
================================================================================

Common MySQL Error Codes:

1045 - Access Denied
- Wrong username or password
- User doesn't have permission
- Solution: Verify credentials, check user privileges

1049 - Unknown Database
- Database doesn't exist
- Wrong database name
- Solution: Create database or fix name in .env

2003 - Can't Connect to MySQL Server
- Server is offline
- Wrong hostname
- Wrong port
- Solution: Start MySQL, check host/port

2006 - MySQL Server Has Gone Away
- Connection timed out
- Server restarted
- Solution: Reconnect

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

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

Test 1: With correct credentials
(Assume .env has correct values)

python test_connection.py

Output:
============================================================
DATABASE CONNECTION TEST
============================================================

📋 Configuration:
  Host: localhost:3306
  Database: learning_blog
  User: philip

🔌 Attempting connection...

✅ DATABASE CONNECTION SUCCESSFUL!

📊 Server Information:
  MySQL Version: 8.0.23-0ubuntu0.20.04.1
  Current Database: learning_blog
  Tables in Database: 12

============================================================
✅ Connection closed safely
============================================================

---

Test 2: With wrong password
(Set DB_PASS to something invalid)

Output:
❌ CONNECTION FAILED!

📌 Error Details:
  Error Code: 1045
  Error Message: Access denied for user 'philip'@'localhost' (using password: YES)

💡 Troubleshooting:
  - Wrong username or password
  - Check DB_USER and DB_PASS in .env
  - Verify the user has access to this database

---

Test 3: With server offline
(Stop MySQL server before running)

Output:
❌ CONNECTION FAILED!

📌 Error Details:
  Error Code: 2003
  Error Message: Can't connect to MySQL server on 'localhost:3306'

💡 Troubleshooting:
  - Cannot connect to server
  - Check DB_HOST and DB_PORT
  - Verify MySQL/MariaDB is running
  - Try: mysql -h localhost -u philip -p

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

WHAT EACH PART DOES:
================================================================================

mysql.connector.connect(...)
- Connect to MySQL/MariaDB server
- Returns a connection object if successful
- Raises Error if connection fails

Error
- Exception class from mysql.connector
- Contains errno and msg attributes
- Specific to MySQL errors (not generic exceptions)

connection.is_connected()
- Check if connection is still active
- Returns True/False
- Useful for verifying connection before queries

connection.get_server_info()
- Get the MySQL server version
- Useful for logging/debugging

cursor.execute(query)
- Send a SQL query to the database
- Raises Error if query is invalid

cursor.fetchone()
- Get one row of results
- Returns tuple of values
- Returns None if no results

connection.close()
- Close the connection
- Releases the database connection
- MUST be called when done

finally block
- Always executes, even if exceptions occur
- Guarantees cleanup code runs
- Essential for preventing resource leaks

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

ADVANCED: RECONNECTION LOGIC:
================================================================================

In production, connections can drop. Here's how to handle it:

def connect_with_retry(config, max_retries=3):
    """Connect to database, retrying on failure."""
    for attempt in range(max_retries):
        try:
            connection = mysql.connector.connect(**config)
            if connection.is_connected():
                return connection
        except Error as e:
            print(f"Connection attempt {attempt+1} failed: {e}")
            if attempt < max_retries - 1:
                print(f"Retrying in 2 seconds...")
                time.sleep(2)
            else:
                print(f"Failed after {max_retries} attempts")
                raise

# Usage:
config = {
    'host': db_host,
    'user': db_user,
    'password': db_pass,
    'database': db_name
}

try:
    connection = connect_with_retry(config)
    # Use connection
finally:
    connection.close()

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

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

In production code, you'd combine this with your ConfigManager from Chapter 2:

from config import ConfigManager
import mysql.connector
from mysql.connector import Error

config_mgr = ConfigManager()
settings = config_mgr.load()

if not config_mgr.is_valid():
    print("Configuration invalid")
    sys.exit(1)

try:
    connection = mysql.connector.connect(
        host=settings['db_host'],
        user=settings['db_user'],
        password=settings['db_pass'],
        database=settings['db_name'],
        port=settings['db_port']
    )

    if connection.is_connected():
        print("✅ Connected successfully")
        # Use connection
        connection.close()

except Error as e:
    print(f"❌ Connection failed: {e.errno} - {e.msg}")
    sys.exit(1)

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

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

✅ Always use try/except for database connections
✅ Check connection.is_connected() after connecting
✅ Use finally block to ensure close() is always called
✅ Catch mysql.connector.Error (not generic Exception)
✅ Use error codes to provide helpful troubleshooting
✅ Load credentials from .env, never hard-code
✅ Validate credentials exist before attempting connection
✅ Test your database connection before running main code

Connection Checklist:
- Load config from .env ✅
- Validate credentials exist ✅
- Attempt connection with error handling ✅
- Check is_connected() ✅
- Execute test query to verify ✅
- Catch mysql.connector.Error ✅
- Close connection in finally block ✅
- Provide helpful error messages ✅

This pattern is the foundation for all database scripts!

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