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: try: with self.

Solution 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:

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

Solution 2:

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

# python 2.7import 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.

Solution 3:

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.