Flask MySQL CRUD Application Example

Posted on Mar 30, 2025 in ICT Systems Engineering

Flask Application Setup

This document outlines a basic CRUD (Create, Read, Update, Delete) application built using Python’s Flask framework and a MySQL database.

Dependencies and Initialization

First, import the necessary libraries and initialize the Flask application and MySQL connection.

from flask import Flask, request, render_template, redirect, url_for, flash
from flask_mysqldb import MySQL

# Initialize Flask app (always use __name__ parameter)
app = Flask(__name__)

# Connect to the Database
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'contacto'
mysql = MySQL(app)

# Secret key for protected sessions
app.secret_key = 'clave'

Flask Routes

Define the routes to handle different web requests.

Index Route (Read All)

This route fetches all contacts from the database and displays them on the main page.

# Route handler for the main page ('/')
@app.route('/')
def index():
    cur = mysql.connection.cursor()
    cur.execute('SELECT * FROM contacto')
    data = cur.fetchall()
    # Render the index template, passing contact data
    return render_template('index.html', contactos=data)

Add Contact Route (Create)

This route handles the submission of a new contact via a POST request.

@app.route('/agregar', methods=['POST'])
def add_contact():
    if request.method == 'POST':
        # Get form data
        nombre = request.form['nombre']
        telefono = request.form['telefono']
        correo = request.form['correo']
        
        # Insert data into the database
        cur = mysql.connection.cursor()
        cur.execute('INSERT INTO contacto (nombre, telefono, correo) VALUES (%s, %s, %s)', (nombre, telefono, correo))
        mysql.connection.commit()
        
        # Show success message
        flash('Contacto agregado satisfactoriamente')
        # Redirect back to the index page
        return redirect(url_for('index'))

Edit Contact Route (Read One)

This route fetches a single contact by its ID for editing.

@app.route('/editar/<string:id>')
def edit_contact(id):
    cur = mysql.connection.cursor()
    # Select the contact with the specified ID
    cur.execute('SELECT * FROM contacto WHERE id = %s', (id,))
    data = cur.fetchall()
    # Render the edit template, passing the specific contact's data
    return render_template('editar.html', contacto=data[0])

Update Contact Route (Update)

This route handles the submission of updated contact information via a POST request.

@app.route('/update/<id>', methods=['POST'])
def update(id):
    if request.method == 'POST':
        # Get form data
        nombre = request.form['nombre']
        telefono = request.form['telefono']
        correo = request.form['correo']
        
        # Update data in the database
        cur = mysql.connection.cursor()
        cur.execute('''
            UPDATE contacto 
            SET nombre = %s, correo = %s, telefono = %s 
            WHERE id = %s
        ''', (nombre, correo, telefono, id))
        mysql.connection.commit()
        
        # Show success message
        flash('Contacto editado satisfactoriamente')
        # Redirect back to the index page
        return redirect(url_for('index'))

Delete Contact Route (Delete)

This route deletes a contact based on its ID.

@app.route('/eliminar/<string:id>')
def delete_contact(id):
    cur = mysql.connection.cursor()
    # Delete the contact with the specified ID
    # Note: Using f-string or .format() directly in SQL can be risky (SQL injection).
    # Parameterized queries (like in add/update) are generally safer.
    # However, for this specific case where 'id' comes from the URL pattern,
    # Flask's routing might offer some protection, but caution is advised.
    # A safer approach would be: cur.execute('DELETE FROM contacto WHERE id = %s', (id,))
    cur.execute('DELETE FROM contacto WHERE id = {0}'.format(id))
    mysql.connection.commit()
    
    # Show success message
    flash('Contacto eliminado satisfactoriamente')
    # Redirect back to the index page
    return redirect(url_for('index'))

Application Runner

This block runs the Flask development server if the script is executed directly.

# If the script is run directly, start the server
if __name__ == '__main__':
    # debug=True restarts the server automatically on code changes
    app.run(debug=True, port=5005)

HTML Template Snippets

Below are snippets likely from the Jinja2 templates used (index.html and editar.html).

Index Page Snippet (index.html)

This shows table headers and a loop to display contact data and action links.

 <!-- Likely part of a form or table structure -->
 <!-- Table Headers -->
 <tr>
  <td>NOMBRE</td>
  <td>TELEFONO</td>
  <td>CORREO</td>
  <td>ACCIONES</td>
 </tr>

 <!-- Loop through contacts data passed from Flask -->
 {% for contacto in contactos %}
 <tr>
  <td>{{ contacto.1 }}</td> <!-- Assumes index 1 is name -->
  <td>{{ contacto.2 }}</td> <!-- Assumes index 2 is phone -->
  <td>{{ contacto.3 }}</td> <!-- Assumes index 3 is email -->
  <td>
   <!-- Link to edit route (assuming it exists) -->
   <a href="/editar/{{ contacto.0 }}">Editar</a> 
   <!-- Link to delete route -->
   <a href="/eliminar/{{ contacto.0 }}">Eliminar</a>
  </td>
 </tr>
 {% endfor %}

 <!-- Potentially a submit button text from an add form -->
 <!-- button type="submit">Guardar</button -->

Edit Page Snippet (editar.html)

This likely shows the text for a save button within the edit form.

 <!-- Potentially a submit button text from the edit form -->
 <!-- button type="submit">Guardar</button -->