Are you trying to optimise the database access (ie. minimize the number of queries), or provide a nice dictionary-style API for your Student objects? What do you mean when you say that looping over student.subjects is quite heavy?
An association proxy can be used to get dict-style access to a relationship: https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#proxying-to-dictionary-based-collections There are also a couple of examples in the SQLAlchemy docs that provide a dictionary-style API: https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.dynamic_dict https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.vertical Hope that helps, Simon On Thu, Jul 2, 2020 at 8:46 PM Justvuur <justv...@gmail.com> wrote: > > Hi there, > > I'm struggling to find an efficient way to get a two columned subset into > dictionary form. > > I have an entity that has a subset of data. The subset is linked to the > entity via Id. The order of the subset of data is defined in another table. > > Example: > Student - Id, firstname, lastname > Subjects - StudentId, SubjectCode, SubjectName > > At the moment I'm looping through the SqlAlchemy result of "student.subjects" > in python and creating a dictionary from that. It's quite heavy, especially > when there are 2000+ students with a potential of 100+ subjects each. > > For each student, how do I get the subjects as a dictionary for a student > where the key is the SubjectCode and the value is the SubjectName? > Better yet, how can I get a result set: Id, firstname, lastname SubjectCode > x, SubjectCode y, etc etc (where the SubjectName becomes the value and the > SubjectCode becomes the column)? > > Regards, > Justin > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/57b74c9a-a6e5-494b-b468-d0bdcbcce60co%40googlegroups.com. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeFF0TvOMhapyPgFeZsPnwgsbOUWgiO%2B_YAtyPrd2JF6A%40mail.gmail.com.