0

JayDeBe is all set up and querying Redshift is working correctly, but I need to find a way to return the column names which will be helpful when converting the list into a Pandas dataframe

This is the code I'm using to execute the query:

curs = conn.cursor()
curs.execute("select u.customer_name, u.status, from table.users u limit 10")
result = curs.fetchall()

Depending on the query, the column names will be different so a set format wouldn't work in this case

I have google searched and have tried various suggestions but none accomplish the task.

Any assistance on how I can return the column names would be appreciated, many thanks!

aynber
  • 22,380
  • 8
  • 50
  • 63
Simon Green
  • 11
  • 3

2 Answers2

1

You can get the column names using something like this

select ordinal_position as position,
       column_name,
       data_type,
       case when character_maximum_length is not null
            then character_maximum_length
            else numeric_precision end as max_length,
       is_nullable,
       column_default as default_value
from information_schema.columns
where table_name = 'table_name'
      and table_schema = 'Schema name'
order by ordinal_position;

Alternatively you could try https://docs.aws.amazon.com/redshift/latest/dg/r_PG_TABLE_DEF.html

Jon Scott
  • 4,144
  • 17
  • 29
1

Thanks Jon Scott for your response, but I created the answer I was looking for here, maybe it can help someone...

curs = conn.cursor()
curs.execute("select u.customer_name, u.status, from table.users u limit 10")
# This next line was the key:
colnames = [desc[0] for desc in curs.description]
result = curs.fetchall()


# Then converting to Pandas DataFrame and adding the column names
df = pd.DataFrame(result)
df.columns = colnames

Thanks!

Simon Green
  • 11
  • 3