How do i keep the order of my SQL columns in Python when saving?

Question

If you scroll down a bit you can see this code from g.d.d.c return SQL table as JSON in python:

qry = "Select Id, Name, Artist, Album From MP3s Order By Name, Artist"
# Assumes conn is a database connection.
cursor = conn.cursor()
cursor.execute(qry)
rows = [x for x in cursor]
cols = [x[0] for x in cursor.description]
songs = []
for row in rows:
  song = {}
  for prop, val in zip(cols, row):
    song[prop] = val
  songs.append(song)
# Create a string representation of your array of songs.
songsJSON = json.dumps(songs)

I just want to keep the order of my columns.

For example when I print(cols) I get this:

['id', 'Color', 'YCoord', 'Width', 'Height'] # right order

But the columns are saved in a wrong order:

[{"Color": "#FF99FF","Width"=345, "id"=43, "YCoord"=5784 "Height"=-546}...] # wrong order

The more columns I add, the more random it gets.

Dictionaries are not ordered, replace `song = {}` with `song = OrderedDict()`. You will need to `from collections import OrderedDict` at the top of your script — Chris_Rands, Aug 02 '16 at 14:05
Does this answer your question? [SELECT results with wrong column order with PyMySQL](https://stackoverflow.com/questions/32503795/select-results-with-wrong-column-order-with-pymysql) — Cagy79, Oct 28 '20 at 22:54

score 2 · Accepted Answer · answered Aug 02 '16 at 14:05

2

Python dict don't save the order of keys, use OrderedDict instead.

answered Aug 02 '16 at 14:05

Compadre

815
1
10
18

score 1 · Answer 2 · answered Aug 02 '16 at 14:08

If I understand You want dictionary to have ordered key. It's not possible, because dictionaries are not keeping keys in some order, because keys are used only to access elements. You can always print columns of data using raw column information:

cols = ["column1", "column2", "column3"]
for row in data_from_database:
    for col in cols:
        print row[col]

How do i keep the order of my SQL columns in Python when saving?

2 Answers2