CODEWITHSUNDEEP
python-3.xhana
Cribber
Cribber

Reputation: 2943

SAP HANA hdblci package - get data with column names

I'm using the hdbcli package to load data from SAP HANA.

Problem: When loading data, I only get the value rows without the actual headers of the SQL table.

When I load only 3 columns (as below), I can manually add them myself, even though it is very ugly. This becomes impossible when I execute a Select * statement, as I really don't want to have to add them manually and might not know when there is a change.

Question: Is there a flag / command to get the column headers from a table?

Code-MRE:

#Initialize your connection
conn = dbapi.connect(
    address='00.0.000.00',
    port='39015',
    user='User',
    password='Password',
    encrypt=True, 
    sslValidateCertificate=False
)

cursor = conn.cursor()
sql_command = "select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;"
cursor.execute(sql_command)
rows = cursor.fetchall()  # returns only data, not the column values
for row in rows:
    for col in row:
        print ("%s" % col, end=" ")
    print (" ")
cursor.close()
conn.close()

Upvotes: 2

Views: 1964

Answers (2)

PV8
PV8

Reputation: 6270

In case you want to have a pandas dataframe:

import pandas as pd
sql_read = f"select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER"
cursor.execute(sql_read)
#get the headers
column_headers = [i[0] for i in cursor.description]  # get column headers
#get the data (no column headers)
df = pd.DataFrame(cursor.fetchall())
cursor.close()
conn.close()
#get the column headers to the dataframe
df.columns = column_headers

Upvotes: 0

Cribber
Cribber

Reputation: 2943

Thanks to @astentx' comment I found a solution:

cursor = conn.cursor()
sql_command = "select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;"
cursor.execute(sql_command)
rows = cursor.fetchall()  # returns only data, not the column headers
column_headers = [i[0] for i in cursor.description]  # get column headers
cursor.close()
conn.close()

result = [[column_headers]]  # insert header
for row in rows:  # insert rows
    current_row = []
    for cell in row: 
        current_row.append(cell)
    result.append(current_row)

Upvotes: 4

Related Questions