CODEWITHSUNDEEP
pythonsqlalchemymariadbfastapipython-3.10
Johann Suarez
Johann Suarez

Reputation: 59

FastAPI + SQLAlchemy - InvalidRequestError when posting entry to table with datetime field

I have just followed and completed the entire SQLAlchemy part of the FastAPI documentation here, with my local MariaDB as my chosen database.

The code I have looks identical to the final files listed at the end. I've tested everything and it all works.

An issue arose when I tried to make my own database table called DHT ( Digital Humidity and Temperature ). It's meant to hold information on the humidity and temperature of my room at certain times during the day. When I test the endpoint for writing into the table, I get an error back. But the data does get saved into the table as illustrated further below.

This is what the SQLAlchemy ORM model looks like:

class DHT(Base):
    __tablename__ = "dht"

    datetime = Column(DateTime, primary_key=True, default=datetime.now)
    humidity = Column(Float(8), index=True)
    temp = Column(Float(8), index=True)

Here are the Pydantic schemas for data validation:

class DHTBase(BaseModel):
    temp: float
    humidity: float

class DHTCreate(DHTBase):
    pass

class DHT(DHTBase):
    """
    In the Pydantic models for reading, we add an internal Config class.
    """
    date: datetime

    class Config:
        orm_mode = True

Everything starts here at the endpoint, I followed the other POST endpoint examples as best as I can.

@app.post("/dht/", response_model=schemas.DHT)
def create_dht(dht: schemas.DHTCreate,
               db: Session = Depends(get_db)):
    return crud.create_dht(db=db, dht_data=dht)

This is the crud operation that the endpoint calls:

def create_dht(db: Session, dht_data: schemas.DHTCreate):

    dht = models.DHT(humidity=dht_data.humidity,
                     temp=dht_data.temp)
    db.add(dht)
    db.commit()
    db.refresh(dht)
    return dht

When I test the endpoint with curl, example something like this:

curl -X 'POST' \
  'http://127.0.0.1:8000/dht/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "temp": 23.34,
  "humidity": 55.35
}'

...FastAPI logs this error:

  File "/home/sydney/.cache/pypoetry/virtualenvs/app-sfi98_Li-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2328, in refresh
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: Could not refresh instance '<DHT at 0x7f09f52c5420>'

But when I check the database, an entry is in fact written.

MariaDB [blogfolio]> select * from dht;
+---------------------+----------+-------+
| datetime            | humidity | temp  |
+---------------------+----------+-------+
| 2022-03-26 10:00:11 |    55.35 | 23.34 |
+---------------------+----------+-------+

The reply I expected from FastAPI would be a JSON of this form.

{
  "temp": 23.34,
  "humidity": 55.35,
  "date": "2022-03-26T17:00:11.815Z"
}

Instead I get "Internal Server Error".

I made a dummy version of the DHT table where the datetime column is just replaced with a string column and is filled with string data, and everything works just fine. So at this point the only thing I'm certain of is that it's coming from SQLAlchemy not liking either the datetime field of my ORM model, or how I'm writing the datetime data into it.

What's wrong with my ORM model / CRUD operation?

Upvotes: 1

Views: 4224

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9049

The documentation for mariadb's datetime states that 0 microseconds is the default datetime precision. This appears to be so in your db output, ie. 2022-03-26 10:00:11. Also it appears from the same documentation that the time zone depends on the session.

I was able to recreate this exception as well as a half-fix by using DATETIME(fsp=6). This is what appears to happen:

  1. DHT is created using python's datetime.now() as the primary key in your application
  2. DHT is written to the db, the microseconds are probably just ignored by mariadb because the column is expecting no microseconds by default
  3. refresh is called, SQLAlchemy queries the db looking for a record with datetime = 2022-03-26T17:00:11.815 (note there are microseconds here)
  4. it can't find one because the datetime in the db trimmed those down to 2022-03-26T17:00:11, so refresh fails with an exception

Right here I'd say the safest strategy would be to use an integer id for the primary key but ...

To make this work first you could bump the precision up on the column to 6 places for microseconds. You have to use the dialect specific DATETIME, from sqlalchemy.dialect.mysql import DATETIME, with fsp=6.

Then timezones... you should make sure the db session time zone matches the time zone you use to make the datetime in your app because mariadb does not do conversion. UTC is probably the safest thing here but I'm not sure of the specifics of mariadb.

You might have to change the server's default itself. This might work for the session, session.execute(text("SET time_zone = 'UTC'")).

In python you could do default = lambda: datetime.datetime.now(datetime.timezone.utc).replace(tzinfo=None) via import datetime. This will get the current time in UTC but then remove the timezone to make the dt naive.

Upvotes: 1

Related Questions