python - How can I use a SQLAlchemy Automap object with FastApi/Pydantic

Question

I have an already existing table in my database. So instead of writing a schema, I read about Automap. I configured like this-

# filename: database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker
import psycopg2

DB_URL = 'postgresql://root:pass@localhost:5432/databaseName'

engine = create_engine(DB_URL, echo=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

AutoBase = automap_base()
AutoBase.prepare(engine, reflect=True, schema='public')

In another file, I imported this AutoBase and got the table I wanted from it into a variable.

from crud.database import Base, AutoBase
Job = AutoBase.classes.job

and, ran my query like this-

def findAll(db: Session, skip: int = 0, limit: int = 100):
    return db.query(Job).all()

I have defined a response class like-

# filename: schema.py
class JobResponse(BaseModel):
    id : int
    title : str

    class Config:
        orm_mode : True

Finally, here's router-

@app.get("/jobs", response_model=List[schema.JobResponse])
def get_jobs(db: Session = Depends(get_db)):
    try:
        jobs = job_repository.findAll(db)
    except ValidationError as e:
        print(e)
    return jobs

But this gives this error-

Traceback (most recent call last):
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 394, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/fastapi/applications.py", line 199, in __call__
    await super().__call__(scope, receive, send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/applications.py", line 111, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/routing.py", line 566, in __call__
    await route.handle(scope, receive, send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/starlette/routing.py", line 41, in app
    response = await func(request)
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/fastapi/routing.py", line 209, in app
    response_data = await serialize_response(
  File "/home/potato/strange/_projects/scrapejobs/jobsinbox/venv/lib/python3.8/site-packages/fastapi/routing.py", line 126, in serialize_response
    raise ValidationError(errors, field.type_)
pydantic.error_wrappers.ValidationError: 19 validation errors for JobResponse
response -> 0
  value is not a valid dict (type=type_error.dict)
response -> 1
  value is not a valid dict (type=type_error.dict)
response -> 2
  value is not a valid dict (type=type_error.dict)
response -> 3
  value is not a valid dict (type=type_error.dict)
response -> 4
  value is not a valid dict (type=type_error.dict)
response -> 5
  value is not a valid dict (type=type_error.dict)
response -> 6
  value is not a valid dict (type=type_error.dict)
response -> 7
  value is not a valid dict (type=type_error.dict)
response -> 8
  value is not a valid dict (type=type_error.dict)
response -> 9
  value is not a valid dict (type=type_error.dict)
response -> 10
  value is not a valid dict (type=type_error.dict)
response -> 11
  value is not a valid dict (type=type_error.dict)
response -> 12
  value is not a valid dict (type=type_error.dict)
response -> 13
  value is not a valid dict (type=type_error.dict)
response -> 14
  value is not a valid dict (type=type_error.dict)
response -> 15
  value is not a valid dict (type=type_error.dict)
response -> 16
  value is not a valid dict (type=type_error.dict)
response -> 17
  value is not a valid dict (type=type_error.dict)
response -> 18
  value is not a valid dict (type=type_error.dict)

Messy Solution

modify router like this-

@app.get("/jobs", response_model=List[schema.JobResponse])
def get_jobs(db: Session = Depends(get_db)):
    jobs = job_repository.findAll(db)
    jobs = [job.__dict__ for job in jobs]
    return jobs

But I could not find a SQLAlchemic's method to handle this. Is there any?

Answer 1

等待大神答复