[FIXED] How to write Python dictionaries to CSV when the order of keys/values varies?

Issue

How do I write a CSV file from a Python list of dictionaries, making sure the dictionary keys are always in the same order?

Specifically, I have a Python list of dictionaries (coming from a JSON API), like this:

[
    {
        "id": 1,
        "name": "Peter",
        "city": "London"
    },
    {
        "id": 2,
        "city": "Boston",
        "name": "Paul"
        
    },
    {
        "id": 3,
        "name": "Mary",
        "city": "Paris"
    }
]

Notice that the order of the keys (name / city) varies.

Now I want to write this to MySQL. What I currently do is I write everything to a CSV file and then load this file into MySQL using LOAD DATA INFILE.

I write the CSV file by looping through all the elements in my list and write the values to the CSV file:

def write_csv_file(filename, resp):

    csvFile = open(filename, "w", newline='', encoding='utf-8')
    csvWriter = csv.writer(csvFile, delimiter=',', quoting=csv.QUOTE_ALL)

    for element in resp:
       csvWriter.writerow(element.values())

    csvFile.close()

This however leads to the CSV file having different values from row to row, depending on how the data sits in the dictionaries.

How can this be done better, making sure the dictionary keys are always in the same order?

Any hints are greatly appreciated!

Solution

Use a csv.DictWriter. The fieldnames will force the order.

import csv
data = [
    {
        "id": 1,
        "name": "Peter",
        "city": "London"
    },
    {
        "id": 2,
        "city": "Boston",
        "name": "Paul"

    },
    {
        "id": 3,
        "name": "Mary",
        "city": "Paris"
    }
]
with open('test.csv', "w", newline='', encoding='utf-8') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=["id", "name", "city"], delimiter=',', quoting=csv.QUOTE_ALL)
    writer.writerows(data)

And this is the csv that was generated:

"1","Peter","London"
"2","Paul","Boston"
"3","Mary","Paris"

Answered By - juanpa.arrivillaga