setup_mysql.py

#!/usr/bin/env python3

import subprocess
import os
import sys
import time
import pymysql
from sqlalchemy import create_engine

def check_mysql_installed():
    """Check if MySQL is installed"""
    try:
        if os.name == 'nt':  # Windows
            subprocess.run(['mysql', '--version'], check=True, stdout=subprocess.PIPE)
        else:  # Linux/Mac
            subprocess.run(['which', 'mysql'], check=True, stdout=subprocess.PIPE)
        return True
    except (subprocess.SubprocessError, FileNotFoundError):
        return False

def create_database_and_user():
    """Create database and user for transport application"""
    try:
        # Get MySQL root credentials
        print("Please enter your MySQL root credentials to create the database and user:")
        root_user = input("MySQL root username (usually 'root'): ") or 'root'
        root_pass = input("MySQL root password: ")
        
        # Connect to MySQL server
        connection = pymysql.connect(
            host='localhost',
            user=root_user,
            password=root_pass
        )
        
        try:
            with connection.cursor() as cursor:
                # Create database
                cursor.execute("CREATE DATABASE IF NOT EXISTS transport_orders")
                
                # Create user and grant privileges
                cursor.execute("CREATE USER IF NOT EXISTS 'transport_user'@'localhost' IDENTIFIED BY 'transport_pass'")
                cursor.execute("GRANT ALL PRIVILEGES ON transport_orders.* TO 'transport_user'@'localhost'")
                cursor.execute("FLUSH PRIVILEGES")
                
                print("\n✅ Database 'transport_orders' and user 'transport_user' created successfully.")
        finally:
            connection.close()
            
        return True
    except Exception as e:
        print(f"\n❌ Error creating database: {e}")
        return False

def test_connection():
    """Test connection to the database with transport_user"""
    try:
        # Try to connect with the application user
        connection_string = 'mysql+pymysql://transport_user:transport_pass@localhost/transport_orders'
        engine = create_engine(connection_string)
        connection = engine.connect()
        connection.close()
        
        print("\n✅ Test connection successful with transport_user!")
        print(f"\nConnection string: {connection_string}")
        print("\nUse this connection string in your application.")
        
        # Create environment variable for convenience
        if os.name == 'nt':  # Windows
            print("\nTo set the database URL as an environment variable in Windows CMD:")
            print("set DATABASE_URL=mysql+pymysql://transport_user:transport_pass@localhost/transport_orders")
            print("\nOr in PowerShell:")
            print("$env:DATABASE_URL=\"mysql+pymysql://transport_user:transport_pass@localhost/transport_orders\"")
        else:  # Linux/Mac
            print("\nTo set the database URL as an environment variable:")
            print("export DATABASE_URL=mysql+pymysql://transport_user:transport_pass@localhost/transport_orders")
        
        return True
    except Exception as e:
        print(f"\n❌ Test connection failed: {e}")
        return False

def install_requirements():
    """Install required Python packages"""
    try:
        print("\nInstalling required packages...")
        subprocess.run([sys.executable, '-m', 'pip', 'install', 'pymysql', 'sqlalchemy', 'flask', 'flask-cors', 'streamlit'], check=True)
        print("\n✅ Required packages installed successfully.")
        return True
    except subprocess.SubprocessError as e:
        print(f"\n❌ Failed to install packages: {e}")
        return False

def main():
    """Main function to setup MySQL for transport application"""
    print("==== Transport Order Application - MySQL Setup ====\n")
    
    # Check MySQL installation
    print("Checking MySQL installation...")
    if not check_mysql_installed():
        print("\n❌ MySQL is not installed or not in PATH. Please install MySQL before continuing.")
        print("Download MySQL from: https://dev.mysql.com/downloads/")
        return
    
    print("✅ MySQL is installed.")
    
    # Install Python requirements
    install_requirements()
    
    # Create database and user
    print("\n--- Setting up database and user ---")
    if not create_database_and_user():
        print("\nDatabase setup failed. Please check your MySQL installation and credentials.")
        return
    
    # Test connection with new user
    print("\n--- Testing connection ---")
    test_connection()
    
    print("\n==== Setup Complete ====")
    print("\nYou can now run the application:")
    print("1. Start the backend: python backend.py")
    print("2. Start the frontend: streamlit run frontend.py")
    print("\nMake sure to run the backend first!")

if __name__ == "__main__":
    main()