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

python pymysql mysql-connector-python

Ratha · May 30, 2018 · Viewed 8.3k times · Source

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

Answer

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

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

Answer

Related questions