How to read all data from cursor.execute() in python?

Question

I use PyMysql to connect to my MySQL DB.

cursor.execute(query)
data = cursor.fetchall()
for (id,clientid,timestamp) in cursor:
    print id,clientid,timestamp

I want to sort the data based on timestamp ;like;

 sortedList = sorted(data, key=lambda x: x.timestamp, reverse=False)

but cursor returns rows. How can I return the whole data, so I can sort them based on any parameter?

p.s: Here data contains multiple rows like; 1, '1170', 'AS0001', 1, '1', datetime.datetime(2018, 3, 15, 10, 56), Decimal('15185.7562'), Decimal('0.0000'), Decimal('19814.3181')

Monophony

First, check what data holds. Second, you probably want to sort on MySQL side, not in Python. – Corrugation

@Corrugation each tuple contains like //1, '1170', 'AS0001', 1, '1', datetime.datetime(2018, 3, 15, 10, 56), Decimal('15185.7562'), Decimal('0.0000'), Decimal('19814.3181')// data – Monophony

If the timestamp is the 5th element try key=lambda x: x[5]. I agree with @Corrugation though. – Quagmire

@Quagmire thanks that fixes – Monophony

Answer 1

With a plain old cursor you can just do cursor.fetchall(), like follows:

with connection.cursor() as cursor:
    cursor.execute("select a, b, c from bar")
    print(cursor.fetchall())

Which outputs

[(1,2,3), (4,5,6), ...]

However, if you want to have the results in dictionary format, make sure to connect with:

connection = pymysql.connect(db='foo', cursorclass=pymysql.cursors.DictCursor)

In this case the results will be usable in your lambda, ie:

[{'a':1, 'b':2, 'c': 3}, ...]

Declinometer

Do we have DictCursor in mysql-python-connector too? – Monophony

yes, works the same except the connection should be specified with cursorclass=MySQLdb.cursors.DictCursor – Declinometer