Returning sqlalchemy Data Model about fastapi HOT 9 CLOSED

@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! 👏 👍 😄

from fastapi.

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.

from fastapi.

@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.

from fastapi.

@nickc1 Can the ticket be closed then?

from fastapi.

Yes!

from fastapi.

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 🍰 🎉

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 😊🎉

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 😄

from fastapi.

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.

from fastapi.

Thanks!

from fastapi.

Assuming the original need was handled, this will be automatically closed now. But feel free to add more comments or create new issues or PRs.

from fastapi.