Displaying RDS MySQL Data with Python and Flask: A Step-by-Step Guide

Begin by creating a MySQL table named ‘usuarios’ with essential fields such as ‘cod,’ ‘nomeUsuario,’ ’emailUsuario,’ ‘senhaUsuario,’ and ‘dataCadastro.’ The table is designed to store user information with a primary focus on user code, name, and email.

In another post we explained how to set up RDS – AWS RDS with Python: A Comprehensive CRUD Tutorial.

CREATE TABLE `usuarios` (
  `cod` int(11) NOT NULL,
  `nomeUsuario` varchar(255) NOT NULL,
  `emailUsuario` varchar(255) NOT NULL,
  `senhaUsuario` varchar(255) NOT NULL,
  `dataCadastro` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

In this example we’ve put in some random data.

INSERT INTO `usuarios` (`cod`, `nomeUsuario`, `emailUsuario`, `senhaUsuario`, `dataCadastro`) VALUES
(1, 'João Silva', '[email protected]', 'senha123', '2022-01-01'),
(2, 'Maria Oliveira', '[email protected]', 'senha456', '2022-01-02'),
(3, 'Carlos Pereira', '[email protected]', 'senha789', '2022-01-03'),
(4, 'Ana Souza', '[email protected]', 'senhaabc', '2022-01-04');

Now, let’s dive into the Python script that establishes a connection to your RDS instance, executes a query to fetch user data, and renders it using Flask. The index route is configured to display the fetched data on an HTML template.

from flask import Flask, render_template, request, redirect
import mysql.connector

app = Flask(__name__)

db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '',
    'database': 'bdcadastro'
}

conn = mysql.connector.connect(**db_config)

@app.route('/')
def index():
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM usuarios")
    data = cursor.fetchall()
    return render_template('index.html', data=data)

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

Lastly, we’ll explore the HTML template where the magic happens. The template utilizes Jinja2 templating to dynamically display user data in a neat table format.

Create a folder called templates and inside it create an index.html file with the content below.

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Lista de Usuários</title>
    </head>
    <body>
        <h1>Lista de Usuários</h1>
        <table border="1">
            <thead>
                <tr>
                    <th>Código</th>
                    <th>Nome</th>
                    <th>Email</th>
                </tr>
            </thead>
            <tbody>
                {% for user in data %}
                    <tr>
                        <td>{{ user[0] }}</td> <!-- Código -->
                        <td>{{ user[1] }}</td> <!-- Nome -->
                        <td>{{ user[2] }}</td> <!-- Email -->
                    </tr>
                {% endfor %}
            </tbody>
        </table>
    </body>
    </html>

In conclusion, you’ve successfully navigated the intricacies of integrating Amazon RDS MySQL with Python and Flask, bringing your data to life on the web. By creating a robust MySQL table structure and leveraging the power of Flask, you’ve crafted a user-friendly interface that seamlessly retrieves and displays user information.

As you reflect on your journey, remember that the skills you’ve acquired extend beyond this specific project. You now possess a solid foundation for building dynamic web applications that interact with databases, a valuable skill set in the ever-evolving landscape of web development.

Continue exploring and experimenting with Flask’s capabilities, and consider expanding your knowledge to incorporate additional features such as user authentication, data manipulation, and enhanced visualization. The world of Python and Flask development is vast, and with your newfound expertise, you’re well-equipped to embark on more ambitious projects.

Thank you for joining us on this coding adventure. May your future endeavors be filled with innovation, seamless integration, and a touch of Flask magic. Happy coding!

Introduction – Setting Up Your Application

Libraries

Setting the Stage: Your MySQL Table Structure

Flask Application for Data Retrieval: app.py

Crafting the User-Friendly Interface: index.html

Run the Script

Conclusion