Bernardo Amorim Bernardo Amorim - 1 year ago 162
SQL Question

SELECT results with wrong column order with PyMySQL

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:

with self.connection.cursor() as cursor:
sql = "SELECT `symbol`,`clordid`,`side`,`status` FROM " + tablename + " WHERE `tradedate` >= %s AND (`status` =%s OR `status`=%s)"
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 Source

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.