SELECT results with wrong column order with PyMySQL

Question

I'm executing a SQL "SELECT" query on a MySQL database via python, using PyMySQL as the interface. Below is the excerpt of the code which performs the task:

try:
        with self.connection.cursor() as cursor:
            sql = "SELECT `symbol`,`clordid`,`side`,`status` FROM " + tablename + " WHERE `tradedate` >= %s AND (`status` =%s OR `status`=%s)"
            cursor.execute(sql,(str(begindate.date()),'I','T'))
            a = cursor.fetchall()

The query executes just fine. The problem is that the column ordering of the results doesn't match the order specified within the query. If I run add the following code:

for b in a:
            print b.values()

The values in variable 'b' appear in the following order:

'status', 'symbol', 'side', 'clordid'

Moreover, it doesn't matter which order is specified by me- the results always appear in this order. Is there any way to fix this? Thanks in advance!

The row ordering of results of this particular query is irrelevant to me. The column ordering, however, is of the essence, as I'm guessing would be the case in any SELECT query that I would execute in the future. — bsam, Sep 10 '15 at 14:01
what hapens when you call `print b[0], b[1]` etc..? i think `.values()` just returns a list of the values internally , ignoring the order. — Lawrence Benson, Sep 10 '15 at 14:05
try this solution : http://stackoverflow.com/a/10195344/4421474 — Alex, Sep 10 '15 at 14:10
@LawrenceBenson, the indexing in b is coherent with the order of the values returned from .values() — bsam, Sep 10 '15 at 15:31
@Alex, That works! Many thanks! I wound up using this exact solution. I just thought there would be a more (i want to say 'pythonic', but i guess my n00bness in the language doesn't make me worthy of using such an adjective ) synthetic and general way of doing it, — bsam, Sep 10 '15 at 15:34

score 4 · Answer 1 · edited May 23 '17 at 10:27

In testing I found the selected answer (convert dict to OrderedDict) to be unreliable in preserving query result column order.

@vaultah's answer in a similar question suggests using pymysql.cursors.DictCursorMixin:

class OrderedDictCursor(DictCursorMixin, Cursor):
    dict_type = OrderedDict

...to create a cursor that remembers the correct column order:

cursor = conn.cursor(OrderedDictCursor)

Then get your results like normal:

results = cursor.fetchall()
for row in results:
    print row # properly ordered columns

I prefer this approach better because it's stable, requires less code, and handles ordering at the appropriate level (as the columns are read).

felipsmartins · Accepted Answer · 2015-09-10T14:34:18.713

2

I amolst sure you need collections.OrderedDict, as each table row is a dict where keys stays for columns:

# python 2.7
import pymysql.cursors
from collections import OrderedDict

# ...
results = cursor.fetchall()

for i in results:
    print OrderedDict(sorted(i.items(), key=lambda t: t[0]))

Also, based on your code snippet b.values() sounds like SQL ORDER BY col_name ASC|DESC. On this case SQL should be work pretty well.

edited Sep 10 '15 at 14:34

answered Sep 10 '15 at 14:27

felipsmartins

13,269
4
48
56

@BernardoAmorim It was solved through OrderedDict or SQL statement? – felipsmartins Sep 10 '15 at 16:30
with the OrderedDict! – bsam Sep 10 '15 at 20:19

score 1 · Answer 3 · edited May 23 '17 at 12:02

Since you liked that solutuion

Here is an approach:

 with self.connection.cursor() as cursor:
        sql = "SELECT `symbol`,`clordid`,`side`,`status` FROM " + tablename + " WHERE `tradedate` >= %s AND (`status` =%s OR `status`=%s)"
        cursor.execute(sql,(str(begindate.date()),'I','T'))
        a = cursor.fetchall()
 for b in a:
         print "%s, %s, %s, %s" % (b["symbol"], b["clordid"], b["side"], b["status"])

I am not sure, if I should post this answer or to flag your OP to be closed as a duplicate.

SELECT results with wrong column order with PyMySQL

3 Answers3

Linked