FastAPI SQLite Databases (original) (raw)
Python FastAPI is a modern and fast, web framework for building APIs with Python. FastAPI gained good popularity for its performance and ease of use. When it comes to integrating SQL databases with FastAPI, the framework provides seamless support, making it a good choice for developers for efficient data storage solutions. In this article, we will use SQL databases with FastAPI in Python, with the help of detailed explanations, and good examples.
Connect FastAPI with Database in Python
Let's understand how we can use SQL Database with FastAPI, for data storing and data retrieving in FastAPI. Here, in this example, we are using an SQLite database. for creating a database model we are using SQLalchemy, which is very compatible with Python when we are doing database-related development. It requires that you have a basic idea of FastAPI to understand the concept more clearly.
**Step 1: Installation of Required Libraries
Before starting to code you need to make sure you have FastAPI, pydantic, sqlalchemy, and uvicorn libraries installed in the system. For that simply run the below command in the terminal to install them.
pip install fastapi pip install pydantic pip install sqlalchemy pip install uvicorn
Now you are ready to explore using Database with FastAPI. Our projects contain only one file _main.py. This file contains all the code we discussed in the next steps. as we run our code file, new _test.db file created which contains data that we store that we will see in other steps.
Project Structure
**Step 2: Importing Necessary Libraries and Classes
Let's start by importing the required modules and classes for building the FastAPI application, working with databases using SQLAlchemy, and also for defining data models with Pydantic. so let's import fastapi, pydantic and sqlalchemy.
Python3 `
Import necessary modules and classes
from fastapi import FastAPI, Depends, HTTPException from sqlalchemy import create_engine, Column, Integer, String import sqlalchemy from sqlalchemy.orm import sessionmaker, Session from pydantic import BaseModel
`
**Step 3: Creating a FastAPI Instance and SetUp the Database
Here In this step, We create an instance of the FastAPI application (app). Then we Set up the database connection using SQLite and create a SQLAlchemy engine also Defined a SessionLocal factory for creating database sessions. Here, Base is a declarative base for defining SQLAlchemy models which we use to create our databse model(named _Item).
Python3 `
FastAPI app instance
app = FastAPI()
Database setup
DATABASE_URL = "sqlite:///./test.db" engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) Base = sqlalchemy.orm.declarative_base()
`
**Step 4: Defining the Database Model
In this step we will create a database model named _Item(using sqlalchemy Base model) in our database, we store all our data into this model. The model named Item represents items with an id, name, and description. here _id, _name and _description are data field of model.
Python3 `
Database model
class Item(Base): tablename = "items" id = Column(Integer, primary_key=True, index=True) name = Column(String, index=True) description = Column(String)
`
**Step 5: Creating Database Tables
Let's create a necessary tables in the database based on the defined models.
Python3 `
Create tables
Base.metadata.create_all(bind=engine)
`
**Step 6: Dependency for Getting the Database Session
In this step We will define a dependency function (get_db) to get a database session. It yields the database session to be used in API endpoints and ensures that the database session is properly closed after use by API.
Python3 `
Dependency to get the database session
def get_db(): db = SessionLocal() try: yield db finally: db.close()
`
**Step 7: Pydantic Model for Request Data and Response Data
Here, We defined a Pydantic model (_ItemCreate) to represent the data expected when creating an item. It includes fields for _name and _description. This pydantic model helping in auto validate the input data with defined pydantic data model.
Python3 `
Pydantic model for request data
class ItemCreate(BaseModel): name: str description: str
`
Also, Define a Another Pydantic model (_ItemResponse) for representing the response data. It includes fields for _id, _name, and _description. It's used for sending response data to user when he make request to access the data using FastAPI.
Python3 `
Pydantic model for response data
class ItemResponse(BaseModel): id: int name: str description: str
`
**Step 8: API Endpoint to Create an Item
Here In this step, we will create simple API endpoints which is responcible for getting item from user and adding a item into the database. we created an API endpoint (_/items/) using the __@app.post_ decorator for creating an item. It takes an item as input, validates it using the Pydantic model (_ItemCreate), and adds it to the database. The response_model parameter ensures that the response follows the structure we defined in the ItemResponse Pydantic model.
Python3 `
API endpoint to create an item
@app.post("/items/", response_model=ItemResponse) async def create_item(item: ItemCreate, db: Session = Depends(get_db)): db_item = Item(**item.model_dump()) db.add(db_item) db.commit() db.refresh(db_item) return db_item
`
**Step 9: API Endpoint to Read an Item by ID
Here we created a API endpoint which is for getting data from database using an item id as input. In simple term when user want to access some data of item from database, he/she will send that item id and in response he/she get all details of that item from databse. so we created Another API endpoint (_/items/{item_id}) which is defined using the __@app.get_ decorator for reading an item by its ID. It takes the _item_id as a parameter, queries the database, and returns the item. If the item is not found, it raises an HTTP exception with a 404 status.
Python3 `
API endpoint to read an item by ID
@app.get("/items/{item_id}", response_model=ItemResponse) async def read_item(item_id: int, db: Session = Depends(get_db)): db_item = db.query(Item).filter(Item.id == item_id).first() if db_item is None: raise HTTPException(status_code=404, detail="Item not found") return db_item
`
**Step 10: Running the FastAPI Application
At last just add the block of code which make sure that the FastAPI application is run only if the script is executed directly (not imported as a module). Basically it uses uvicorn to run the FastAPI application on "_http://127.0.0.1:8000".
Python3 `
if name == "main": import uvicorn
uvicorn.run(app, host="127.0.0.1", port=8000)`
Final Code Implementation
In this final code, we created the FastAPI application that is about to interact with an SQLite database through SQLAlchemy. The code establishes the FastAPI instance(web API application), configures the database connection, defines a simple data model for items, and creates corresponding database tables. Also, in the example we use Pydantic models for both request and response data. Two API endpoints are implemented, one for creating items and another for retrieving items by their ID. we will use those API endpoints and see how data changes in the Database.
Python3 `
from fastapi import FastAPI, Depends, HTTPException from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker, Session from pydantic import BaseModel
FastAPI app instance
app = FastAPI()
Database setup
DATABASE_URL = "sqlite:///./test.db" engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) Base = declarative_base()
Database model
class Item(Base): tablename = "items" id = Column(Integer, primary_key=True, index=True) name = Column(String, index=True) description = Column(String)
Create tables
Base.metadata.create_all(bind=engine)
Dependency to get the database session
def get_db(): db = SessionLocal() try: yield db finally: db.close()
Pydantic model for request data
class ItemCreate(BaseModel): name: str description: str
Pydantic model for response data
class ItemResponse(BaseModel): id: int name: str description: str
API endpoint to create an item
@app.post("/items/", response_model=ItemResponse) async def create_item(item: ItemCreate, db: Session = Depends(get_db)): db_item = Item(**item.dict()) db.add(db_item) db.commit() db.refresh(db_item) return db_item
API endpoint to read an item by ID
@app.get("/items/{item_id}", response_model=ItemResponse) async def read_item(item_id: int, db: Session = Depends(get_db)): db_item = db.query(Item).filter(Item.id == item_id).first() if db_item is None: raise HTTPException(status_code=404, detail="Item not found") return db_item
if name == "main": import uvicorn
# Run the FastAPI application using Uvicorn
uvicorn.run(app, host="127.0.0.1", port=8000)`
**Output:
We can run this code as we run any simple python file. API will be available at "_127.0.0.1:8000", and to use Swagger UI we will acess "_127.0.0.1:8000/docs" in browser.
.jpg)