How to build a data search API in Python: step by step
This tutorial shows how to build a data search API in Python to expose simple queries, with filters, pagination and basic security. Having a search API allows you to make data available to dashboards, applications or automations without exposing the database directly. It is useful when you want to share only a subset of data (for example 10–100 fields per record) and enforce limits (for example 10–100 results per request) to avoid overload. For light to moderate loads (up to a few hundred requests per minute) SQLite and FastAPI can be sufficient; for larger loads it is recommended to migrate to PostgreSQL or a managed service.
Prerequisites
- Python 3.9+ installed (check with
python --version) - Basic knowledge of Python and SQL
- Libraries: FastAPI, Uvicorn, SQLAlchemy, pydantic
- Text editor and terminal
I recommend creating a virtual environment to isolate dependencies. A quick example:
python -m venv .venv
source .venv/bin/activate # on Windows: .venv\\Scripts\\activate
python -m pip install --upgrade pip
pip install fastapi uvicorn sqlalchemy pydantic
Step 1: Create a minimal project and install dependencies
Start by creating an environment and installing the libraries. FastAPI is used to build the API, Uvicorn is the recommended ASGI server for development and light production, SQLAlchemy to access the database and pydantic to validate requests. This separation helps keep the code testable and secure.
In a small project, the minimal structure can be:
./
├── main.py
├── db_setup.py
├── schemas.py
└── data.db # criado pelo script de setup
Step 2: Prepare a sample SQLite database
We use SQLite for simplicity. SQLite creates a single file (for example data.db) and is suitable for development or data up to tens of thousands of records. For databases with >1M records or high concurrency, use PostgreSQL. The example creates an "items" table with some test records — enough to experiment with filters and pagination.
# db_setup.py
from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
def init_db(path='sqlite:///data.db'):
engine = create_engine(path)
metadata = MetaData()
items = Table('items', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('category', String),
Column('price', Integer))
metadata.create_all(engine)
# inserir dados de exemplo
with engine.connect() as conn:
conn.execute(items.insert(), [
{'name': 'Camisola', 'category': 'Vestuário', 'price': 30},
{'name': 'Calças', 'category': 'Vestuário', 'price': 45},
{'name': 'Mouse', 'category': 'Eletrónica', 'price': 25},
{'name': 'Teclado', 'category': 'Eletrónica', 'price': 50},
])
if __name__ == '__main__':
init_db()
Run python db_setup.py to create the data.db file with some records.
Step 3: Define the request and response model with pydantic
We use pydantic to validate search parameters: q (text), category (filter), min_price/max_price and pagination (limit/offset). Validating parameters prevents malformed requests and limits server load — for example we enforce limit between 1 and 100. Default values help ensure predictable behavior (limit=10, offset=0).
# schemas.py
from pydantic import BaseModel, Field
from typing import Optional
class SearchParams(BaseModel):
q: Optional[str] = Field(None, description='Texto a procurar em name')
category: Optional[str] = None
min_price: Optional[int] = None
max_price: Optional[int] = None
limit: int = Field(10, gt=0, le=100)
offset: int = Field(0, ge=0)
Step 4: Implement the search route in FastAPI
The route accepts query parameters, builds the SQLAlchemy query dynamically and returns paginated results. By building conditions with SQLAlchemy we avoid SQL injection. To provide useful pagination info you may want to also return a "total" field with the total number of records that match the filters (requires a separate COUNT query).
# main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import create_engine, select, Table, MetaData, and_
from sqlalchemy.sql import text
from sqlalchemy.exc import SQLAlchemyError
from typing import List
from schemas import SearchParams
app = FastAPI()
engine = create_engine('sqlite:///data.db')
metadata = MetaData()
items = Table('items', metadata, autoload_with=engine)
@app.get('/search')
def search(params: SearchParams = Depends()):
try:
stmt = select(items)
conditions = []
if params.q:
conditions.append(items.c.name.ilike(f"%{params.q}%"))
if params.category:
conditions.append(items.c.category == params.category)
if params.min_price is not None:
conditions.append(items.c.price >= params.min_price)
if params.max_price is not None:
conditions.append(items.c.price <= params.max_price)
if conditions:
stmt = stmt.where(and_(*conditions))
stmt = stmt.limit(params.limit).offset(params.offset)
with engine.connect() as conn:
result = conn.execute(stmt).mappings().all()
return {"count": len(result), "results": result}
except SQLAlchemyError as e:
raise HTTPException(status_code=500, detail=str(e))
Step 5: Run the server and test locally
Use Uvicorn to run the API. FastAPI includes automatic documentation at /docs (Swagger) and /redoc, very useful to test interactively. Example calls:
uvicorn main:app --reload --port 8000
# Example call
curl "http://127.0.0.1:8000/search?q=tec&category=Eletrónica&limit=5&offset=0"
Check JSON responses for the keys count and results. Test combinations: text search, filter by category, price ranges and pagination. In Swagger you can try various combinations quickly.
Step 6: Handle common errors and improve security
Common issues: SQL injection (using SQLAlchemy with parameters avoids it), invalid parameters (pydantic helps), timeouts and payload limits. For production, add authentication (API key/JWT), rate limits (for example 100 requests/min per IP), logging and monitoring. It is also common to enforce a maximum limit (e.g. 100) to avoid responses that are too large.
# exemplo simples de verificação de API key
from fastapi import Header
API_KEY = 'trocar_para_prod'
def get_api_key(x_api_key: str = Header(...)):
if x_api_key != API_KEY:
raise HTTPException(status_code=401, detail='Invalid API Key')
return x_api_key
# depois usar Depends(get_api_key) nas rotas
Other improvements: handle CORS properly, add caching (for example Redis) for popular results and use a connection pool when migrating to PostgreSQL.
Verify the result
Confirm that the API responds with JSON with the keys count and results. Test various parameters: text search, filters by category, price ranges and pagination (limit/offset). Also verify that sending a wrong API key returns 401 and that excessive values for limit are rejected by pydantic validation.
Conclusion
You now have a data search API in Python with FastAPI and SQLite, supporting filters, pagination and validation. Recommended next steps: migrate to a production database (PostgreSQL) if you expect high concurrency or volumes >1M records, add caching with Redis, and integrate robust authentication (OAuth/JWT) and structured logging. Try adding sorting and facets to improve search — what sorting would make sense for your data?