Flask and SQL application on Ubuntu

Flask: Flask is a lightweight web framework for Python. It is easy to learn and allows you to quickly build web applications. Flask follows the WSGI (Web Server Gateway Interface) standard and provides tools, libraries, and templates to simplify web development.

MySQL: MySQL is a popular open-source relational database management system. It is widely used for managing and organizing data in a structured manner. MySQL supports SQL (Structured Query Language), making it easy to interact with databases.

Python: Python is a popular choice for back-end development due to its readability, versatility, and the availability of various frameworks. Flask and Django are two commonly used Python frameworks for building web applications.

Why Flask:

1. Lightweight and Simple: Flask is a lightweight micro-framework that is simple to understand and easy to get started with. It doesn’t come with unnecessary components, allowing developers to choose and integrate only what they need for their specific project.
2. Flexibility: Flask provides a flexible structure, allowing developers to choose their own tools and libraries for various components like databases, authentication, and templating. This flexibility is beneficial when tailoring the application to specific requirements.
3. Extensive Documentation: Flask has extensive and well-documented resources, making it easy for developers to find solutions to common problems and learn how to use various features. This is especially valuable for beginners.
4. Large Community: Flask has a large and active community of developers. This means there are plenty of tutorials, plugins, and extensions available, and developers can seek help from the community when facing challenges.
5. Widely Used: Flask is widely used in the industry, and many companies and startups choose it for building web applications. This popularity ensures that there is a wealth of knowledge and experience available for developers.

Why SQL:

1. Relational Data Structure: SQL (Structured Query Language) is designed for managing and querying relational databases. Many real-world applications deal with structured data, and SQL provides a powerful and standardized way to interact with relational databases.
2. Data Integrity: SQL databases enforce data integrity through features like constraints, foreign keys, and transactions. This ensures that the data remains accurate and consistent, which is crucial for applications that rely on reliable and structured information.
3. Scalability: SQL databases are scalable, allowing for the efficient management of large datasets and high transaction volumes. This scalability is essential for applications that need to handle a growing user base and increasing amounts of data.
4. ACID Properties: SQL databases adhere to ACID (Atomicity, Consistency, Isolation, Durability) properties, providing a high level of reliability and ensuring that database transactions are processed reliably even in the face of errors or system failures.
5. Standardization: SQL is an industry-standard language for interacting with databases. This standardization means that knowledge and skills acquired while working with one SQL database (e.g., MySQL, PostgreSQL) are often transferable to others.

In summary, Flask is chosen for its simplicity, flexibility, and community support, while SQL is chosen for its suitability in managing structured data, ensuring data integrity, and providing a standardized way to interact with databases. The combination of Flask and SQL is a popular choice for building web applications with a back-end that involves data storage and retrieval.

Installation of Flask

Ubuntu 20.04 ships with Python 3.8. You can verify that Python is installed on your system by typing:

python3 -V

The recommended way to create a virtual environment is by using the venv module, which is provided by the python3-venv package. Run the following command to install the package:

sudo apt install python3-venv

Now create a folder for your flask application

mkdir flask_app && cd flask_app

Create a virtual environment named venv:

python3 -m venv venv

Start the environment:

source venv/bin/activate

Install FLASK:

pip install Flask

Check the installation:

python3 -m flask --version

Output should look like this:

Python 3.8.5
Flask 1.1.2
Werkzeug 1.0.1

Installation of SQL:

To install MySQL on Ubuntu, you can use the package manager apt. Here are the steps to install MySQL on Ubuntu:

1.Update Package Lists:

Before installing any new software, it’s a good practice to update the package lists to get the latest information about available packages. Open a terminal and run:

sudo apt update

2. Install MySQL Server:

Now you can install the MySQL server package. You will be prompted to set a password for the MySQL root user during the installation process.

sudo apt install mysql-server

3. Secure MySQL Installation (Optional but Recommended):

MySQL comes with a script that can help you secure the installation. It will guide you through various security-related configurations.

sudo mysql_secure_installation

Follow the on-screen instructions to set a password for the MySQL root user, remove anonymous users, disallow remote root login, and remove the test database.

4. Start and Enable MySQL Service:

MySQL should start automatically after installation. If not, you can start it manually:

sudo systemctl start mysql

To ensure that MySQL starts on boot, you can enable it:

sudo systemctl enable mysql

5. Check MySQL Status:

You can check the status of the MySQL service to ensure that it’s running:

sudo systemctl status mysql

6. Access MySQL:

You can access the MySQL shell using the following command. You will be prompted to enter the MySQL root password.

mysql -u root -p

Now you have MySQL installed and can start creating databases, tables, and managing users using the MySQL shell or a database management tool.

Remember that securing your MySQL installation is crucial for the safety of your data, so make sure to follow the best practices for database security.

Connect SQL with Python:

To connect to a MySQL database in Python, you can use the mysql-connector-python library, which is an official MySQL driver for Python provided by the MySQL team. Here's a simple example of how to connect to a MySQL database using this library:

If you haven’t installed the mysql-connector-python library yet, you can do so using:

pip install mysql-connector-python

SQL CLI:

Creating a database and creating a Table

SQL connection and executing a command

A Simple Flask Application Program:

main.py

#!/usr/bin/env python
# -*- coding:utf-8 -*-
###
# Filename: /home/sudhir/Documents/flask_app/main.py
# Path: /home/sudhir/Documents/flask_app
# Created Date: Monday, December 18th 2023, 3:29:38 pm
# Author: Sudhirkumar Saminathan
# 
# Copyright (c) 2023 Trinom Digital Pvt Ltd
###
from flask import Flask, render_template, request
import mysql.connector
from dotenv import load_dotenv
import os
load_dotenv()

app = Flask(__name__)

users_table_name = "users"
record_table_name = "records"

conn = mysql.connector.connect(
    host=os.getenv('FLASK_HOST'),
    user=os.getenv('FLASK_USER'),
    password=os.getenv('FLASK_PASSWORD'),
    database=os.getenv('FLASK_DATABASE'),
)
cursor = conn.cursor()

insert_query = f"INSERT INTO {users_table_name} (id, username, password) VALUES (%s, %s, %s);"
data = (10, 'John', 'Dpoe')

query = f"SELECT * FROM {users_table_name};"
cursor.execute(query)
results = cursor.fetchall()

# Fetch and print the results
print(results)

def checkUser(username, password):
    query = f"SELECT * FROM {users_table_name} WHERE username = '{username}' AND password = '{password}'"
    cursor.execute(query)
    result = cursor.fetchall()
    if len(result) > 0:
        return True
    return False

def formatData(results):
    formatted_result = [
        {
            'id': record[0],
            'name': record[1],
            'roll_number': record[2],
            'english_marks': record[3],
            'tamil_marks': record[4],
            'maths_marks': record[5],
            'physics_marks': record[6],
            'chemistry_marks': record[7],
            'botany_marks': record[8],
            'zoology_marks': record[9],
            'average_marks': record[10],
            'total_marks': record[11],
            'position': record[12],
        }
        for record in results
    ]
    return formatted_result

@app.route('/', methods=["POST", "GET"])
def hello_world():
    return render_template('login.html')

@app.route('/login', methods=["POST", "GET"])
def login():
    if request.method == "POST":
        username = request.form.get('username')
        password = request.form.get('password')

        if checkUser(username, password):
            query = f"SELECT * FROM {record_table_name};"
            cursor.execute(query)
            results = cursor.fetchall()
            results = formatData(results)
            return render_template('dashboard.html', records=results)
        else:
            return render_template('login.html')
    else:
        return render_template('login.html')

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

login.html

<!--
Filename: /home/sudhir/Documents/flask_app/templates/index.html
Path: /home/sudhir/Documents/flask_app/templates
Created Date: Monday, December 18th 2023, 3:30:33 pm
Author: Sudhirkumar Saminathan

Copyright (c) 2023 Trinom Digital Pvt Ltd
-->

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Flask App - Login</title>
    <!-- Add any additional styles or scripts here -->
    <style>
        body {
            font-family: Arial, sans-serif;
        }

        header {
            text-align: center;
            padding: 20px;
            background-color: #f0f0f0;
        }

        form {
            max-width: 400px;
            margin: 0 auto;
        }

        label {
            display: block;
            margin-bottom: 8px;
        }

        input {
            width: 100%;
            padding: 10px;
            margin-bottom: 15px;
            box-sizing: border-box;
        }

        button {
            background-color: #4caf50;
            color: white;
            padding: 10px 15px;
            border: none;
            cursor: pointer;
        }

        button:hover {
            background-color: #45a049;
        }
    </style>
</head>
<body>
    <header>
        <h1>Flask App - Login</h1>
    </header>

    <section>
        <form action="/login" method="post">
            <label for="username">Username:</label>
            <input type="text" id="username" name="username" required>

            <label for="password">Password:</label>
            <input type="password" id="password" name="password" required>

            <button type="submit">Login</button>
        </form>
    </section>

    <footer>
        <p>&copy; 2023 Your Flask App</p>
    </footer>
</body>
</html>

dashboard.html

<!--
Filename: /home/sudhir/Documents/flask_app/templates/dashboard.html
Path: /home/sudhir/Documents/flask_app/templates
Created Date: Tuesday, December 26th 2023, 9:29:05 am
Author: Sudhirkumar Saminathan

Copyright (c) 2023 Trinom Digital Pvt Ltd
-->

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Students Marks and Ranks</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }

        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }

        th {
            background-color: #f2f2f2;
        }
    </style>
</head>
<body>
    <h1>Students Marks and Ranks</h1>
    
    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Roll Number</th>
                <th>English Marks</th>
                <th>Tamil Marks</th>
                <th>Maths Marks</th>
                <th>Physics Marks</th>
                <th>Chemistry Marks</th>
                <th>Botany Marks</th>
                <th>Zoology Marks</th>
                <th>Average Marks</th>
                <th>Total Marks</th>
                <th>Ranking</th>
            </tr>
        </thead>
        <tbody>
            <!-- Loop through records and display data -->
            {% for record in records %}
                <tr>
                    <td>{{ record.id }}</td>
                    <td>{{ record.name }}</td>
                    <td>{{ record.roll_number }}</td>
                    <td>{{ record.english_marks }}</td>
                    <td>{{ record.tamil_marks }}</td>
                    <td>{{ record.maths_marks }}</td>
                    <td>{{ record.physics_marks }}</td>
                    <td>{{ record.chemistry_marks }}</td>
                    <td>{{ record.botany_marks }}</td>
                    <td>{{ record.zoology_marks }}</td>
                    <td>{{ record.average_marks }}</td>
                    <td>{{ record.total_marks }}</td>
                    <td>{{ record.position }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>

Running Flask Application:

Exporting the file name with environment variable and running flask app

Output:

Table for users — username and password.

Table for records — student name, roll number and marks

Login Page UI

Display Page UI

About the Author

Sudhirkumar started his journey as a Software Engineer at CodeStax.Ai. He loves to explore multiple domains and loves to solve problems in an efficient manner.

About CodeStax.Ai

At CodeStax.Ai, we stand at the nexus of innovation and enterprise solutions, offering technology partnerships that empower businesses to drive efficiency, innovation, and growth, harnessing the transformative power of no-code platforms and advanced AI integrations.

But the real magic? It’s our tech tribe behind the scenes. If you’ve got a knack for innovation and a passion for redefining the norm, we’ve got the perfect tech playground for you. CodeStax.Ai offers more than a job — it’s a journey into the very heart of what’s next. Join us, and be part of the revolution that’s redefining the enterprise tech landscape.