How to avoid None value for optional parameters

[BonuV]

Hi,

In my code i have two optional parameters in my get request , Sometimes I may pass both and sometime I may pass only one. But when i pass only 1 parameter its being consider as NULL in my query and the results are not accurate. Could anybody help how to solve this issue.

i could find an option mongoengine.queryset.visitor import Q when the database is mongoDB , but not able to figure out the same for postgres db

`from fastapi import FastAPI, Depends, Path,Query
from pydantic import BaseModel
from typing import Optional, List
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker, Session
from sqlalchemy import Boolean, Column, Float, String, Integer,or_,and_

app = FastAPI()

SqlAlchemy Setup

SQLALCHEMY_DATABASE_URL = 'postgresql://{}:{}@{}:{}/{}?sslmode={}'.format('', 'prefer')
engine = create_engine(SQLALCHEMY_DATABASE_URL, echo=True, future=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

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

A SQLAlchemny ORM Place

class DBPlace(Base):
tablename = 'places'

id = Column(Integer, primary_key=True, index=True)
name = Column(String(50))
description = Column(String, nullable=True)
coffee = Column(Boolean)
wifi = Column(Boolean)
food = Column(Boolean)
lat = Column(Float)
lng = Column(Float)

Base.metadata.create_all(bind=engine)

A Pydantic Place

class Place(BaseModel):
name: str
description: Optional[str] = None
coffee: bool
wifi: bool
food: bool
lat: float
lng: float

class Config:
    orm_mode = True

Methods for interacting with the database

def get_place(db: Session, place_id: int, coffee: bool):
return db.query(DBPlace).filter(and_(DBPlace.id == place_id,DBPlace.coffee==coffee)).all()

def get_places(db: Session):
return db.query(DBPlace).all()

def create_place(db: Session, place: Place):
db_place = DBPlace(**place.dict())
db.add(db_place)
db.commit()
db.refresh(db_place)

return db_place

Routes for interacting with the API

@app.post('/places/', response_model=Place)
def create_places_view(place: Place, db: Session = Depends(get_db)):
db_place = create_place(db, place)
return db_place

@app.get('/places/', response_model=List[Place])
def get_places_view(db: Session = Depends(get_db)):
return get_places(db)

@app.get('/query_here/')
def get_place_view(place_id: Optional[int]=Query(None),
coffee: Optional[bool]=Query(None),
db: Session = Depends(get_db)):
return get_place(db, place_id,coffee)`

[negadive]

hi, sorry this will be out of topic, try to use 3 ticks for code block, this will help us to read your question

```python
<big code>
```

[simondale00]

Hey @BonuV.

In general, I'd recommend setting default non-null values for query parameters. For instance, you could give default values like coffee: Optional[bool] = False. This ensures the endpoint offers well understood, default functionality when query parameters aren't provided.

Another option is to change your SQLAlchemy query; only filter by parameters provided. Your filter could look something like this:

def get_place(db: Session, place_id: int, coffee: bool):
    query = db.query(DBPlace)
    if place_id is not None:
        query = query.filter(DBPlace.id == place_id)
    if coffee is not None:
        query = query.filter(DBPlace.coffee == coffee)
    return query.all()

Multiple filter statements chained in this way will act as if you'd combined then using the and_() function. Yet this syntax gives you more flexibility to include/exclude parameters.

Best,
Simon