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
– Chris_Rands, Commented Aug 2, 2016 at 14:05
Does this answer your question? SELECT results with wrong column order with PyMySQL — Cagy79
– Cagy79, Commented Oct 28, 2020 at 22:54

Compadre · Accepted Answer · 2016-08-02 14:05:27Z

2

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

answered Aug 2, 2016 at 14:05

Compadre

8341 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Deptula · Accepted Answer · 2016-08-02 14:08:29Z

1

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]

answered Aug 2, 2016 at 14:08

Lukasz Deptula

3613 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related