Integration of SQLAlchemy and Databases in FastAPI Python Framework

FastAPI is a modern web framework for building APIs with Python. It provides an easy-to-use and efficient way to create fast and scalable web applications. In this tutorial, we will walk through how to integrate SQLAlchemy with FastAPI to work with databases.

Step 1: Set up a virtual environment

First, let’s create a virtual environment for our project. Open a terminal and run the following command:

python -m venv venv

Activate the virtual environment by running:

source venv/bin/activate

Step 2: Install FastAPI, SQLAlchemy, and databases libraries

Next, let’s install the required libraries for our project. Run the following commands to install FastAPI, SQLAlchemy, and databases libraries:

pip install fastapi
pip install sqlalchemy
pip install databases

Step 3: Create a FastAPI app

Now, let’s create a FastAPI app. Create a new Python file named main.py and add the following code:

from fastapi import FastAPI

app = FastAPI()

@app.get("/")
async def read_root():
    return {"Hello": "World"}

Save the file and run the following command to start the FastAPI app:

uvicorn main:app --reload

Visit http://localhost:8000 in your browser to see the "Hello World" message.

Step 4: Set up SQLAlchemy database connection

Now, let’s set up a database connection using SQLAlchemy. Add the following code to the main.py file:

from sqlalchemy import create_engine

DATABASE_URL = "postgresql://user:password@localhost/dbname"
engine = create_engine(DATABASE_URL)

Replace user, password, and dbname with your PostgreSQL username, password, and database name.

Step 5: Create SQLAlchemy models

Next, let’s create SQLAlchemy models for our database tables. Add the following code to the main.py file:

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

Base = declarative_base()

class Item(Base):
    __tablename__ = "items"

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

Step 6: Create database tables

Now, let’s create the database tables based on the SQLAlchemy models we defined. Add the following code to the main.py file:

Base.metadata.create_all(engine)

Step 7: Insert data into the database

To insert data into the database, you can use the following code:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

item = Item(title="FastAPI Tutorial", description="Learn how to use FastAPI with SQLAlchemy")
session.add(item)
session.commit()

Step 8: Fetch data from the database

To fetch data from the database, you can use the following code:

items = session.query(Item).all()
for item in items:
    print(item.title, item.description)

Step 9: Integrate SQLAlchemy with FastAPI routes

Now, let’s integrate SQLAlchemy with FastAPI routes. Update the read_root route in the main.py file to fetch data from the database:

@app.get("/")
async def read_root():
    items = session.query(Item).all()
    return {"items": [{"title": item.title, "description": item.description} for item in items]}

Step 10: Run the FastAPI app

Save the file and run the FastAPI app using the following command:

uvicorn main:app --reload

Visit http://localhost:8000 in your browser to see the database items returned as JSON.

Congratulations! You have successfully integrated SQLAlchemy with FastAPI to work with databases. You can now build powerful and scalable web applications using FastAPI and SQLAlchemy.