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!

Answer 1

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).