Python — Building Simple API with FastAPI and PostgreSQL
Getting started with the GET method on the FastAPI backend and SQL database
API stands for Application Programming Interface and is one of the most essential concepts in software development. It allows programs to interact with other services by sending and receiving data. One of the most widely used standards for API web communication is REST, which relies on JSON format or key-value pairs, similar to Python’s dictionaries.
In this article, I want to focus on the practical implementation and start building API right away. If you want to build one in Python, you can choose from several frameworks. Flask-RESTful, Django Rest Framework, and FastAPI are the most popular ones. However, FastAPI is specifically designed for swift API applications. Why do I recommend using FastAPI for your first API?
- Automatic documentation — FastAPI generates comprehensive documentation automatically based on OpenAPI standards, which saves time and effort.
- Fast and easy — designed for high-performance and efficient execution.
- Data validation — FastAPI is built on top of Pydantic, providing a batch of useful functionality such as data validation and asynchronous support.
Although Django Rest Framework is also commonly used in many companies, let’s start with FastAPI for its benefits. With that said, let’s move on to the first steps of writing FastAPI code after a brief theoretical introduction.
All the code is available on my GitHub, where you can also find examples for some of my other articles. Feel free to use it and ask me any questions directly. Also, you can find the dataset for the SQL database in the path sql-data.
Brief overall introduction
Endpoint
As previously discussed, an API serves as a means of communication between different services and applications. This communication would typically be facilitated by a server-side infrastructure and a backend implementation for the API. The purpose of this backend could be to provide access to data without requiring a direct connection to the database, thereby minimizing the number of requests to the database.
In API communication, we typically have a designated endpoint, which could appear as /api/item/5 at the end of the website. This approach is highly advantageous as it allows us to utilize the same endpoint for requesting data from various devices and receiving the same response.
HTTP methods
Even though I don’t want to go deep into theoretic concepts, it’s important to understand the distinction between different methods when working with APIs. Let’s quickly review the most commonly used ones:
- GET — used to retrieve data
- POST — to write new data
- DELETE — removes your data
- PUT — updates existing information
While other options exist, these are the basics you should start with. It’s important to note the difference between POST and PUT. Both can be used to add data, but POST is used for writing new items, while PUT is used for updating existing items with newer values.
Remember that this is just a guideline on how to write your API. You can use these methods in your own way, but it’s better to stick to industry standards. Today I want to focus on implementing a simple GET method.
Building your first API
Running the first FastAPI server
To begin with, we need to install all dependencies. You need at least to have the fastapi package together with uvicorn server and pydantic. The first line should install all the listed libraries, but check to have all the packages.
pip install fastapi[all]
pip install uvicorn
pip install pydanticNow, let’s create a basic FastAPI app by using a decorator with the necessary method and endpoint. In this example, we will retrieve data using the GET method on the empty endpoint /. In this article, I will not discuss decorators, it’s out of the scope of the API topic. However, in the context of FastAPI, we utilize the application name, method, and endpoint. Before the function, you may observe a line of code that is responsible for adding all the FastAPI processes to our method.
from fastapi import FastAPI
app = FastAPI()
@app.get('/') # get method to empty endpoint
def first_response():
return {"response": "first"}To view the response, you can run the server using uvicorn. By default, your server is on port 8000 and can be accessed through http://127.0.0.1:8000. During development, you can use the --reload option to ensure that the server reacts to any changes made to your code.
uvicorn main:app
uvicorn main:app --reload
As an alternative, you may also use curl to get the same response.
curl 127.0.0.1:8000
#{"response": "first"}
curl 127.0.0.1:8000 -X POST
#{"detail":"Method Not Allowed"}Finally, we can use the requests library to access our endpoint and print the response in Python.
import requests
print(requests.get('http://127.0.0.1:8000').json())
#{'response': 'first'}Working with data from JSON file
Let’s move on to the next step and work with some actual data. To simplify things at this point, we can create a JSON file with some inputs. For instance, we can take three stocks from NASDAQ and create an example with a symbol that can be used to obtain the required item and perform other methods.
{
"stocks": [
{
"symbol": "TSLA",
"stockname": "Tesla Inc. Common Stock",
"lastsale": "$235.45",
"country": "United States",
"ipoyear": 2010
},
{
"symbol": "NVDA",
"stockname": "NVIDIA Corporation Common Stock",
"lastsale": "$477.76",
"country": "United States",
"ipoyear": 1999
},
{
"symbol": "AMZN",
"stockname": "Amazon.com Inc. Common Stock",
"lastsale": "$146.74",
"country": "United States",
"ipoyear": 1997
}
]
}We can now modify our code to retrieve not all, but only the items that contain the specific symbol we want. In other words, perform filtering on the backend side. To achieve this, we perform the following additional actions:
- Identify the model for
pydantic. - Read the JSON file and store its contents in a dictionary.
- Apply a filter using the stock symbol to retrieve only the desired item from this dictionary. If the symbol is not found, we raise an exception with the 404 status code (Not Found).
Looks straightforward, but this is the fundamental part of every FastAPI application — schema, data, and method.
from fastapi import FastAPI, HTTPException, Query
from pydantic import BaseModel
from typing import Optional
import json
app = FastAPI()
class Stock(BaseModel):
symbol: str
stockname: str
lastsale: str
country: str
ipoyear: Optional[int] = None
with open('stocks.json', 'r') as f:
stocks = json.load(f)['stocks']
@app.get('/stock/{stock_symbol}', status_code=200)
def get_stock(stock_symbol: str) -> Stock:
stock = [stock for stock in stocks if stock['symbol'] == stock_symbol]
if len(stock) == 0:
raise HTTPException(
status_code=404, detail=f"No stock {stock_symbol} found."
)
return stock[0]Surely, now we need to change the endpoint for the request to get the Amazon stock data.
import requests
print(requests.get('http://127.0.0.1:8000/stock/AMZN').json())
#{'symbol': 'AMZN', 'stockname': 'Amazon.com Inc. Common Stock', 'lastsale': '$146.74', 'country': 'United States', 'ipoyear': 1997}As mentioned earlier, our documentation should now be available. You can access it by typing http://127.0.0.1:8000/docs in your browser’s address bar. As you continue to add more methods, you can easily navigate to the same location to find them all. This documentation will be particularly helpful to those who are just getting started with your API.
After identifying the Pydantic model in our code, we can now determine the schema of the output response. This is also a great way to gain a better understanding of various API methods and what can we get from them.
Connect the PostgreSQL database to your API
In the past, we only worked with local files. However, in most cases, you will need to use a database at the backend. To achieve this, we will connect the PostgreSQL database and attempt to use it in our GET method. On the database side — it’s a simple SELECT statement. Still, we need to identify everything correctly for the FastAPI usage.
The process involves the SQLAlchem library, which is one of the most popular packages for Object-Relational Mapping (ORM) operations in Python. I have already provided an introduction to ORM and compared it to other ways of communicating with databases in a previous article.
To store credentials in a separate file, create a config.py with the following code. This file is not included in my GitHub example as it contains sensitive data, but you can use the following example.
from pydantic_settings import BaseSettings
class Settings(BaseSettings):
sqlalchemy_string: str = "postgresql://user:passwordp@host/db"
settings = Settings()Let’s create the engine and prepare the database for the FastAPI session by structuring this part into the database.py file. Here we use the settings from the config.py file.
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from config import settings
engine = create_engine(
settings.sqlalchemy_string, connect_args={'sslmode':'require'}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()Next, we need to link the model to the relevant tables in our database. We specifically deal with the table that contains the NASDAQ stock data, and we will use a simple model for it. We will stick to the basic model for the purpose of our example.
from sqlalchemy import Column, Integer, String, Float, BigInteger
from database import Base
class Stock(Base):
__tablename__ = "nasdaq_stocks"
symbol = Column(String, primary_key=True)
stockname = Column(String)
lastsale = Column(String)
netchange = Column(Float)
percentchange = Column(String)
marketcap = Column(BigInteger)
country = Column(String, nullable=True)
ipoyear = Column(Integer, nullable=True)
volume = Column(Integer)
sector = Column(String, nullable=True)
industry = Column(String, nullable=True)I prefer to store our pydantic models in separate files to avoid confusion. Let’s name the file schemas.py and add the relevant models to it. It’s important to configure the orm_mode properly as we’re working with SQLAlchemy and the database.
from pydantic import BaseModel
from typing import Optional
class StockBase(BaseModel):
symbol: str
stockname: str
lastsale: str
country: str
ipoyear: Optional[int] = None
volume: int
class StockCreate(StockBase):
pass
class Stock(StockBase):
class Config:
orm_mode = TrueWe must specify the code for CRUD (Create, Read, Update, Delete) operations on the database side. It will be more convenient to use functions only in the FastAPI application later in the main script. For our basic GET method, a simple filter query by symbol is sufficient. This is an example of the most basic crud.py file.
from sqlalchemy.orm import Session
import models, schemas
def get_stock(db: Session, symbol: str):
return db.query(models.Stock).filter(models.Stock.symbol == symbol).first()We have completed all the necessary preparations for implementing the API. Since we are working with the database, we need to include some additional details in the script. The get_db function is responsible for establishing a connection with the database, and we have included it in the Depends FastAPI class. Here is the final example of the working code.
from fastapi import FastAPI, HTTPException, Query, Depends
from sqlalchemy.orm import Session
import crud, models, schemas
from database import SessionLocal, engine
models.Base.metadata.create_all(bind=engine)
app = FastAPI(
title="NASDAQ stocks",
description="Start using FastAPI in development",
version="0.1"
)
# Dependency
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
@app.get('/stock/{symbol}', response_model=schemas.Stock, status_code=200)
def get_stock(symbol: str, db: Session = Depends(get_db)) -> models.Stock:
db_stock = crud.get_stock(db, symbol=symbol)
if db_stock is None:
raise HTTPException(
status_code=404, detail=f"No stock {symbol} found."
)
return db_stockThere haven’t been any significant changes, but we can now search the entire database rather than just the three stocks from the JSON file. Modifying the output is also simple as we can add or remove fields in our pydantic model. We have an extra field called volume which I added to observe the difference.
import requests
print(requests.get('http://127.0.0.1:8000/stock/AAL').json())
#{'symbol': 'AAL', 'stockname': 'American Airlines Group Inc. Common Stock', 'lastsale': '$12.31', 'country': 'United States', 'ipoyear': None, 'volume': 14803753}ORM works seamlessly with various database options available in the market, enabling efficient integration without any modifications. You can refer to the comprehensive documentation on utilizing SQL databases with FastAPI.
Conclusion
In this article, we will provide an overview of FastAPI and its ability to simplify the implementation of REST APIs. Along with other useful Python dependencies, FastAPI offers many essential features:
- Pydantic for data validation.
- SQLAlchemy for ORM communication with the database.
FastAPI is not limited to returning data as a response only with the GET method. It offers the full power of REST API with other valuable methods such as POST, PUT, and DELETE. In the next article, we will dive deeper into these options and conclude by building a basic, yet production-ready API.
I can be found on LinkedIn and I am looking forward to connecting with you. Let’s engage in discussions about the intricate world of data science and data engineering.
Stackademic 🎓
Thank you for reading until the end. Before you go:
- Please consider clapping and following the writer! 👏
- Follow us X | LinkedIn | YouTube | Discord
- Visit our other platforms: In Plain English | CoFeed | Venture | Cubed
- More content at Stackademic.com
