Create Your Own API with Python Flask, MySQL and a VPS

I wanted to have my own API, so I took a dive with the Flask API, MySQL and a simple Ubuntu VPS. I managed to connect all of them, within just a couple of minutes. So, to share the knowledge, I made this simple but effective guide for you.

This guide will help you to install MySQL, setup a Python-based REST API using Flask, and the deployment of the API on your Ubuntu VPS.

Pre-requisites:

• A VPS running a Linux distribution (Ubuntu, CentOS, etc.)
• SSH access to the VPS
• Basic knowledge of Linux terminal commands
• Python installed on the VPS

Step 1: Install MySQL on VPS

1. Update the package list:
   • sudo apt update
2. Install MySQL server:
   • sudo apt install mysql-server
3. Secure your MySQL installation:
   • sudo mysql_secure_installation
4. Follow the prompts to set a root password and secure your MySQL installation.

Step 2: Create a MySQL Database and Table

1. Log in to MySQL as root:
   • sudo mysql -u root -p
2. Create a new database:
   • CREATE DATABASE my_database;
3. Create a table to store data:
   • USE my_database; CREATE TABLE my_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));
4. Insert sample data:
   • INSERT INTO my_table (name) VALUES ('Cyberwarzone'), ('Test_Data');

Step 3: Install Required Python Packages

Install Flask and MySQL connector for Python:

pip install Flask mysql-connector-python

Step 4: Create a Flask API

Create a Python file, e.g., app.py, and paste the following code:

#Example code of a working Flask API 2023

from flask import Flask, jsonify
import mysql.connector

app = Flask(__name__)

@app.route("/get_data", methods=["GET"])
def get_data():
    try:
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="my_database"
        )
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM my_table")
        rows = cursor.fetchall()
        return jsonify({"data": rows})
    except mysql.connector.Error as err:
        return jsonify({"error": str(err)})
    finally:
        cursor.close()
        conn.close()

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000)

Replace your_password with the MySQL root password you set earlier.

Step 5: Deploy the API

1. Run the Flask app:
   • python app.py
2. Your API should now be accessible at http://your_vps_ip:5000/get_data.

Step 6: Set Up a Firewall (Optional)

If you have a firewall running, you’ll need to allow traffic on port 5000:

sudo ufw allow 5000/tcp

Step 7: Run API in Background (Optional)

To keep the API running even after you’ve closed the SSH session, you can use a tool like tmux or screen, or set it up as a system service.

That’s it! You’ve successfully set up an API service on your VPS to query a MySQL database.