11

I've been using PyMySQL for a while now and created my own wrapper that I'm used to to shorthand writing queries. Nonetheless I've been creating CSV files with OrderedDict because I need to keep the order the same but I realize that if I use PyMySQL for querying the database, I will not get the order the database is giving back. This is a little annoying for spot checking CSV files if I wanted to just dump stuff rather than hand write the orders.

My question is, how do I use PyMySQL with OrderedDict? Currently my code is as follows:

import pymysql
conn = pymysql.connect(host='localhost', user='root', passwd='', db='test')
cursor = conn.cursor(pymysql.cursors.DictCursor)

So whenever I query, I'll be getting a dictionary back:

cursor.execute("""SELECT * FROM test""")
for row in cursor:
    pp(row)  # gives me dictionary

What I want is that when I roll through cursor I'm actually retrieving an OrderedDict of the columns in the order they come in from the database.

Something like:

cursor = conn.cursor(pymysql.cursors.OrderedDict)
vaultah
  • 44,105
  • 12
  • 114
  • 143
Paul Carlton
  • 2,785
  • 2
  • 24
  • 42
  • add another column which holds, source row and order by that when querying? – Busturdust Nov 03 '15 at 17:03
  • @Busturdust - I could write in my wrapper a function say "query_ordered" or something that has the select fields in a list and then can create a generator for yielding ordereddict in replace of the dictionary but it if I can just get at a solution without writing that and that would also play well with my wrapper already, I would rather have that. – Paul Carlton Nov 03 '15 at 17:14

1 Answers1

19

You could use cursors.DictCursorMixin and change its dict_type to collections.OrderedDict (the default is dict):

from collections import OrderedDict
from pymysql.cursors import DictCursorMixin, Cursor

class OrderedDictCursor(DictCursorMixin, Cursor):
    dict_type = OrderedDict

Then you can use the new cursor class as shown below

cursor = conn.cursor(OrderedDictCursor)
vaultah
  • 44,105
  • 12
  • 114
  • 143