Introduction:

In this blog, we will walk through the process of inserting data into a MySQL table using FastAPI.

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python based on standard Python type hints.

MySQL HeatWave is a fully managed database service, powered by the HeatWave in-memory query accelerator. It’s the only cloud service that combines transactions, real-time analytics across data warehouses and data lakes, and machine learning in one MySQL Database—without the complexity, latency, risks, and cost of ETL duplication.

 

Prerequisites:

 

  1. OCI Linux VM Compute:
    • The Compute should be already configured and accessible over a public IP. (you can use private IP)
    • This blog uses Oracle Linux 8 (Image build: 2024.04.19-0). But you can use other image build.

 

  1. OCI MySQL HeatWave:
    • MySQL HeatWave should be already setup in OCI.

 

Login to the compute instance over public ip:

ssh -i private_key.pem opc@XXX.XXX.XXX.XX

 

Install python if it’s already not installed:

sudo dnf install python39

sudo ln -sf /usr/bin/python3.9 /usr/bin/python3

python3 -V

 

Output:

 

python install

 

Upgrade pip:

python3.9 -m pip install –upgrade pip

 

Now as opc user install the libraries:

sudo pip3 install fastapi

sudo pip3 install uvicorn

sudo pip3 install mysql-connector-python

sudo dnf install  mysql-shell

 

Allow traffic on port 8000:

 

[opc@api ~]$ sudo firewall-cmd –zone=public –add-port=8000/tcp –permanent

success

[opc@api ~]$ sudo firewall-cmd –reload

 

firewall

 

Add ingress rule for port # 8000 in security list for FastAPI:

 

Security_ingress

 

Now connect to MySQL HeatWave from VM compute :

mysqlsh -p -h 10.xxx.xxx.xxx -u admin –sql

 

Create database and table:

create database fastapi;

use fastapi;

CREATE TABLE students (

    student_id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50),

    age INT,

    grade VARCHAR(10)

);

 

Output:

create database

Create a file my_fastapi.py with following code and replace your host,password details:

[opc@api ~]$ cat my_fastapi.py

from fastapi import FastAPI, HTTPException

import mysql.connector

 

app = FastAPI()

 

# MySQL HeatWave database connection details

host = ‘xx.x.x.x’

database = ‘fastapi’

username = ‘admin’

password = ‘password’

 

# Endpoint to insert student data

@app.post(“/students/”)

async def insert_student(name: str, age: int, grade: str):

# Establish connection to MySQL HeatWave database

try:

connection = mysql.connector.connect(host=host, database=database,user=username, password=password)

except mysql.connector.Error as error:

raise HTTPException(status_code=500, detail=f”Database connection error: {error}”)

 

try:

cursor = connection.cursor()

# Insert data into the students table

insert_query = “INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)”

cursor.execute(insert_query, (name, age, grade))

connection.commit()

cursor.close()

connection.close()

return {“message”: “Student data inserted successfully”}

except mysql.connector.Error as error:

raise HTTPException(status_code=500, detail=f”Database error: {error}”)

 

if __name__ == “__main__”:

import uvicorn

uvicorn.run(app, host=”0.0.0.0″, port=8000)

 

Now run the above python code with following command:

 

$python3.9 my_fastapi.py

 

Output:

 

 

Now pass FastAPI URL(ip_address:8000) in the browser and click on “Try it out” button as in following screen shot:

 

mysql_hw_try_it_out

 

Fill the details as in following screen shot and click on the execute button:

 

execute button

 

Now login to your database and there would be 1 record in the students table which we posted above:

 

table_output

 

 

To learn more about MySQL HeatWave, please visit: https://www.oracle.com/mysql/