Check the site here! The site is hosted on PythonAnywhere

Random Data Generator Description

The data used is random. Here's the key components of the dataset:

• Database Connection: Establishes a connection to the MySQL database using mysql.connector. If the connection is successful, it proceeds to generate and insert fake data; otherwise, it prints an error message.

• Inserting Fake Data into the Customer Table: Generates fake customer details, including names, phone numbers, emails, unique account numbers, and addresses. It ensures that the account numbers are unique within the table before inserting the data.

• Inserting Fake Data into the UtilityCompanies Table: For each utility company, it creates fake company names, descriptions, phone numbers, addresses, and costs. It links each utility company to an existing customer by using their CustomerID.

• Inserting Fake Data into the Contracts Table: Creates fake contracts with names, types (Standard, Premium, Custom), and descriptions.

• Inserting Fake Data into the MainEnergyGrid Table: Associates each entry in the MainEnergyGrid table with a type of energy (Solar, Wind, Hydro, Nuclear) and links to existing contracts and utility companies by their IDs.

• Inserting Fake Data into Specific Energy Grid Tables (ElectricityGrid, GasGrid, WaterGrid): Generates fake data for different types of energy grids, including electricity, gas, and water. The data include specifics such as voltage levels, power capacities, pressure levels, and status indicators relevant to each energy type.

• Inserting Fake Data into the Government Table: Simulates government records associated with contracts, energy grids, utility companies, and customers by creating fake entries that link to the corresponding IDs in other tables.

• Inserting Fake Data into the RMSupplier Table: Generates fake records for renewable material suppliers, including names and addresses, linked to contracts through their IDs.

• Handling Industry-Specific Data (Business and Residential Tables): Creates entries for businesses and residential customers, including the type of business, number of people associated, and linking to existing customers by their IDs.

• Committing Changes and Handling Errors: Each insert operation is followed by a commit to save changes to the database. The script includes error handling to manage duplicate entries and other potential issues that may arise during data insertion.

• Cleanup: Closes the cursor and the database connection once all operations are complete or in case of an exception.

Random Data Generator Code Breakdown

• Database Connection

  Establish a connection to the MySQL database. This block initializes the connection and cursor for executing SQL commands.

  import mysql.connector from faker import Faker # Create a Faker instance fake = Faker() try: conn = mysql.connector.connect( host='localhost', user='root', password='DBMS*fall2023', database='energy_grid' ) cursor = conn.cursor()

• Inserting Fake Data into Customer Table

  Generates and inserts fake data for 10,000 customers, ensuring unique account numbers.

  for _ in range(10000): fake_name = fake.name() fake_phone_number = fake.phone_number()[:10] fake_email = fake.email() fake_account_no = None while True: fake_account_no = fake.random_int(min=1, max=1000000, step=2) query_check = f"SELECT COUNT(*) FROM customer WHERE AccountNo = {fake_account_no}" cursor.execute(query_check) result = cursor.fetchone()[0] if result == 0: break fake_address = f"{fake.street_address()}, {fake.city()}, {fake.state_abbr()} {fake.random_int(min=37600, max=38200)}" fake_address = fake_address.replace(',', ' ').replace('\n', ' ') query_customer = f"INSERT INTO customer (AccountNo, CustomerName, PhoneNo, EmailID, Address) " \ f"VALUES ({fake_account_no}, '{fake_name}', '{fake_phone_number}', '{fake_email}', '{fake_address}')" cursor.execute(query_customer) conn.commit()

• Inserting Fake Data into UtilityCompanies Table

  Links utility companies to existing customers and handles potential duplicate entries.

  cursor.execute("SELECT CustomerID, AccountNo FROM customer") customer_data = {row[0]: row[1] for row in cursor.fetchall()} for _ in range(10000): # Generate fake data for utility companies # Insert query execution # Handle duplicates with try-except conn.commit()

• Inserting Fake Data into Contracts Table

  Creates fake contracts with a variety of types.

  for _ in range(10000): fake_contract_name = fake.company() fake_type_of_contract = fake.random_element(elements=('Standard', 'Premium', 'Custom')) fake_description = fake.text() query_contract = f"INSERT INTO Contracts (ContractName, TypeOfContract, Description) " \ f"VALUES ('{fake_contract_name}', '{fake_type_of_contract}', '{fake_description}')" cursor.execute(query_contract) conn.commit()

• Inserting Fake Data into MainEnergyGrid Table

  Associates energy grid entries with contracts and utility companies.

  for _ in range(10000): # Generate fake data for main energy grid # Insert query execution conn.commit()

• Inserting Fake Data for Specific Energy Grids

  Inserts detailed data for electricity, gas, and water grids, addressing specifics like voltage and pressure levels.

  for _ in range(10000): # Generate and insert fake data for ElectricityGrid, GasGrid, WaterGrid tables # Insert query execution for each grid type conn.commit()

• Cleanup: Closing Connection

  Ensures the cursor and connection are closed properly after all operations are complete.

  if conn.is_connected(): cursor.close() conn.close()

Flask App Description

I've used flask to create the application and matplotlib for the visualizations. Key components and features include:

• Database Configuration and Connection: Utilizes mysql.connector to establish a connection with a MySQL database using specified credentials and database details. This connection is used for executing SQL queries and managing database interactions throughout the application.

• User Authentication with Flask-Login: Implements user authentication using the Flask-Login extension, handling user login, logout, and session management. A User class is defined, extending UserMixin, to represent user-related data and methods for password hashing and verification.

• Web Forms with Flask-WTF: Defines web forms for user registration and login using Flask-WTF, with fields validated for data presence and, in the case of registration, password confirmation.

• SQL Query Execution: Provides routes for executing predefined SQL queries against the database. These queries retrieve and manipulate data related to the energy grid, such as information on customers, suppliers, contracts, and different types of energy grids (electricity, gas, water).

• Data Visualization: Leverages Pandas for data manipulation and Matplotlib for generating visualizations of query results. This includes bar charts and pie charts to represent various statistical data points like contract types, energy production types, and utility company costs. Visualizations are saved as static files and displayed within the web application.

• Web Routes and Templates: Defines Flask routes for handling user registration, login, logout, and displaying the results of various SQL queries. It uses Jinja2 templates for rendering HTML pages that present form interfaces and query results to the user.

• App and Database Teardown: Ensures the MySQL database connection is properly closed when the application shuts down or after handling a request to prevent resource leaks.

Flask Code Breakdown

• User Authentication Setup with Flask-Login:

  login_manager = LoginManager(app) login_manager.login_view = 'login'

  This configures the Flask-Login extension for handling user sessions.

• User Model:

  class Users(UserMixin): def __init__(self, user_id, username, password_hash): ...

  Defines the Users class for managing user data and authentication methods.

• Form Definitions with Flask-WTF:

  class RegistrationForm(FlaskForm): ... class LoginForm(FlaskForm): ...

  These classes define the forms for user registration and login.

• Route for User Login:

  @app.route('/login', methods=['GET', 'POST']) def login(): ...

  This route handles user login, including form processing and authentication.

• SQL Query Execution Routes:

  @app.route('/query1_results') @login_required def query1_results(): ...

  Each route like this executes a specific SQL query and displays the results.

• Data Visualization within Query Routes:

  @app.route('/query4_results') @login_required def query4_results(): ... plt.savefig(plot_filepath)

  In routes designated for queries, data is visualized using Matplotlib and saved to a file for inclusion in the web response.

• Teardown App Context for Closing Database Connection:

  @app.teardown_appcontext def close_connection(exception=None): ...

  This function ensures that the database connection is properly closed after the app context ends.