6

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

One Crayon
  • 19,119
  • 11
  • 33
  • 40

3 Answers3

2

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

Muhammad Azhdari
  • 119
  • 2
  • 9
  • I'm not using the SQLAlchemy execute method (that would require an engine/session, and I am executing database queries with async/await via Databases, as mentioned in my post). I do have rows that are accessible via key, but it's the key from the generated SQLAlchemy query and I need help figuring a strategy to map from that to the model object's keys. – One Crayon Jul 27 '19 at 21:36
  • See this gist for a runnable example; the code applicable to this question is lines 92-109: https://gist.github.com/onecrayon/dd4803a5099061fa48d52f2d4bc2396b – One Crayon Jul 28 '19 at 23:47
0

Casting the object into a dict should work if the result is a raw SQLAlchemy row and not an ORM-mapped instance.

Seeing from your comment on another answer, it looks like you need to map the result back into an ORM instance. You can define declarative mappings so that your result gets translated back into a python instance.

gkhnavarro
  • 446
  • 2
  • 14
  • I'm actually mapping results into dicts, but the trick is that I want to include relationships if they were loaded as part of the query. So for every parent item, I might have multiple duplicate rows, each of which represents a child item. I then want to generate a list with a single parent item that includes a `child` property that is a list of every related `child` item in the result set. See the gist linked in the original question for an example. – One Crayon Dec 02 '19 at 22:25
0

SQLALchemy object has an option to return as dict - Here is the SQLALchemy source doc to help you. https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.util.KeyedTuple._asdict

Or You can go with the https://pythonhosted.org/dictalchemy which works as wrapper on top of SQLALchemy.

Hope that helps.

Mahabub
  • 1
  • 2
  • 1
    DictAlchemy isn't quite what I need, since it is mapping ORM results to dicts, and I specifically need to wrap RowProxy results to dicts (including relationships, if they were loaded as part of the query). I will experiment with `asdict()`, though! – One Crayon Dec 02 '19 at 22:22