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!

cursor.execute('select color from myschema.this_table limit 1'), color = cursor.fetchall() — gold_cy
– gold_cy, Commented Apr 24, 2017 at 14:57

Dan Lowe · Accepted Answer · 2017-04-24 15:07:20Z

2

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.

edited Apr 24, 2017 at 15:07

answered Apr 24, 2017 at 14:57

Dan Lowe

57.5k20 gold badges133 silver badges115 bronze badges

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

2 Comments

singmotor Over a year ago

Dan, thanks for the response. this doesn't quite work for me though, since I'm not always positive that the column is present. So first I'm actually doing a select *, then using cursor.description to search for the column name. Then if I find it, I need to check it's value.

Dan Lowe Over a year ago

@Acoustic77 See updated answer. I added a way to get the column name map and then use that to figure out which column to snag (only if the column exists). Hope this is more in line with what you were after...

Collectives™ on Stack Overflow

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

1 Answer 1

Select only some columns

Get a map of columns first

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Select only some columns

Get a map of columns first

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related