CODEWITHSUNDEEP
pythonpython-3.xflasksqlalchemyflask-sqlalchemy
corvid
corvid

Reputation: 11187

create dictionary based on filter in sqlalchemy

I am trying to get a data structure like this, based on a many-to-many relationship.

Say for example there is a table with Users and a table with Roles. A user can have many roles. 

user_roles = db.Table(
    'user_roles',
    db.Column('user_id', db.Integer, db.ForeignKey(User.id), primary_key=True),
    db.Column('role_id', db.Integer, db.ForeignKey(Role.id), primary_key=True)
)

What I am trying to get is a data structure like this, where the Role is the key, and the list that follows is all the users of that role, like this

{
  'Write_Articles': ['<User Corvid>, <User Crow>'],
  'Delete_Articles': ['<User Corvid>'],
  'Change_Tags': ['<User Crow>', '<User Jeff>'],
  'Kitty_Cat': ['<User Kitty>']
}

How can this be achieved in SQLAlchemy?

Upvotes: 0

Views: 213

Answers (1)

davidism
davidism

Reputation: 127260

Eager-load the relationship to avoid multiple queries, and use a dictionary comprehension to manipulate the results.

by_name = {g.name: g.users for g in Group.query.options(db.joinedload(Group.users))}

Upvotes: 1

Related Questions