I'm trying to produce a JSON format for a given entity and I'm having an issue getting the dictionary to NOT overwrite itself or become empty. This is pulling rows from a table in a MySQL database and attempting to produce JSON result from the query.

Here is my function:

def detail():
    student = 'John Doe'
    conn = get_db_connection()
    cur = conn.cursor()
    sql = ("""
             select
                a.student_name,
                a.student_id,
                a.student_homeroom_name,
                a.test_id,
                a.datetaken, 
                a.datecertified,
                b.request_number
                FROM student_information a 
                INNER JOIN homeroom b ON a.homeroom_id = b.homeroom_id
                WHERE a.student_name = '"""   student   """'
                ORDER BY datecertified DESC 
             """)
    cur.execute(sql)
    details=cur.fetchall()
    
    dataset = defaultdict(dict)
    case_dataset = defaultdict(dict)
    case_dataset = dict(case_dataset)
    
    for student_name, student_id, student_homeroom_name, test_id, datetaken, datecertified, request_number in details:
        dataset[student_name]['student_id'] = student_id
        dataset[student_name]['student_homeroom_name'] = student_homeroom_name
        
        case_dataset['test_id'] = test_id
        case_dataset['datetaken'] = datetaken
        case_dataset['datecertified'] = datecertified
        case_dataset['request_number'] = request_number

        dataset[student_name]['additional_information'] = case_dataset

        case_dataset.clear()
    
    dataset= dict(dataset)
    print(dataset)

    cur.close()
    conn.close()

I tried a few different ways but nothing seems to work. What I'm getting is nothing in the additonal_information key. What I'm getting is this:

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": []
    }
}

What I'm expecting is something similar to the below JSON. However, I'm torn if this is even correct. Each student will have one to many test_id and I will need to iterate through them in my application.

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": [
                {
                    "test_id": "0987",
                    "datetaken": "1-1-1970",
                    "datecertified": "1-2-1970",
                    "request_number": "5643"
                },
                {
                    "test_id": "12343",
                    "datetaken": "1-1-1980",
                    "datecertified": "1-2-1980",
                    "request_number": "39807"
                }
        ]
    }
}

Removing the clear() from the function produces this JSON:

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": [
            {
                "test_id": "0987",
                "datetaken": "1-1-1970",
                "datecertified": "1-2-1970",
                "request_number": "5643"
            },
            {
                "test_id": "0987",
                "datetaken": "1-1-1970",
                "datecertified": "1-2-1970",
                "request_number": "5643"
            }
        ]
    }
}

CodePudding user response：

lists are mutable objects. Which means that list's are passed by reference.

when you set

dataset[student]['additional_information'] = case_dataset

case_dataset.clear()

you're setting the list and then clearing it. So the list inside additional_information is also cleared.

Copy the list when setting it:

dataset[student]['additional_information'] = case_dataset[:]

case_dataset.clear()

CodePudding user response：

Thanks everyone for the guidance and pointing me in the right direction.

I have what I'm looking for now. Based on some of the comments and troubleshooting, I updated my code. Here is what I did:

1. I added back additional_dataset as a list
2. Removed case_dataset = defaultdict(dict) and case_dataset = dict(case_dataset) and replaced it with case_dataset = {}.
3. Updated dataset[student_name]['additional_information'] = case_dataset with dataset[student_name]['additional_information'] = additional_dataset
4. Replaced case_dataset.clear() with case_dataset = {}

Here is my new code now

def detail():
    student = 'John Doe'
    conn = get_db_connection()
    cur = conn.cursor()
    sql = ("""
             select
                a.student_name,
                a.student_id,
                a.student_homeroom_name,
                a.test_id,
                a.datetaken, 
                a.datecertified,
                b.request_number
                FROM student_information a 
                INNER JOIN homeroom b ON a.homeroom_id = b.homeroom_id
                WHERE a.student_name = '"""   student   """'
                ORDER BY datecertified DESC 
             """)
    cur.execute(sql)
    details=cur.fetchall()
    
    dataset = defaultdict(dict)
    case_dataset = {} #2 - Updated to just dict
    additional_dataset = [] #1 - added back additional_dataset as a list
    
    for student_name, student_id, student_homeroom_name, test_id, datetaken, datecertified, request_number in details:
        dataset[student_name]['student_id'] = student_id
        dataset[student_name]['student_homeroom_name'] = student_homeroom_name
        
        case_dataset['test_id'] = test_id
        case_dataset['datetaken'] = datetaken
        case_dataset['datecertified'] = datecertified
        case_dataset['request_number'] = request_number

        dataset[student_name]['additional_information'] = additional_dataset #3 - updated to additional_dataset

        case_dataset = {} #4 - updated to clear with new dict
    
    dataset= dict(dataset)
    print(dataset)

    cur.close()
    conn.close()

This is what it produces now. This is a much better structure then wat I was previously expecting.

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": [
            {
                "test_id": "0987",
                "datetaken": "1-1-1970",
                "datecertified": "1-2-1970",
                "request_number": "5643"
            },
            {
                "test_id": "12343",
                "datetaken": "1-1-1980",
                "datecertified": "1-2-1980",
                "request_number": "39807"
            }
        ]
    }
}