Are you eager-loading the "student.subjects" relationship? If not, that will give you the biggest performance increase. Without that, you will be issuing a separate DB query for each of the students, to load that student's subjects. Eager-loading allows you to preload the subjects for every student in a single query:
https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#joined-eager-loading Simon On Fri, Jul 3, 2020 at 1:36 PM Justvuur <justv...@gmail.com> wrote: > > Hi Simon, thanks for the help! I've never used that before, it's quite handy. > > I'm looping through all the students and printing them and their subject > details to a CSV file. > What makes things a tad complicated is the subjects must appear in a specific > order. > There is a table that has the subject code and order number (ordered_subjects > used below is the resultset from it). > I printed out the timing and found the problem to be with a nested for loop. > > I was hoping to reduce that process time by using a map that automatically > gets populated instead of having to create it on the fly. > > Before - subjects_collection "attribute_mapped_collection": > ******************************************************************************************** > for row in students: > row_no += 1 > > for subject in row.subjects: > student_subjects[subject.code] = subject.value > > csv_row = [row_no] > csv_row += [student_subjects.get(x.code, '') for x in ordered_subjects] > csv_row += [row.created_on, row.updated_on] > > writer.writerow([x.encode('utf-8') if type(x) == unicode else x for x in > csv_row]) > > > After adding the subjects_collection "attribute_mapped_collection", I > unfortunately did not see a change in performance. > > After - subjects_collection "attribute_mapped_collection": > ******************************************************************************************** > for row in students: > row_no += 1 > csv_row = [row_no] > csv_row += [row.subjects_collection.get(x.code, '').value for x in > ordered_subjects] > csv_row += [row.created_on, row.updated_on] > > writer.writerow([x.encode('utf-8') if type(x) == unicode else x for x in > csv_row]) > > > class Subject(db.Model): > __tablename__ = 'subjects' > > student_id = db.Column(db.Integer, db.ForeignKey('students.id'), > primary_key=True) > > code = db.Column(db.String(50), primary_key=True) > > value= db.Column(db.String) > > def __init__(self, code , value): > self.code = code > self.value = value > > > class Student(ResourceMixin, db.Model): > __tablename__ = 'students' > > subjects= db.relationship('Subject', backref='student') > > id = db.Column(db.Integer, primary_key=True) > > subjects_collection = relationship("Subject", > collection_class=attribute_mapped_collection('code')) > > Can you see a way I can optimize this? Any ideas? > > > On Friday, 3 July 2020 12:31:03 UTC+2, Simon King wrote: >> >> 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 <just...@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 sqlal...@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/67439e94-2666-402a-a642-7f4e7de8fbffo%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/CAFHwexcb9MvVyi8f-%3DNk5aJgMuNsdAOMZJ12xgza0yr8HOr8%2BQ%40mail.gmail.com.
