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 <[email protected] <javascript:>>
> 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 [email protected] <javascript:>.
> > 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/67439e94-2666-402a-a642-7f4e7de8fbffo%40googlegroups.com.
