Flask + MySQL REST API (Training Project)

A small RESTful API built with Flask and a MySQL database (local MySQL Server + MySQL Workbench). It exposes basic User and Message endpoints and is suitable as a starter or training base.

---

What It Does

• Users: create a user and fetch by id or email.
• Messages: create, fetch by id, and delete.
• Thin route layer calls controller methods, which defer to DAO/database logic (you can swap implementations).

Routes in this repo include:

• POST /users — create user
• GET /users/<user_id> — get user by id
• GET /users/email/<email> — get user by email
• POST /messages — send/create message
• GET /messages/<m_id> — get message by id
• DELETE /messages/<m_id> — delete message

---

Requirements

• Python 3.10+
• MySQL Server (local) and MySQL Workbench (for convenience)
• Python packages:

  pip install flask mysql-connector-python python-dotenv

  (You may swap mysql-connector-python for pymysql or SQLAlchemy if preferred.)

---

Quick Start

1. Clone and install

git clone https://github.com/YOUR_USERNAME/python-flask-mysql-rest-api.git
cd python-flask-mysql-rest-api
python -m venv .venv
# Windows: .venv\Scripts\activate
# macOS/Linux:
source .venv/bin/activate
pip install -r requirements.txt  # if you create one
# or install minimal deps:
pip install flask mysql-connector-python python-dotenv

2. Create MySQL database (example)

Use MySQL Workbench (or CLI) to create a schema and tables. Example starter DDL:

CREATE DATABASE IF NOT EXISTS appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE appdb;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sender FOREIGN KEY (sender_id) REFERENCES users(id),
  CONSTRAINT fk_receiver FOREIGN KEY (receiver_id) REFERENCES users(id)
);

3. Configure environment variables

Create a .env file (do not commit real credentials):

FLASK_ENV=development
FLASK_APP=app
DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=your_mysql_user
DB_PASSWORD=your_mysql_password
DB_NAME=appdb

Your DAO layer should read these (e.g., with os.environ and dotenv.load_dotenv()), open a MySQL connection, and run queries.

4. Run the API

python -m flask run --debug
# or specify host/port:
# flask run --host=0.0.0.0 --port=5000

Server listens on http://127.0.0.1:5000/ by default.

---

Example Requests

Create user

POST /users
Content-Type: application/json

{
  "name": "Ada Lovelace",
  "email": "ada@example.com"
}

Response 201 Created:

{"id": 1, "name": "Ada Lovelace", "email": "ada@example.com"}

Get user by email

GET /users/email/ada@example.com

Response 200 OK:

{"id": 1, "name": "Ada Lovelace", "email": "ada@example.com"}

Send a message

POST /messages
Content-Type: application/json

{
  "sender_id": 1,
  "receiver_id": 2,
  "content": "Hello, world!"
}

Response 201 Created (example):

{"id": 7, "sender_id": 1, "receiver_id": 2, "content": "Hello, world!"}

Get / Delete a message

GET    /messages/7
DELETE /messages/7

---

Notes

• The controllers are thin and call DAO methods (swap MySQL for Postgres by changing the DAO only).
• Use Blueprints to register routes under your Flask app factory.
• Add input validation (e.g., marshmallow/pydantic) and proper error handling for production.

---

License

MIT