0

I'm trying to run a presto query and it runs successfully in DB Visualizer(Database Querying Tool). Upon running the same query via Python script it prints the result as is in my file but fails to write the headers in my CSV file.

Can anyone tell me how can I print my dynamic headers in the file?

NOTE: My headers are dynamic and will be generated based on the Presto Query, so I can't hard code the headers.

My code is as follows:

from sys import argv
import argparse
from pyhive import presto
import prestodb
import pandas as pd


connection = presto.connect(host='XXXX', port=8889, username='test')
cur = connection.cursor()
print('Connection Established')

result = cur.execute("""
with map_date as 
(
 SELECT 
 box, 
 epoch,
 timestamp,
 date,
 map_agg(signalName, value) as map_values
from hive.test.table1
where box = 'A' 
and (time >= 1596045600 and time <= 1596045720)
and signalName in ('field1','field2')
GROUP BY box,epoch,timestamp,date
order by timestamp asc
)
SELECT
  epoch
, timestamp
, CASE WHEN element_at(map_values, 'field1') IS NOT NULL THEN map_values['filed1'] ELSE NULL END AS field1
, CASE WHEN element_at(map_values, 'field2') IS NOT NULL THEN map_values['filed2'] ELSE NULL END AS field2
, box
, date AS date
from map_date
""" )


rows = cur.fetchall()
print('Fetching Result')
print('Query Finished')    
fp = open('/Users/xyz/Desktop/Python/file.csv', 'w')
print('File Created')
myFile = csv.writer(fp)
myFile.writerows(rows)
cur.close()
connection.close()
print("Connection Closed")

The output I get in the DB visualizer is(Attaching a sample output) :

Any help is much appreciated. Thank you!

Shrads
  • 883
  • 19
  • 39
  • 1
    As shown in the linked duplicate, you can get the query's column names from the cursor with `colnames = [desc[0] for desc in cur.description]`. Then you can write them to the csv before writing the result rows. – snakecharmerb Aug 18 '20 at 04:48
  • @snakecharmerb Thanks for sharing the post. The answer is : colnames = [desc[0] for desc in cur.description] myFile.writerow(colnames) Solved my problem. Thanks! – Shrads Aug 18 '20 at 05:05

0 Answers0