0

I making a query with Flask-SQLAlchemy, and then append the result to a list. Here is the snippet of my code:

students_payment = db.session.query(Payment, Student, Course).join(Student, Course).distinct(Payment.student_id)

student_per_course = []
for data in students_payment:
    student_per_course.append({data.Course.name: [{data.Payment.payment_for_month.value: students_payment.filter(Payment.payment_for_month == data.Payment.payment_for_month).count()}]})

When I print the student_per_course variable I got a result like this:

[{'Tahsin': [{'January': 1}]}, {'Arabic Language': [{'September': 1}]}, {'Arabic Language': [{'August': 2}]}, {'Arabic Language': [{'August': 2}]}]

There is duplicate value on a month, which is August in this query result.

Then I try to remove the duplicate value with this following code:

list_of_unique_dicts = []
for dict_ in student_per_course:
    if dict_ not in list_of_unique_dicts:
       list_of_unique_dicts.append(dict_)

And here is the result when I print the list_of_unique_dicts:

[{'Tahsin': [{'January': 1}]}, {'Arabic Language': [{'September': 1}]}, {'Arabic Language': [{'August': 2}]}]

The duplicate value on the month has been removed, but one of the key of the dictionary still got a duplicate value.

The result what I want is like this:

[
    {
        'Tahsin': [
            {'January': 1}
        ]
    },
    {
        'Arabic Language':
            [
                {'September': 1,
                 'August': 2
                 }
            ]
    },
]

In the result above, the result on Arabic Language keys are merged to all of the value from the keys.

So, the point of my question is, what's wrong in my query..?, in what point should I modify it..?, or should I thinking to merged the result on Python directly, no in SQLALchemy..?

Tri
  • 2,722
  • 5
  • 36
  • 65
  • 1
    The best place to fix this is in your SQLAlchemy query. Carefully look at what the join is doing and make sure you are filtering the duplicates you intend. – Kevin Welch Sep 20 '19 at 17:28
  • Also, a more sensible end result may be `{'Tahsin': {'January': [1]}, 'Arabic Language': ['September': [1], 'August': [2]}}` You don't really need the outer array it seems, and this would be a much easier data structure to construct. – Kevin Welch Sep 20 '19 at 17:29

0 Answers0