How to construct a list as (key,value) pairs with PyMySQL and Python?

I have the following code:

connection = pymysql.connect(...)
try:
    with connection.cursor() as cursor:
       sql = "select cola,colb from ...."
       result = cursor.fetchall()
       How to build a list from the result?
finally:
    connection.close()

The query returns data as:

cola   colb
-----------
123    abc
124    abd
140    ghf

cola is the key

colb is the value

I know it should be something like:

list = []
for i in range (0, ????): 
     cola_value = result[0][i].get('cola')
     colb_value = result[1][i].get('colb')
     list.append((cola_value, colb_value))

I'm wondering what is the correct syntax and if this is the correct approach? I want to be able to search the list by key and access the value by key.

I'll need 2-3 lists each one with around 900000 (key,value) pairs. Is it smart to manage it in memory or better to write it to file and process it on disk?

Solution

I did not try this, but you should be able to use a list comprehension:

list = [(r['cola'], r['colb']) for r in result]

If you plan to do lookups by key, using a dictionary would be even better:

map = {r['cola']: r['colb'] for r in result}

Then if you want to find the value corresponding with they key 123:

value = map[123]

Regarding whether or not it makes sense to hold 900,000 pairs depends on your hardware resources - there are probably more memory efficient ways to do it, but you might need to install some additional dependencies.