Getting column header from snowflake table using python snowflake connector

user_6114709 0
Viewed 1613

How to fetch the table data along with column names using snowflake connector cursor. Well I am able to get it using dictcursor but it becomes complex to consolidate the result set as it gives all data as key pair. I wonder if there is any straight forward way.

snowflake-cloud-data-platform python
2 Answers

To get the column headings you can use the cursor's description attribute which returns the metadata of the results and is described here.

Use it like the following:

import snowflake.connector
import os

snowflake_username = os.environ['SNOWFLAKE_USERNAME']
snowflake_password = os.environ['SNOWFLAKE_PASSWORD']
snowflake_account = os.environ['SNOWFLAKE_ACCOUNT']
snowflake_warehouse = os.environ['SNOWFLAKE_WAREHOUSE']
snowflake_database = 'sample_data'
snowflake_schema = 'tpch_sf1'


if __name__ == '__main__':
    with snowflake.connector.connect(
            user=snowflake_username,
            password=snowflake_password,
            account=snowflake_account,
            warehouse=snowflake_warehouse,
            database=snowflake_database,
            schema=snowflake_schema,
            autocommit=False
    ) as con:
        # Execute cursor and print metadata
        cursor = con.cursor().execute("select * from sample_data.TPCH_SF1.customer limit 10")
        for c in cursor.description:
            print(c)

        # # Fetch and print results
        # results = cursor.fetchall()
        # print(results)

The above prints out:

('C_CUSTKEY', 0, None, None, 38, 0, False)
('C_NAME', 2, None, 25, None, None, False)
('C_ADDRESS', 2, None, 40, None, None, False)
('C_NATIONKEY', 0, None, None, 38, 0, False)
('C_PHONE', 2, None, 15, None, None, False)
('C_ACCTBAL', 0, None, None, 12, 2, False)
('C_MKTSEGMENT', 2, None, 10, None, None, True)
('C_COMMENT', 2, None, 117, None, None, True)
user_32872500

I had the same question, using the python snowflake connector in Jupyter notebooks. I work with dataframes, so working from @SimonD's answer above I adapted the section with cursor.description to:

hdrs = pd.DataFrame(cursor.description)

df = pd.DataFrame(sql_data)

From my data, the resulting hdrs dataframe has an attribute 'name' that I can use to set column names for the df dataframe, like so:

df.columns = hdrs['name']

user_48836960
Related