Skip to content

Instantly share code, notes, and snippets.

@hrishikeshrt
Last active December 18, 2023 22:42
Show Gist options
  • Save hrishikeshrt/abb610743c394ce140196498b9c4ff0b to your computer and use it in GitHub Desktop.
Save hrishikeshrt/abb610743c394ce140196498b9c4ff0b to your computer and use it in GitHub Desktop.
model_to_dict function for SQLAlchemy
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
model_to_dict() functiion for SQLAlchemy models
Based on answers by "doog abides" and "daveoncode" from
https://stackoverflow.com/questions/23554119/convert-sqlalchemy-orm-result-to-dict
@author: Hrishikesh Terdalkar
"""
from datetime import datetime
from sqlalchemy.orm import class_mapper
###############################################################################
def model_to_dict(
obj,
max_depth: int = 1,
visited_children: set = None,
back_relationships: set = None,
):
"""SQLAlchmey objects as python `dict`
Parameters
----------
obj : SQLAlchemy model object
Similar to an instance returned by declarative_base()
max_depth : int, optional
Maximum depth for recursion on relationships.
The default is 1.
visited_children : set, optional
Set of children already visited.
The default is None.
Primary use of this attribute is for recursive calls, and a user
usually does not explicitly set this.
back_relationships : set, optional
Set of back relationships already explored.
The default is None.
Primary use of this attribute is for recursive calls, and a user
usually does not explicitly set this.
Returns
-------
dict
Python `dict` representation of the SQLAlchemy object
"""
if visited_children is None:
visited_children = set()
if back_relationships is None:
back_relationships = set()
mapper = class_mapper(obj.__class__)
columns = [column.key for column in mapper.columns]
get_key_value = (
lambda c: (c, getattr(obj, c).isoformat())
if isinstance(getattr(obj, c), datetime) else
(c, getattr(obj, c))
)
data = dict(map(get_key_value, columns))
if max_depth > 0:
for name, relation in mapper.relationships.items():
if name in back_relationships:
continue
if relation.backref:
back_relationships.add(name)
relationship_children = getattr(obj, name)
if relationship_children is not None:
if relation.uselist:
children = []
for child in (
c
for c in relationship_children
if c not in visited_children
):
visited_children.add(child)
children.append(model_to_dict(
child,
max_depth=max_depth-1,
visited_children=visited_children,
back_relationships=back_relationships
))
data[name] = children
else:
data[name] = model_to_dict(
relationship_children,
max_depth=max_depth-1,
visited_children=visited_children,
back_relationships=back_relationships
)
return data
###############################################################################
@gbozzetti
Copy link

Hello, I have a bug with the following scenario:

Table A
 - id
 - value

Table B
 - id
 - id_table_A
 - id_table_C
 - value

Table C
 - id
 - value

When I run model_to_dict with max_depth = 2 on Table A, I expect to see a result like:

{
  "id": int
  "value": str
  "table_b_results": [
    {
      "id": int
      "value": str
      "table_c_result": dict
    },
    {
      "id": int
      "value": str
      "table_c_result": dict
    },
    ....
  ]
}

instead I get

{
  "id": int
  "value": str
  "table_b_results": [
    {
      "id": int
      "value": str
      "table_c_result": dict
    },
    {
      "id": int
      "value": str
    },
    ....
  ]
}

So basically table_c_result is resolved only in the first result of table_b_results, how can I have all the table_c_result?

Thank you for your help!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment