Fastapi: Returning sqlalchemy Data Model

Created on 9 Jan 2019  路  8Comments  路  Source: tiangolo/fastapi

Hello,

First, thanks for all your docker project generators as well as the fantastic documentation. They have been super useful in getting my project off the ground.

I've been following the sqlalchemy example at: https://fastapi.tiangolo.com/tutorial/sql-databases/.

I'm trying to return a sqlalchemy data model, but I am getting the following error:

Fatal Python error: Cannot recover from stack overflow.

Thread 0x00007f115f510700 (most recent call first):
  File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 78 in _worker
  File "/usr/local/lib/python3.7/threading.py", line 865 in run
  File "/usr/local/lib/python3.7/threading.py", line 917 in _bootstrap_inner
  File "/usr/local/lib/python3.7/threading.py", line 885 in _bootstrap

Current thread 0x00007f116c905700 (most recent call first):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 834 in __getattr__
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 836 in __getattr__
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 190 in __getattr__
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 51 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 30 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 59 in jsonable_encoder
  File "/usr/local/lib/python3.7/site-packages/fastapi/encoders.py", line 31 in <dictcomp>
  ...
INFO: Stopping reloader process [2468]

If I modify the fastapi specific code:
https://fastapi.tiangolo.com/tutorial/sql-databases/#create-your-fastapi-code

and parse the sqlalchemy response into a dictionary, it works fine:

@app.get("/users/{username}")
def read_user(username: str):
    user = get_user(username, db_session)
    user = {"name": user.id,  "email": user.email}
    return user

Thanks again. Been loving FastAPI so far.

picture nickc1
👍2

Most helpful comment

@LexSerest Thanks for the suggestion. It cleaned up my code quite a bit.

@tiangolo Just upgraded to v0.3.0 and it works flawlessly. Thanks for all your work! :clap: :+1: :smile:

picture nickc1 on 12 Feb 2019
🎉1 👍1

All 8 comments

First, thanks for all your docker project generators as well as the fantastic documentation. They have been super useful in getting my project off the ground.

That's great to hear :cake: :tada:

Thanks for the report!

I think I know what might be happening.

Are you using the simple model from the tutorial? Or are you using the models from the full-stack project generator here?: https://github.com/tiangolo/full-stack/blob/master/%7B%7Bcookiecutter.project_slug%7D%7D/backend/app/app/models/user.py

I see those would create recursive attributes, user.roles[0].users[0].roles[0].users[0].roles[0], etc.

I have to find a way to solve that specific case or document it properly. But first I want to confirm with you, how do your model looks like?

Or can you share the model here?

Thanks again. Been loving FastAPI so far.

Thanks! Awesome :blush::tada:

As a note, if you are using the full-stack generator, I plan on creating a full-stack-fastapi-postgres generator equivalent to that one :smile:

tiangolo picture tiangolo on 10 Jan 2019

Thanks for the quick response. My use case is pretty simple, so I just pulled the tiangolo/uvicorn-gunicorn-fastapi:python3.7 docker container and have been looking at your project generators to add the pieces I need.

I've simplified the example a bit below for readability, but it is essentially the same. The model I am working with is straight forward and doesn't have any relationships. The model is below:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import JSONB, NUMERIC, VARCHAR

Base = declarative_base() 

class Summary(Base):
    __tablename__ = 'full_summary'
    company_name = Column(VARCHAR)
    city = Column(VARCHAR)
    state = Column(VARCHAR)
    business_types = Column(JSONB)
    total = Column(NUMERIC)

And I call it with the code below:

def get_company(company_name):
    session = Session()
    res = session.query(Summary).filter(Summary.company_name == company_name).first()
    session.close()
    return res

@router.get("/summary/{company_name}")
def summary(company_name: str):

    company = get_company(company_name)
    if company is None:
        return {}
    # company = {
    #     "name": company.company_name,
    #     "city": company.city,
    #     "state": company.state,
    #     "business_types": company.business_types,
    #     "total": company.total
    # }

    return company

If I uncomment that dictionary, it returns fine. I also noticed that if I modify the get_company function to:

def get_company(company_name):
    session = Session()
    res = session.query(Summary.name, Summary.city, Summary.business_types).\
        filter(Summary.company_name == company_name).first()
    session.close()
    return res

It will return without crashing, but it will return as a list:

[
    "My Company Name",
    "My Company City",
    [
    "Good",
    "Small Business"
    ]
]

Where I would expect it to return:

{
    company_name : "My Company Name",
    city: "My Company City",
    business_types: ["Good", "Small Business"]
}

Sorry for the long post. Thanks for taking a look.

nickc1 picture nickc1 on 10 Jan 2019

I faced the same problem by following the official documentation, but I was able to solve the problem pretty quickly.

I'm assuming FastAPI can't display the data.
It is enough to add to the model a database function for data serialization.

For example:

class User(Base):
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean(), default=True)

    def json(self):
        return {"username": self.username, "id": self.id}

and router

@app.get("/users/{username}")
def read_user(username: str):
    user = get_user(username, db_session)
    if user:
        return user.json()

The situation was rectified by this decision.

@tiangolo please add this to documentation.

LexSerest picture LexSerest on 7 Feb 2019
👍1

@nickc1 @LexSerest There was indeed a bug in the way the SQLAlchemy model was encoded.

I just updated the code, tests, and docs, and released version 0.3.0.

The code for the SQL tutorial itself is being tested now to ensure SQLAlchemy works correctly and that the tutorial code is correct.

The tutorial is now updated to use by default SQLite, to allow quickly testing it. But it also has instructions for the line of code that has to be changed to switch to PostgreSQL.

This means that you should be able to copy the code as is, run it, and get a local file test.db with the SQLite database, with a FastAPI serving from it. You would most probably want to switch to PostgreSQL (or similar) for production, but this way it's easy to test SQLAlchemy without needing to set up a full database server.

It also means that now is actually possible to return the SQLAlchemy model directly.

A custom method to encode the model (like user.json()) should keep working, as you are handling the conversion to dict in your code. But you can now return the model directly.

Please check and let me know if it works for you.

tiangolo picture tiangolo on 12 Feb 2019
👍1

@LexSerest Thanks for the suggestion. It cleaned up my code quite a bit.

@tiangolo Just upgraded to v0.3.0 and it works flawlessly. Thanks for all your work! :clap: :+1: :smile:

nickc1 picture nickc1 on 12 Feb 2019
🎉1 👍1

@nickc1 Can the ticket be closed then?

haizaar picture haizaar on 21 Feb 2019
👍1

Yes!

nickc1 picture nickc1 on 21 Feb 2019
👍1

Thanks!

tiangolo picture tiangolo on 2 Mar 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

levchik picture levchik  路  3Comments
devtud picture devtud  路  3Comments
RenanAlonkin picture RenanAlonkin  路  3Comments
danielgtaylor picture danielgtaylor  路  3Comments
kkinder picture kkinder  路  3Comments