How to translate SQLAlchemy result rows into nested dicts

Question

I am evaluating a potential setup for using SQLAlchemy in an async/await FastAPI app. I am currently composing models and queries using declarative_base classes, and then executing the queries with Databases (the syntax is much more readable and easy to write for model classes; working directly with SQLAlchemy core tables is not my favorite activity). This all works great.

At this point, I have SQLAlchemy result rows, but I need to convert them into generic dicts, potentially nested due to eagerly loaded relationships (only type I will support in this environment). I can't use SQLAlchemy's ORM because 1) I don't have an engine or session; and 2) the ORM assumes that it can just hit the database whenever it needs to load in objects, which is not the case in an async/await FastAPI app.

Does anyone have ideas or pointers for how to accomplish this? I'm struggling to figure out how to associate result rows with particular relationship keys, in particular. I've been poking around in the SQLAlchemy internals for ideas, but it's pretty opaque since a lot of it assumes an entire layer of object caching and session/engine management that just isn't present in my setup.

The two things I could use ideas about:

1. How to map columns names like table_1_column_name to specific models and their properties
2. How to detect and map relationships (potentially more than one level deep)

Thanks for any help you can provide!

Update: You can find a runnable example here: https://gist.github.com/onecrayon/dd4803a5099061fa48d52f2d4bc2396b (see lines 92-109 for the relevant place where I need to figure out how to convert a RowProxy to a nested dict by mapping the query column names to the names on the SQLAlchemy model).

Answer 1

If you are db first, sqlalchemy execute method usually returns a Result Proxy object and you can get the result of it with its methods such as fetchone, first, fetchall and then cast it to list or dict. You can see also this dock