Connecting FastAPI with PostgreSQL for Data Science Applications

In the previous lesson, we explored how to integrate FastAPI with machine learning models like Scikit-Learn and TensorFlow. We built APIs that could take user inputs, process them using trained models, and return predictions. However, storing these predictions for future use or analysis was not covered. That’s where this lesson comes in. Today, we’ll dive into connecting FastAPI with PostgreSQL, a powerful relational database, to store and manage data efficiently. By the end of this tutorial, you’ll know how to use SQLAlchemy, a popular ORM, to interact with PostgreSQL and store processed data from your FastAPI applications.

Storing ML Model Predictions in PostgreSQL

Let me share a real-world scenario I faced while building a data science application. I developed a FastAPI app that used a Scikit-Learn model to predict house prices based on user inputs. While the app worked perfectly, I realized that storing these predictions in a database would make the system more robust. For instance, users could retrieve their past predictions, or I could analyze trends over time. To achieve this, I decided to integrate PostgreSQL with FastAPI using SQLAlchemy. This setup allowed me to store predictions in a structured way and query them whenever needed.

Setting Up PostgreSQL

Before connecting FastAPI to PostgreSQL, you need to set up a PostgreSQL database. If you don’t have PostgreSQL installed, you can download it from the official website or use a cloud-based service like AWS RDS. Once installed, create a new database and a user with the necessary permissions. For example, you can use the following SQL commands:

CREATE DATABASE fastapi_db;
CREATE USER fastapi_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE fastapi_db TO fastapi_user;

This creates a database named fastapi_db and a user named fastapi_user with full access to the database.

Installing Required Libraries

To connect FastAPI with PostgreSQL, you’ll need two main libraries: SQLAlchemy and psycopg2. SQLAlchemy is an ORM that simplifies database interactions, while psycopg2 is a PostgreSQL adapter for Python. Install these libraries using pip:

pip install sqlalchemy psycopg2-binary

Configuring SQLAlchemy in FastAPI

Next, configure SQLAlchemy in your FastAPI application. Start by creating a models.py file to define your database schema. For example, if you want to store house price predictions, you can define a table like this:

from sqlalchemy import Column, Integer, Float, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class HousePrediction(Base):
    __tablename__ = 'house_predictions'
    id = Column(Integer, primary_key=True, index=True)
    size = Column(Float, nullable=False)
    bedrooms = Column(Integer, nullable=False)
    location = Column(String, nullable=False)
    predicted_price = Column(Float, nullable=False)

This code defines a table named house_predictions with columns for house size, bedrooms, location, and predicted price.

Connecting FastAPI to PostgreSQL

Now, let’s connect FastAPI to PostgreSQL. Create a database.py file to handle the database connection:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "postgresql://fastapi_user:your_password@localhost/fastapi_db"

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

Base = declarative_base()

This code sets up a connection to the PostgreSQL database using the URL format postgresql://username:password@host/database.

Storing Predictions in the Database

Finally, let’s modify your FastAPI app to store predictions in the database. Here’s an example of how to do this:

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from . import models, schemas, database

app = FastAPI()

models.Base.metadata.create_all(bind=database.engine)

def get_db():
    db = database.SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.post("/predict/")
def predict_house_price(house: schemas.House, db: Session = Depends(get_db)):
    # Assume predict_price is a function that returns the predicted price
    predicted_price = predict_price(house.size, house.bedrooms, house.location)
    db_prediction = models.HousePrediction(
        size=house.size,
        bedrooms=house.bedrooms,
        location=house.location,
        predicted_price=predicted_price
    )
    db.add(db_prediction)
    db.commit()
    db.refresh(db_prediction)
    return {"predicted_price": predicted_price}

This code defines a /predict/ endpoint that takes house details as input, predicts the price, and stores the result in the PostgreSQL database.

Conclusion

In this tutorial, we learned how to connect FastAPI with PostgreSQL using SQLAlchemy. We set up a PostgreSQL database, installed the required libraries, configured SQLAlchemy, and modified our FastAPI app to store predictions. This setup is essential for building robust data science applications that need to store and manage data efficiently. In the next lesson, we’ll explore how to use NoSQL databases like MongoDB with FastAPI. Stay tuned to learn more about handling unstructured data in your applications.