Query results missing column names Prestodb via Python

user_10986937 0
Viewed 204

I am fairly new to Python, and I am using prestodb to connect to a database to pull data and manipulate it. Everything works fine, except the query results do not have column names.

I saw a post about enabling headers in SQL+, but I am unable to find anything for prestodb. I checked the documentation on GitHub, but I am either not experienced enough or did not find a way to change the results for genall, fetchall, etc.

Does anyone have ideas on how to pull in column names/headers with my query?

Code:

conn = prestodb.dbapi.connect(
    host='hostname',
    port=4443,
    user='username',
    catalog='meld',
    schema='athena',
    http_scheme='https',
    auth=prestodb.auth.BasicAuthentication("username", "password"),
)

cur = conn.cursor()
cur.execute('SELECT * FROM meld.athena.athena_business_summary limit 10')
query = cur.genall()
rows = pd.DataFrame(query)
print(rows)

Query Results:

presto pycharm python
2 Answers

You can get column names from cur.description.

user_40874650

With ebyhr's help response, I found another thread that guided me to the answer using Pandas.

conn = prestodb.dbapi.connect(
    host='hostname',
    port=4443,
    user='username',
    catalog='meld',
    schema='athena',
    http_scheme='https',
    auth=prestodb.auth.BasicAuthentication("username", "password"),
)

cur = conn.cursor()
query = cur.execute(""" SELECT * FROM meld.athena.athena_business_summary limit 10 """)
queryFetch = cur.fetchall()
queryResults = pd.DataFrame.from_records(queryFetch, columns = [i[0] for i in cur.description])
print(queryResults)

I am sure there is a more consolidated way to do it, but I hope this helps someone!

user_109869370
Related