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!
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].
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.