How to check if a column exists, if it does, return a value from it

Question

In psycopg2, if I have a table:

+------+-----+-------+  
| name | age | color |  
+------+-----+-------+  
| Bob  |  25 | red   |  
| Bill |  50 | blue  |  
| Jane |  45 | black |  
+------+-----+-------+

If I do cursor.execute("SELECT * FROM mySchema.this_table LIMIT 1") then I check if color exists with: colnames = [desc[0] for desc in cursor.description] then search colnames for 'color'

then I think I get the row with: myrow = importCursor.fetchone()

but how do I get the 'color' value of that row? I tried color = importCursor.fetchone()['color'] but that doesn't work.

How do I get the color value of the row returned by that SELECT statement? I don't know how many columns there are in the table, or if the 'color' column will always be column #3 I have to do this for a bunch of columns in this table (check if exists, if does, return column value of row) so an efficient way is best!

Answer 1

Select only some columns

If you select only certain columns, then you do know the column order.

cursor.execute("SELECT name, age, color FROM mySchema.this_table LIMIT 1")

Now you know that column 0 is name, 1 is age, 2 is color. So you can choose which one with something like myrow[1].

Get a map of columns first

You can get a map of the returned columns, and then use that to figure out where to fetch.

column_names = [desc[0] for desc in cursor.description]
if 'color' in column_names:
    color = cursor.fetchOne()[column_names.index('color')]

This should be relatively efficient, because you only need to calculate the column name list one time after the query, and then you can map the column name to a column number with simple list operations like index and in, which are much faster than hitting the database again.