How to use SQLAlchemy's reflection to load data from a database table?

I’m trying to use the reflection feature in SQLAlchemy to interact with an already existing database which contains data.

My work so far:

import os
import fastapi
import databases
import sqlalchemy
from typing import List
from pydantic import BaseModel
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv('STAGING_SQLALCHEMY_DATABASE_URL')
database = databases.Database(DATABASE_URL)

engine = sqlalchemy.create_engine(DATABASE_URL)

metadata = sqlalchemy.MetaData()
metadata.reflect(bind=engine)
metadata.create_all(engine)

us_states = metadata.tables['us_states']


class USStates(BaseModel):
    name: str
    code: str


router = fastapi.APIRouter()


@router.get("/us_states/", response_model=List[USStates])
async def read_us_states():
    query = us_states.select()
    return await database.execute(query)

For some reason though, get_us_states() returns Null, even though the table currently has data.

How can I fix this?

from Recent Questions - Stack Overflow https://ift.tt/3peZ9OU
https://ift.tt/eA8V8J