Creating CRUD Operations in RESTful API using Python, FASTAPI, SQL Alchemy, and PostgreSQL

RESTful API stands for Representational State Transfer Application Programming Interface. It is a software architectural style that defines a set of constraints to be used for creating web services. CRUD operations refer to Create, Read, Update, and Delete operations on data.

In this tutorial, we will be creating a RESTful API using Python, FASTAPI, SQL Alchemy, and PostgreSQL to perform CRUD operations on a database.

Step 1: Setting up the Environment

The first step is to set up the environment by installing the necessary libraries. You will need to install FASTAPI, SQL Alchemy, and the PostgreSQL database.


pip install fastapi
pip install sqlalchemy
pip install databases


Step 2: Creating the Database

Next, you will need to create a PostgreSQL database and set up a table for your data. You can do this using the following SQL commands:


CREATE DATABASE mydatabase;
CREATE TABLE items (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
description TEXT
);


Step 3: Writing the Python Code

Now, it’s time to write the Python code for our RESTful API. We will be using FASTAPI for creating the API endpoints and SQL Alchemy for interacting with the database.


from fastapi import FastAPI
from sqlalchemy import create_engine, Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Item(Base):
__tablename__ = 'items'

id = Column(Integer, primary_key=True, index=True)
name = Column(String, index=True)
description = Column(Text)

engine = create_engine('postgresql://username:password@localhost/mydatabase')
Base.metadata.create_all(bind=engine)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

app = FastAPI()

@app.post('/items/')
def create_item(item: Item):
db = SessionLocal()
db.add(item)
db.commit()
db.refresh(item)
return item

@app.get('/items/{item_id}')
def read_item(item_id: int):
db = SessionLocal()
item = db.query(Item).filter(Item.id == item_id).first()
return item

@app.put('/items/{item_id}')
def update_item(item_id: int, item: Item):
db = SessionLocal()
db_item = db.query(Item).filter(Item.id == item_id).first()
db_item.name = item.name
db_item.description = item.description
db.commit()
db.refresh(db_item)
return db_item

@app.delete('/items/{item_id}')
def delete_item(item_id: int):
db = SessionLocal()
db_item = db.query(Item).filter(Item.id == item_id).first()
db.delete(db_item)
db.commit()
return {'message': 'Item deleted successfully'}


Step 4: Running the API

Finally, you can run the API using the following command:


uvicorn main:app --reload


After running the API, you can test the CRUD operations using tools like Postman or curl to send requests to the API endpoints.

Conclusion

In this tutorial, we have learned how to create a RESTful API using Python, FASTAPI, SQL Alchemy, and PostgreSQL to perform CRUD operations on a database. By following the steps outlined in this tutorial, you can create a robust API for interacting with your data.