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

First, check what data holds. Second, you probably want to sort on MySQL side, not in Python. — randomir
– randomir, Commented May 30, 2018 at 3:45
@randomir 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 — Ratha
– Ratha, Commented May 30, 2018 at 3:50
If the timestamp is the 5th element try key=lambda x: x[5]. I agree with @randomir though. — Selcuk
– Selcuk, Commented May 30, 2018 at 3:51

harvey · Accepted Answer · 2018-05-30 04:01:56Z

7

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}, ...]

answered May 30, 2018 at 4:01

harvey

2,95312 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ratha Over a year ago

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

harvey Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related