app.py

from flask import Flask, render_template, url_for
from dotenv import load_dotenv
import os
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import inspect

load_dotenv()

# CREATE A FLASK INSTANCE
app = Flask(__name__)

# Configure database connection
username = os.getenv('DB_USERNAME')
password = os.getenv('DB_PASSWORD')
host = os.getenv('DB_HOST')
database = os.getenv('DB_DATABASE_NAME')
port = os.getenv('DB_PORT')
app.config['SQLALCHEMY_DATABASE_URI'] =f"mysql+mysqlconnector://{username}:{password}@{host}:{port}/{database}"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Initialize SQLAlchemy
db = SQLAlchemy(app)

@app.route('/db_structure')
def db_structure(db=db):
    # inspect the database
    with app.app_context():
        inspector = inspect(db.engine)

    # Get all table names
    table_names = inspector.get_table_names()

    db_dict = dict.fromkeys(table_names, [])

    # Get columns for each table:
    for table in table_names:
        columns = inspector.get_columns(table)
        db_dict[table] = columns

    # add database name to the dictionary
    db_dict['database'] = os.getenv('DB_DATABASE_NAME')
    
    return render_template('db_structure.html', db_dict=db_dict)


@app.route('/')
@app.route('/index')
def index():
    return "<h1>Flask is running!</h1>"

# route with arguments in the url and rendering a template
@app.route('/user/<username>')
def show_username(username):
    return render_template('users.html', username=username)

@app.route('/user_details/<username>')
def show_user_details(username):
    user_details = {
        'username': username,
        'age': 46,
        'location': 'Málaga',
        'full_name': 'Albert McAvenger',
        'occupation': 'Data Analyst'
    }

    return render_template('user_details.html', user_details=user_details)



# run the app
if __name__ == '__main__':
    app.run(debug=True)