The objective of this project is to develop a complete application that performs CRUD (Create, Read, Update, Delete) operations on a MySQL database using Python.
Video Implementation: Youtube
- Ensure you have MySQL Server installed and running.
- Create a database named
school
and a table namedstudents
with appropriate columns (id
,name
,age
,grade
). Refer to the instructions for setting up the database.
- Make sure you have Python installed on your system. You can download Python from python.org if not already installed.
- database.py: Implement MySQL connection and CRUD operations.
- main.py: Implement FastAPI application with CRUD endpoints.
- requirements.txt: List the Python dependencies (fastapi, uvicorn, mysql-connector-python).
fastapi==0.70.0
uvicorn==0.15.0
mysql-connector-python==8.0.28
Create a new file named requirements.txt in your project directory.
Copy the above dependencies into requirements.txt
.
Install these dependencies using pip:
pip install -r requirements.txt
By including these dependencies in your requirements.txt file, you ensure that anyone setting up your FastAPI project can easily install the necessary packages with a single command.
UVICORN is an ASGI (Asynchronous Server Gateway Interface) web server implementation tailored for Python.
pip install uvicorn
To run it:
uvicorn main:app --reload
python -m uvicorn main:app --reload
Once uvicorn starts your FastAPI application successfully, you should see output indicating that the server is running, usually on http://127.0.0.1:8000
by default.
To stop the FastAPI application, press Ctrl + C
in the terminal or command prompt where the server is running.
import mysql.connector
from mysql.connector import Error
def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school"
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")
return connection
host: The hostname or IP address where your MySQL server is running. Typically, if it's on your local machine, you use "localhost".
user: The username to authenticate with the MySQL server.
password: The password corresponding to the username for authentication.
database: The name of the database you want to connect to.
CREATE DATABASE school;
USE school;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
grade VARCHAR(10) NOT NULL
);
- CREATE DATABASE: This SQL command creates a new database named school.
- USE school: Sets the school database as the current working database.
- CREATE TABLE students: Defines a table named students within the school database. It includes columns
id
(auto-incremented integer and primary key),name
(string),age
(integer), andgrade
(string).
To perform CRUD operations on your FastAPI application using Postman, you'll use HTTP methods (POST
, GET
, PUT
, DELETE
) to interact with your API endpoints.
Here’s how you can set up and execute these commands in Postman:
Ensure your FastAPI application is running using uvicorn as explained earlier.
Method: POST
URL: http://127.0.0.1:8000/students/
-
Content-Type: application/json
-
Accept: application/json
{
"name": "John Doe",
"age": 21,
"grade": "A"
}
Click Send
. You should receive a response confirming the creation of the student.
Method: GET
URL: http://127.0.0.1:8000/students/
- Accept: application/json
Click Send
. You should receive a response with a list of all students.
Method: GET
URL: http://127.0.0.1:8000/students/{student_id}
Replace {student_id}
with the ID of the student you want to fetch.
- Accept: application/json
Click Send
. You should receive a response with the student details if the student exists.
Method: PUT
URL: http://127.0.0.1:8000/students/{student_id}
Replace {student_id}
with the ID of the student you want to update.
-
Content-Type: application/json
-
Accept: application/json
{
"name": "Jane Doe",
"age": 22,
"grade": "A+"
}
Method: DELETE
URL: http://127.0.0.1:8000/students/{student_id}
Replace {student_id}
with the ID of the student you want to delete.
- Accept: application/json
Click Send
. You should receive a response confirming the deletion of the student.
-
Ensure the FastAPI server
(uvicorn main:app --reload)
is running while testing with Postman. -
Adjust the URL
(127.0.0.1:8000)
and endpoint paths(/students/, /students/{student_id})
based on the FastAPI application's configuration. -
Verify each operation's success through Postman's response and status codes
(200 OK, 201 Created, 204 No Content, 404 Not Found, etc.)
.
By following these steps, we can effectively test the FastAPI application's CRUD operations using Postman, ensuring that the API endpoints behave as expected when interacting with the MySQL database.