In this example we want to learn that How to Build Flask REST API with MySQL , This is step by step guide to build Flask REST API with CRUD (Create, Read, Update, Delete) operations and a MySQL database:
- Set up your development environment:
- Install Flask and Flask-RESTful
- Install MySQL database (e.g. MySQL or MariaDB)
- Install Python MySQL library (e.g. PyMySQL or MySQL Connector)
- Create a Flask application:
- Create new directory for your project
- Create new virtual environment and activate it
- Install required libraries using pip
- Create new Flask application using flask run command
- Connect to the MySQL database:
- Create new database and a table for your API
- Configure your MySQL connection in the Flask application
- Define your REST API routes:
- Define four routes for CRUD operations
- Implement the logic for each route
- Test each route using a REST client (e.g. Postman)
- Deploy your API:
- Choose web server for your API (e.g. gunicorn, uWSGI)
- Configure your web server to run your Flask application
- Deploy your API to a production environment (e.g. Heroku, AWS)
Note: This is high level overview, specific details will depend on your implementation.
This is an example of Flask REST API with CRUD operations and MySQL database:
- Set up your environment:
|
1 2 3 4 5 6 |
# create virtual environment and activate it python -m venv venv source venv/bin/activate # install required libraries pip install flask flask-restful PyMySQL |
- Create Flask application:
|
1 2 3 4 5 6 7 8 |
# create new directory and navigate to it mkdir myapi cd myapi # create flask application export FLASK_APP=app.py export FLASK_ENV=development flask run |
- Connect to the MySQL database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# create database and table CREATE DATABASE mydb; USE mydb; CREATE TABLE items (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255)); # configure mysql connection in flask from flask import Flask from flask_restful import Api, Resource import pymysql app = Flask(__name__) api = Api(app) conn = pymysql.connect( host='localhost', user='root', password='', database='mydb' ) |
- Define your REST API routes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
class Item(Resource): def get(self, id=None): # retrieve item from database with conn.cursor() as cursor: if id is not None: cursor.execute(f'SELECT * FROM items WHERE id={id}') else: cursor.execute('SELECT * FROM items') items = cursor.fetchall() return {'items': items} def post(self): # insert item into database item = request.get_json() with conn.cursor() as cursor: cursor.execute(f'INSERT INTO items (name) VALUES("{item["name"]}")') conn.commit() return {'item': item}, 201 def put(self, id): # update item in database item = request.get_json() with conn.cursor() as cursor: cursor.execute(f'UPDATE items SET name="{item["name"]}" WHERE id={id}') conn.commit() return {'item': item} def delete(self, id): # delete item from database with conn.cursor() as cursor: cursor.execute(f'DELETE FROM items WHERE id={id}') conn.commit() return {'result': 'item deleted'} api.add_resource(Item, '/item/<int:id>', '/item') |
- Run the API:
|
1 |
flask run |
This is just a basic example, you may need to add error handling, authentication, and other features depending on your requirements.
Lean More on Python
- Is Python Good for GUI Apps
- Is Python Good for Ethical Hacking
- Python Best Libraries for Web Development
- Top 10 Python REST API Frameworks
Here is the complete example in a single file app.py:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
from flask import Flask, request from flask_restful import Api, Resource import pymysql app = Flask(__name__) api = Api(app) conn = pymysql.connect( host='localhost', user='root', password='', database='mydb' ) class Item(Resource): def get(self, id=None): with conn.cursor() as cursor: if id is not None: cursor.execute(f'SELECT * FROM items WHERE id={id}') else: cursor.execute('SELECT * FROM items') items = cursor.fetchall() return {'items': items} def post(self): item = request.get_json() with conn.cursor() as cursor: cursor.execute(f'INSERT INTO items (name) VALUES("{item["name"]}")') conn.commit() return {'item': item}, 201 def put(self, id): item = request.get_json() with conn.cursor() as cursor: cursor.execute(f'UPDATE items SET name="{item["name"]}" WHERE id={id}') conn.commit() return {'item': item} def delete(self, id): with conn.cursor() as cursor: cursor.execute(f'DELETE FROM items WHERE id={id}') conn.commit() return {'result': 'item deleted'} api.add_resource(Item, '/item/<int:id>', '/item') if __name__ == '__main__': app.run(debug=True) |
This example assumes that you have created a database mydb and a table items with the following SQL commands:
|
1 2 3 |
CREATE DATABASE mydb; USE mydb; CREATE TABLE items (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255)); |
You can use the postman and you can access the routes like this http://127.0.0.1:5000/item
